Executing a dynamic sql statement into a SYS_REFCURSOR

Question

is it possible to execute a dynamic piece of sql within plsql and return the results into a sys_refcursor? I have pasted my attempt soo far, but dosnt seam to be working, this is the error im getting throught my java app

ORA-01006: bind variable does not exist ORA-06512: at "LIVEFIS.ERC_REPORT_PK", line 116 ORA-06512: at line 1

but that could be somthing misconstrued by java, everything seams to compile fine soo im not sure.

 procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
                                ,pReport out SYS_REFCURSOR) is
  begin
    declare
      lsql  varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
          ||' where c.id = ccf.carer_id (+)'
          ||' AND cf.id (+) = ccf.cared_for_id';

    begin

     if pPostcode is not null and pAge <= 0 then
        lsql := lsql||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
      elsif pPostcode is null and pAge > 0 then 
         lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge';
      elsif pPostcode is not null and pAge > 0 then
         lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge'
                      ||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
      end if;


        execute immediate lsql
        into pReport;


    end;
  end;

Im new to plsql and even newer to dynamic sql soo any help/ suggestions would be greatly apreciated.

Thanks Again

Jon

score 12 · Answer 1 · answered Feb 09 '10 at 16:39

12

you will have to bind the parameters pAge and pPostcode. In dynamic SQL you would prefix them with a colon (:). If you use EXECUTE IMMEDIATE or OPEN ... FOR, you will bind your parameters via position, this is why I renamed them :P1 and :P2 in the example:

DECLARE
   lsql VARCHAR2(500) := 'SELECT c.id 
                            FROM carer c, cared_for cf, carer_cared_for ccf 
                           WHERE c.id = ccf.carer_id (+)
                             AND cf.id (+) = ccf.cared_for_id';
BEGIN
   IF pPostcode IS NULL THEN
      lsql := lsql || ' AND :P1 IS NULL';
   ELSE
      lsql := lsql || ' AND c.postcode like ''%''|| upper(:P1)||''%''';
   IF pPostcode pAge > 0 THEN
      lsql := lsql || ' AND :P2 = ROUND((MONTHS_BETWEEN(sysdate,
                                                        c.date_of_birth)/12))';
   ELSE
      lsql := lsql || ' AND nvl(:P2, -1) <= 0';
   END IF;
   OPEN pReport FOR lsql USING pPostcode, pAge;
END;

Note: The number and position of bind variables has to be known at compile time, this is why I often use the construct above (adding the parameter to its position even if it is not used). Adding a tautology (as in AND :P1 IS NULL) to a query won't affect its explain plan.

answered Feb 09 '10 at 16:39

Vincent Malgrat

66,725
9
119
171

1

