title: Executing a dynamic sql statement into a SYS_REFCURSOR
body: <p>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 </p>
  
  <blockquote>
    <p>ORA-01006: bind variable does not
    exist ORA-06512: at
    "LIVEFIS.ERC_REPORT_PK", line 116
    ORA-06512: at line 1</p>
  </blockquote>
  
  <p>but that could be somthing misconstrued by java, everything seams to compile fine soo im not sure.    </p>
  
  <pre><code> 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 &lt;= 0 then
          lsql := lsql||' AND c.postcode like ''%''|| upper(pPostcode)||''%''';
        elsif pPostcode is null and pAge &gt; 0 then 
           lsql := lsql||' AND ROUND((MONTHS_BETWEEN(sysdate,c.date_of_birth)/12)) = pAge';
        elsif pPostcode is not null and pAge &gt; 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;
  </code></pre>
  
  <p>Im new to plsql and even newer to dynamic sql soo any help/ suggestions would be greatly apreciated.</p>
  
  <p>Thanks Again</p>
  
  <p>Jon </p>