How to use Lookup table in Oracle

[nvshah]

Hello,
I wrote big pl/sql script containing 19 cursors. Well it works very fine & its been in production since long time.

Now I have come some changes in that and those changes are not permanent. I may have to change again & recompile the script again. In these changes I'm putting one line condition using IF statement. Instead of doing that again & again I thought that I should setup one Lookup table & I should put that condition into that lookup table. And in my PL/SQL script I should store that column into variable and check it using IF (that variable) THEN... statement. But its not working. I don't know whether there is any function to evaluate that condition or not.

If any of you are aware of Clipper that we can create a Codeblock in clipper & we can also store that as a character string into clipper database. And later we can evaluate using EVAL() function available in clipper. That's same thing I'm trying to do here.

I will appreciate your help.
Thanks,

Nikhil Shah

 

[sem]

You may create packaged boolean variable and make assignment dynamically by EXECUTE IMMEDIATE. Then you may use it in condition:

create or replace package vars as
begin
test boolean;
end;
/

declare
mcondition varchar2(2000);
begin
<begining of static part>
select condition into mcondition
from condition_table where ...
execute immediate 'begin vars.test:=mcondition; end;';

if vars.test then
<optional part>
end if;

<end of static part>

end;
/


Another idea is to create the script dynamically by spooling it into file and then executing it:

spool some_file
select <begining of static part>
||'if '||condition||' then '
||<optional part> ||' end if;'
||<end of static part>
from condition_table where ...
/
spool off
@some_file

 

[nvshah]

Thanks for your help Sem, But it doesn't seems to be working. I'm including the code below. Try to run.

Thanks again.
Nikhil Shah

create or replace package tst_vars as
test_cond boolean;
end;
/
create or replace procedure tst_chkval AS

mcondition varchar2(500);

Cursor test_cur IS Select empno,ename from emp;

begin

select ctrl_val into mcondition from ctrlext where ctrl_eid='001';
dbms_output.put_line(mcondition);

for cur_emp In test_cur
Loop

execute immediate 'tst_vars.tst_cond := mcondition;';

if tst_vars.test_cond then
dbms_output.put_line('THIS IS KING');
end if;

End loop;

end;
/

 

[sem]

execute immediate 'tst_vars.tst_cond :='|| mcondition||';';

If you place mcondition within qoutes it will not be evaluated and probably produces error. The idea is that it should be EVALUATED. In your case the expression is just a string 'condition' rather then its value.