Passing a XML in Execute Immediate

Question

      varSQL: = 'DECLARE


      varOptionId NUMBER;
      varXML XMLTYPE;
      varHsCode VARCHAR2(200);
      varHsCodeCount NUMBER;
      varId NUMBER;
      RVAL  OBJ_RETURN:=OBJ_RETURN(NULL,NULL,NULL,NULL);

      BEGIN

      varXML:=:refXML;


      varHsCode:= varXML.EXTRACT('/OBJECT/HsCodes/HsCode/text()').GETSTRINGVAL();


      :out:=varHsCode;

       END';

       EXECUTE IMMEDIATE varSQL USING PXML,OUT varOut;

This gives the following error:

ORA-06550: line 9, column 15: PLS-00382: expression is of wrong type ORA-06550: line 9, column 2: PL/SQL: Statement ignored ORA-06512: at "BIZZXE_V2_SCH.SYSTEM_UTILITY", line 225 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Is it possible to pass a XML?

No idea why you'd want to use dynamic SQL for that, but you have unescape single quotes in the varSQL string value so I'm surprised that compiles at all. We have no context, so can you confirm that the EXECUTE IMMEDIATE is line 225 of your package body, and this is a run-time error not something you see when you compile the package? — Alex Poole
And yes, you can pass an XMLType, just tried with a version of your code with some errors fixed - missing semicolon, incorrect assignment on the first line, escaped quotes. It would be helpful if you showed what you're actually running, as a complete block of code we can run to recreate the error you're seeing. Is PXML actually XMLType, or varchar2/CLOB? You'll get that error if it's a string, so pretty sure that's the issue. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2016-04-07T11:44:19

Yes, you can pass an XMLType variable as a bind parameter:

declare
    varsql varchar2(1024);
    varout varchar2(4);
    pxml xmltype := xmltype('<OBJECT><HsCodes><HsCode>Test</HsCode></HsCodes></OBJECT>');
begin
    varSQL := 'DECLARE
 varOptionId NUMBER;
 varXML XMLTYPE;
 varHsCode VARCHAR2(200);
 varHsCodeCount NUMBER;
 varId NUMBER;
 --RVAL  OBJ_RETURN:=OBJ_RETURN(NULL,NULL,NULL,NULL);
 BEGIN
 varXML:=:refXML;

 varHsCode:= varXML.EXTRACT(''/OBJECT/HsCodes/HsCode/text()'').GETSTRINGVAL();

 :out:=varHsCode;
END;';

    EXECUTE IMMEDIATE varSQL USING PXML,OUT varOut;
    dbms_output.put_line(varOut);
end;
/

PL/SQL procedure successfully completed.

Test

The error you are getting is because you have declared PXML as a string; either a varchar2 or CLOB variable:

declare
    varsql varchar2(1024);
    varout varchar2(4);
</HsCodes></OBJECT>');
    pxml varchar2(64) := '<OBJECT><HsCodes><HsCode>Test</HsCode></HsCodes></OBJECT>';
begin
    varSQL := 'DECLARE
 varOptionId NUMBER;
 varXML XMLTYPE;
 varHsCode VARCHAR2(200);
 varHsCodeCount NUMBER;
 varId NUMBER;
 --RVAL  OBJ_RETURN:=OBJ_RETURN(NULL,NULL,NULL,NULL);
 BEGIN
 varXML:=:refXML;

 varHsCode:= varXML.EXTRACT(''/OBJECT/HsCodes/HsCode/text()'').GETSTRINGVAL();

 :out:=varHsCode;
END;';

    EXECUTE IMMEDIATE varSQL USING PXML,OUT varOut;
    dbms_output.put_line(varOut);
end;
/

Error report -
ORA-06550: line 9, column 10:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 2:
PL/SQL: Statement ignored
ORA-06512: at line 21

In your example there's no reason to use dynamic SQL anyway, but maybe your real-world example is more complicated.

Passing a XML in Execute Immediate

1 Answers