How to corrrectly use oracle EXECUTE IMMEDIATE in this code

Question

i have following pl sql code that will extract a xml tag value and treat that value as stored function using oracle's EXECUTE IMMEDIATE statement:

This code will find xml tag <function> and extract its value 'get_val' (which it self is a stored function) and will call this function:

DECLARE  
    xml_output  VARCHAR2(5000); 
    output  VARCHAR2(5000);
    --sourceXML VARCHAR2(5000);
    matchingXML VARCHAR2(5000);
    childtag VARCHAR2(100);
    childval VARCHAR2(100);
    sourceXML xmltype;
 BEGIN
   sourceXML := xmltype('<Payheads>
    <All>
        <P>
            <code>2</code>                                                
            <function>get_val</function>
            <param>1</param>
            <amount></amount>
            <source>source>
        </P>
    </All>

</Payheads>');
  childtag := 'function';

  SELECT EXTRACTVALUE(sourceXML, '//'||childtag) AS  into xml_output FROM SYS.DUAL;
  output :=   'pkg_xmltool'||'.'||xml_output||'()';

 EXECUTE IMMEDIATE output;

   if (output is not null)then
    dbms_output.put_line(output);
    elsif (output is null)then
    dbms_output.put_line('null');
    end if;

 END;

in the above code output := 'pkg_xmltool'||'.'||xml_output||'()';

i want to evaluate this in the following patter:

output := pkg_xmltool.get_val() this will call the function get_val() which will return value 100 to output. Here, this is get_val function body:

FUNCTION get_val  return  float IS  ret float;   

  v_code NUMBER;
  v_errm VARCHAR2(500);
  BEGIN

   ret := 100;
   if (ret is not null)then
    RETURN  ret;
    elsif (ret is null)then
    RETURN  null;
    end if;
    EXCEPTION    
    WHEN OTHERS THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1 , 500);
    --DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
    RETURN '<result><status>Error'||v_errm||'</status></result>';
  END get_val;

iam new oracle xml. please help to correct this code.

please provide a working sample of PL/SQL for the following: `output:= pkg_xmltool.function_name();` so that it will be clear how your dynamic SQL should look like. Beside that you forgot to add 'BEGIN ... END; /' to your anonymous PL/SQL block `output` — MaxU - stand with Ukraine, Mar 08 '16 at 10:17

score 1 · Accepted Answer · answered Mar 08 '16 at 14:57

1

You must set the resulting value into another PL variable with INTO after EXECUTE IMMEDIATE.

(declare xmlfunction)

xmlfunction :=  'select pkg_xmltool.'||xml_output||'() from dual';
EXECUTE IMMEDIATE xmlfunction INTO output;

answered Mar 08 '16 at 14:57

J. Chomel

8,193
15
41
69

How to corrrectly use oracle EXECUTE IMMEDIATE in this code

1 Answers1