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

Goliard

Answer 1

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.

Bicapsular

+1: CONTEXT variables are more accommodating in this situation – Usurer

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? – Brigandage

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 for dynamic queries without this limitation, see for example This SO answer. You can't mix regular dynamic SQL (EXECUTE IMMEDIATE) with DBMS_SQL as the synthax are quite different. – Bicapsular

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") docs.oracle.com/cd/B12037_01/appdev.101/b10807/… but not for OPEN...FOR. Does its syntax differ with duplicate placeholders? – Harwill

@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; – Bicapsular

Thanks! I have opened a new question here as it wasn't working for me #60530997 – Harwill

Answer 2

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;

Credit

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. – Sarette

Answer 3

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; 
/ 

Sexivalent

Answer 4

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; 

Sulfonal