Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel