ORA-00933: SQL command not properly ended ORA-06512: at "SYS.DBMS

I can compile the below code successfully:

open po_service_characteristic for
        select x.*
        from  
        XMLTable(
             '/ROWSET/ROW/*'
        passing DBMS_XMLGEN.GETXMLType('SELECT  b.equipment_type type,
                '||v_mfg_desc||' vendor,
                '||v_model_desc||' model,
                '||v_rev_desc||' version,
                a.mac_address mac
        from 
            alb_hsd_event_dtls a, wha_activation b
        WHERE
            a.ID_ALB_EVENT = '||pi_event_id||' AND
            a.mac_address = '||pi_mac||' AND
            a.mac_address = b.equipment_id AND
            b.activation_id = '||v_activation_id )
         columns
           NAME   varchar2(4000 byte) path 'name()'
         , VALUE varchar2(4000 byte) path 'text()'
        ) x
        ;

But when running the same code getting below error:

ORA-19202: Error occurred in XML processing ORA-00933: SQL command not properly ended ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1

CodePudding user response：

You need to add escaped quotes around any string values you are embedding. If you print out the SQL string you'll see that, with the values you are using, it ends up as invalid SQL - exactly what error you get will depend on the values, and which are numeric or strings.

If the IDs are numeric and other things aren't then you might want something like:

...
        passing DBMS_XMLGEN.GETXMLType('SELECT  b.equipment_type type,
                '''||v_mfg_desc||''' vendor,
                '''||v_model_desc||''' model,
                '''||v_rev_desc||''' version,
                a.mac_address mac
        from 
            alb_hsd_event_dtls a, wha_activation b
        WHERE
            a.ID_ALB_EVENT = '||pi_event_id||' AND
            a.mac_address = '''||pi_mac||''' AND
            a.mac_address = b.equipment_id AND
            b.activation_id = '||v_activation_id )
         columns
...

But given the error you reported I think at least one ID might be a string too; or you MAC has spaces perhaps.

db<>fiddle with a string ID. (The 'fixed' version still gets ORA-00942 but that's OK, I don't have your tables... but the generated SQL looks a bit more reasonable. Your actual datatypes matter though.)

I'd suggest you build up the generated statement as a separate string variable, so you can use dbms_output to debug exactly what it contains, then use that string as the argument to getxmltype.

Even better, perhaps, would be to define a ref cursor for the query, call newcontext with that, and pass the context to getxmltype. That would avoid having to add quotes, as the ref cursor can refer to the other variables directly. Something like:

...
  v_ref sys_refcursor;
  v_ctx dbms_xmlgen.ctxhandle;
begin
...
  open v_ref for
    SELECT  b.equipment_type type,
                v_mfg_desc vendor,
                v_model_desc model,
                v_rev_desc version,
                a.mac_address mac
        FROM 
            alb_hsd_event_dtls a
        JOIN
            wha_activation b
        ON
            a.mac_address = b.equipment_id
        WHERE
            a.ID_ALB_EVENT = pi_event_id AND
            a.mac_address = pi_mac AND
            b.activation_id = v_activation_id;

  v_ctx := dbms_xmlgen.newcontext(v_ref);

  open po_service_characteristic for
        select x.*
        from  
        XMLTable(
             '/ROWSET/ROW/*'
        passing DBMS_XMLGEN.GETXMLType(v_ctx)
         columns
           NAME   varchar2(4000 byte) path 'name()'
         , VALUE varchar2(4000 byte) path 'text()'
        ) x
        ;
...
end;
/

As the query is no longer dynamic this will also throw compile-time errors instead of run-time errors if there is a problem with its syntax.