Introduction
It's been a long time since I'm using stored procedures in both Oracle and SQL server to do my programmings and this is what all other programmers are doing too. But when we move everything in spc, then sometimes we need to do search inside our spc too and return the result. So I was asking my own how I can generate an spc in Oracle that recieves parameters from caller and does a dynamic search based on what is passed to.
Certainly, I need to have a flexible dynamic
WHERE at the end of my spc that has to be generated based on input parameters.
The following example shows how easily we are able to do this.
Please pay attention:
● Oracle is not case sensitive and this type of coding is just for reading it better.
● 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 length greater than zero then it will be used inside
where clause too
● Result will be returned as
sys_refcursor
Using the code
This is very simple code and I think it is not needed to be described.
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;
Give me your ideas about this topic here or in my website:
www.yazdinezhad.com.
