Executing A Dynamic Sql Statement Into A Sys_refcursor

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 t

Solution 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 ISNULLTHEN
      lsql := lsql ||' AND :P1 IS NULL';
   ELSE
      lsql := lsql ||' AND c.postcode like ''%''|| upper(:P1)||''%''';
   IF pPostcode pAge >0THEN
      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.

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

Solution 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 isnotnulland pAge <=0then
    lsql := lsql||' AND c.postcode like upper(''%''||:1||''%'')';
    open pReport for lsql using pPostcode;
  elsif pPostcode isnulland pAge >0then 
     lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = :1';
    open pReport for lsql using pAge;
  elsif pPostcode isnotnulland pAge >0then
     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.

createor 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 isnotnulland pAge <=0then 
    lsql := root_string || pc_string; 
    open pReport for lsql using pPostcode; 

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

  elsif pPostcode isnotnulland pAge >0then 
     lsql := root_string || age_string 
                         || pc_string; 
    open pReport for lsql using pAge, pPostcode; 

  end if; 
end all_carers_param_dy; 
/

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