Invalid Identifier for input parameter variables on Oracle stored procedure

Question

I am trying to create a stored procedure that will grab all columns from a view and generate XML data for it on a CLOB.

CREATE OR REPLACE PROCEDURE PaGenerarServicioXML(vIdViaje IN NUMBER, vIDCliente IN varchar)
IS
ServicioXML CLOB;
BEGIN

  SELECT DBMS_XMLGEN.GETXML (q'!
       SELECT *
      FROM VwServicio VwS
      WHERE VwS.Identificador_Viaje = vIdViaje 
      AND VwS.Identificador_Cliente = vIdCliente
      !')
    INTO ServicioXML
    FROM DUAL;
    SYS.DBMS_OUTPUT.PUT_LINE(ServicioXML);
END;

EXECUTE PaGenerarServicioXML(2,'304560326')

The stored procedure is saved correctly.

When I execute this stored procedure, the following errors are thrown:

ORA-19202: Error occurred in XML processing
ORA-00904: "VIDCLIENTE": invalid identifier ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at "SYS.DBMS_XMLGEN", line 7
ORA-06512: at "SYS.DBMS_XMLGEN", line 164
ORA-06512: at line 1
ORA-06512: at "SQL_MBFTRUKIWLGITBRTRBBHFHTSA.PAGENERARSERVICIOXML", line 6
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

If I remove the AND statement in the WHERE clause, the error changes to it not identifying VIDVIAJE as valid. Why is it not accepting the input variables I just created?

Inside the VwServicio view, Identificador_Viaje is an INT datatype, and Identificador_Cliente is a VARCHAR, so I really don't understand where the issue is.

Answer 1

Your query is a string and DBMS_XMLGEN doesn't know anything about external context when it executes the inner query. To pass parameters to it you need to use this signature of the function:

DBMS_XMLGEN.GETXML (
   ctx          IN ctxHandle,
   dtdOrSchema  IN number := NONE)
 RETURN CLOB;

Then create a context from general cursor and pass parameters here:

dbms_xmlgen.newcontext(cursor(
  SELECT *
  FROM VwServicio VwS
  WHERE VwS.Identificador_Viaje = vIdViaje 
    AND VwS.Identificador_Cliente = vIdCliente
))

Putting it all together, you'll have something like this:

create table t
as
select level - 1 as id
from dual
connect by level < 3

create table t_child
as
select mod(level, 6) as id
  , level as child_id
from dual
connect by level < 18

create function f_get_children_xml(
  p_id in int
) return clob
is
  l_cur sys_refcursor;
begin
  open l_cur for
    select *
    from t_child
    where t_child.id = p_id
  ;
  return dbms_xmlgen.getxml(
    ctx => dbms_xmlgen.newcontext(queryString => l_cur)
  );
end;
/

✓

select f_get_children_xml(id)
from t

F_GET_CHILDREN_XML(ID)
<?xml version="1.0"?> <ROWSET> <ROW> <ID>0</ID> <CHILD_ID>6</CHILD_ID> </ROW> <ROW> <ID>0</ID> <CHILD_ID>12</CHILD_ID> </ROW> </ROWSET>
<?xml version="1.0"?> <ROWSET> <ROW> <ID>1</ID> <CHILD_ID>1</CHILD_ID> </ROW> <ROW> <ID>1</ID> <CHILD_ID>7</CHILD_ID> </ROW> <ROW> <ID>1</ID> <CHILD_ID>13</CHILD_ID> </ROW> </ROWSET>

Or the same with plain SQL:

select
  id,
  dbms_xmlgen.getxml(dbms_xmlgen.newcontext(
    cursor(select * from t_child where t_child.id = t.id)
  )) as dependent_xml
from t

ID	DEPENDENT_XML
0	<?xml version="1.0"?> <ROWSET> <ROW> <ID>0</ID> <CHILD_ID>6</CHILD_ID> </ROW> <ROW> <ID>0</ID> <CHILD_ID>12</CHILD_ID> </ROW> </ROWSET>
1	<?xml version="1.0"?> <ROWSET> <ROW> <ID>1</ID> <CHILD_ID>1</CHILD_ID> </ROW> <ROW> <ID>1</ID> <CHILD_ID>7</CHILD_ID> </ROW> <ROW> <ID>1</ID> <CHILD_ID>13</CHILD_ID> </ROW> </ROWSET>

db<>fiddle here