Need help with PL SQL code unit

raksrules

raksrules

Elite
I need some help with a small piece of PL SQL code. I am not able to figure out how to make this query.

Requirement is that a concurrent program is run with parameters and one of them i_num_org_id is non mandatory so it can come as NULL

Now in an existing code which i have to change, it uses a query as

Code: 
SELECT xyz

  FROM abc_table

WHERE <various conditions>

  AND DECODE(i_num_org_id,NULL,1,table.organization_id) = NVL(i_num_org_id,1);

Now with the above way, if the program is run with some value for i_num_org_id or run as normal query (with NULL as the value) inside a PLSQL procedure/package then it runs fine.

This query if you run in Toad etc then too it will work fine but if it is made a dynamic SQL and then used as either EXECUTE IMMEDIATE or opened as a cursor then we get a "Missing expression"

I created this small anonymous block to test this and this will go into missing expression error

Code: 
declare

l_num_org_id    NUMBER := NULL;

l_temp  VARCHAR2(100);

l_sql varchar2(1000);

begin

l_sql := 'SELECT '||''''||'abcd'||''''||'

          FROM DUAL

          WHERE 1=1

            AND decode('||i_num_org_id||',NULL,1,100) = NVL('||i_num_org_id||',100)';

dbms_output.put_line('----------------------------------------------------');

dbms_output.put_line(l_sql);

dbms_output.put_line('----------------------------------------------------');

execute immediate l_sql INTO l_temp ;       

dbms_output.put_line('date is: ' || l_temp);

end;

/

Can anyone tell me how i can reformat this query so that even if NULL value comes for i_num_org_id then it is handled.

I am aware about CASE but that cannot be used in WHERE clause i guess.
 
Back
Top