Re: Is Table Driven Logic possible?

From: <nasof_at_hotmail.com>
Date: Thu, 13 Aug 1998 13:26:11 GMT
Message-ID: <6quphj$mch$1@nnrp1.dejanews.com>

Although this is not the most elegant solution (you need to put in more error checking, etc..) It does just what you are asking. I did this as a test because I am planning on doing something similar for warehousing data...

create table temp_action ( action varchar2(10), the_proc varchar2(30) ); create table temp_data ( the_value varchar(20), the_date date );

create or replace procedure sp_run_test as

    curRunTestCursor integer;
    proc_name varchar2(30);
    the_statement varchar2(100);
    rows_processed number default 0;
BEGIN
    curRunTestCursor := dbms_sql.open_cursor;

    select the_proc into proc_name from temp_action where action='add';     the_statement := 'begin '||proc_name||'; end;'; dbms_output.put_line( the_statement );

    dbms_sql.parse( curRunTestCursor, the_statement, dbms_sql.native );     rows_processed := dbms_sql.execute(curRunTestCursor); commit;

    select the_proc into proc_name from temp_action where action='delete';     the_statement := 'begin '||proc_name||'; end;';     dbms_sql.parse( curRunTestCursor, the_statement, dbms_sql.native );     rows_processed := dbms_sql.execute(curRunTestCursor);

    dbms_sql.close_cursor( curRunTestCursor ); END; create procedure sp_temp_add as
BEGIN
    insert into temp_data values ('This is adding', sysdate); END; create procedure sp_temp_delete as
BEGIN
    insert into temp_data values ('This is deleting', sysdate); END; insert into temp_action values ('add', 'sp_temp_add'); insert into temp_action values ('delete', 'sp_temp_delete');

---

Run these scripts above and then do the following to test: execute sp_run_test;

select * from temp_data;

Good Luck,
Frank
In article <6qsp7v$8sl$1_at_nnrp1.dejanews.com>,   dejaron_at_my-dejanews.com wrote:
> Can you set up tables for use on Oracle to perform table-driven logic? For
> example, have a table populated as:
>
> action procedure_name
> ------ --------------
> add test_proc1
> add test_proc2
> change test_proc50
> change test_proc51
> change test_proc60
> delete test_proc1
> delete test_proc60
>
> and then perform a SELECT where action = 'add' and use all occurrences of the
> field procedure_name to call that procedure name stored in the field itself.
> So that I could then call test_proc1 and test_proc2 without actually knowing
> the procedures names (since they're in a table)?
>
> Any help would be greatly appreciated.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 13 1998 - 08:26:11 CDT