+1: CONTEXT variables are more accommodating in this situation – OMG Ponies Feb 09 '10 at 16:48
How to do it without the USING clause to bind the variables (eg: in case of optional parameters)? I've read about DBMS_SQL.BIND_VARIABLE, but when I try to use it, everything seems to need to be rewritten. Do you know how to do this? – Iúri dos Anjos Dec 19 '18 at 20:02
With `EXECUTE IMMEDIATE` or `OPEN ... FOR`, the number of variables and their position have to be known at compile time, so in order to use optional parameters you have to use a workaround as shown in this example. You can use [`DBMS_SQL`](https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS058) for dynamic queries without this limitation, see for example [This SO answer](https://stackoverflow.com/a/11004699/119634). You can't mix regular dynamic SQL (`EXECUTE IMMEDIATE`) with `DBMS_SQL` as the synthax are quite different. – Vincent Malgrat Dec 20 '18 at 09:19
Hi @VincentMalgrat I can't find the correct syntax to use when you need to use the same bind variable multiple times. You can see the syntax for EXECUTE IMMEDIATE here (see "Using Duplicate Placeholders with Dynamic SQL") https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/11_dynam.htm#i13057 but not for OPEN...FOR. Does its syntax differ with duplicate placeholders? – J Dor Mar 04 '20 at 15:55
1

@JDor: AFAIK, the syntax is the same for `EXECUTE IMMEDIATE` and `OPEN...FOR`: the binding is by position, not name. You need to repeat the variables in case of duplicates since all variables are considered independent. For example: `OPEN cursor FOR 'SELECT :x, :x, :y FROM DUAL' USING px, px, py;` – Vincent Malgrat Mar 04 '20 at 16:40
Thanks! I have opened a new question here as it wasn't working for me https://stackoverflow.com/questions/60530497/repeating-the-same-bind-variable-multiple-times-when-using-the-open-for-dynami – J Dor Mar 04 '20 at 17:07

score 5 · Answer 2 · answered Feb 09 '10 at 16:09

5

You cannot assign a refcursor through the use of execute immediate.

You'll have to build the SQL into a string and then use open.

sql_str := 'SELECT * FROM...';
open pReport for sql_str;

answered Feb 09 '10 at 16:09

RC.

27,409
9
73
93

Technically, if the SELECT returns a cursor datatype you could select it into a ref cursor (which execute immediate or a static sql). But you are correct in that this select is returning an id value, not a ref cursor. – Gary Myers Feb 09 '10 at 21:47

APC · Answer 3 · 2010-02-10T07:25:23.030

Use the OPEN FOR syntax and bind variables.

procedure all_carers_param_dy (pPostcode in carer.postcode%type, pAge Number
                            ,pReport out SYS_REFCURSOR) 
is
  lsql  varchar2(500) :='SELECT c.id FROM carer c, cared_for cf,carer_cared_for ccf '
      ||' where c.id = ccf.carer_id (+)'
      ||' AND cf.id (+) = ccf.cared_for_id';

begin

 if pPostcode is not null and pAge <= 0 then
    lsql := lsql||' AND c.postcode like upper(''%''||:1||''%'')';
    open pReport for lsql using pPostcode;
  elsif pPostcode is null and pAge > 0 then 
     lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1';
    open pReport for lsql using pAge;
  elsif pPostcode is not null and pAge > 0 then
     lsql := lsql ||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1'
                  ||' AND c.postcode like upper(''%''||:2||''%'')';
    open pReport for lsql using pAge, pPostcode;
  end if;

end all_carers_param_dy;
/

Dynamic SQL is hard, hard to understand and hard to get right. One of the tricky areas is handling repetition. It is a good idea to declare repeating sections of bolierplate as constants. Also, note that we can split large strings over several lines without having to concatenate them with '||'. This reduces the maintenance overhead.

create or replace procedure all_carers_param_dy 
    (pPostcode in carer.postcode%type
      , pAge Number 
      , pReport out SYS_REFCURSOR)  
is 
  lsql varchar2(500) ;

  root_string constant varchar2(500) :='SELECT c.id FROM carer c
                                , cared_for cf,carer_cared_for ccf   
                      where c.id = ccf.carer_id (+)  
                      and cf.id (+) = ccf.cared_for_id'; 
  pc_string constant varchar2(256) := 
      ' AND c.postcode like upper(''%''||:pc||''%'')';
  age_string constant varchar2(256) := 
      ' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :age';
begin 

 if pPostcode is not null and pAge <= 0 then 
    lsql := root_string || pc_string; 
    open pReport for lsql using pPostcode; 

  elsif pPostcode is null and pAge > 0 then  
     lsql := root_string || age_string; 
    open pReport for lsql using pAge; 

  elsif pPostcode is not null and pAge > 0 then 
     lsql := root_string || age_string 
                         || pc_string; 
    open pReport for lsql using pAge, pPostcode; 

  end if; 
end all_carers_param_dy; 
/

score 0 · Answer 4 · answered Aug 05 '19 at 20:31

yes it's possible. Do like this:

v_sql := 'BEGIN OPEN :1 FOR :2 USING ';
v_bindvars := pPostcode ||', '||pAge; --this part you can create dynamically base on your if's
v_sql := v_sql||v_bindvars||' ; END;';
v_select := 'select yourdata from dual where 1 = :bind_first_var and 2 = :bind_second_var';

execute immediate v_sql using pReport, v_select;

Executing a dynamic sql statement into a SYS_REFCURSOR

4 Answers4

Linked