raksrules
Oracle
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
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
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.
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.
