Introduction
Its long time that im using stored procedures in both oracle and sql server to do my programmings and this is all other programmers are doing too.but when we move every thing in spc then some times we need to do search inside our spc too and return the result. so i was asking my own how i can generate a spc in oracle that recices parameters from caller and do a dynamic search based on what is passed to.
certainly i need to have a flexible dynamic WHERE at end of my spc that have to be generated based on input parameters.
the following example shows how easy we are able to do this.

Please Attention :
●Oracle is not case sensitive and this type of coding is just for better reading it.
●in this example we have two parameters , a numeric and a string. if Numeric parameter be >0 then it will be used inside the where clause and also if the string one has a lengh greater than zero then it will be used inside where clauase too
●result will be returned as sys_refcursor

Using the codethis is very simple code and i think not needed to describe .

create or replace PROCEDURE SP_MyProc
( Para_01_IN IN NUMBER, Para_02_IN In Nvarchar2 ,RESULT_OUT OUT sys_refcursor )
IS
Begin
declare
SelectClause clob; --this will save the dynamic sql statement of search
whereClause clob;
begin
SelectClause:='';
whereClause:='';
SelectClause:=' select * from MyTable';
whereClause := ' where {FixedConditionsHere} '

--Generating dynamic conditions
if Para_01_IN != 0 then
whereClause:=whereClause || ' and Para_01= '|| to_char(Para_01_IN);
end if;
if LENGTH(Para_02 ) > 0 then
whereClause:=whereClause||' and Para_02 ='||Para_02_IN ;
end if;
-- This will open the cursor dynamically based on generated SQL commands
open RESULT_OUT for SelectClause||whereClause ;--using whereClause;
commit;
end;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,'Error code ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1 , 200));

End;


edit by admin: no contact info or advertising permitted.