how to use a procedure parameter in a query

Question

Hi..
how do i access the procedure parameters inside the same procedure using a query
for example: see this procedure

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
--some local variables  
begin  
merge into tgt_table
using subquery --(here is what i need to use the parameters)  
on some condition  
when matched then  
update the tgt table  
when not matched then  
insert the table;  
end game;

In above procedure and in merge statement, i need a query such that it uses the parameters value as a table reference and using those values it either updates or inserts into the table based on the condition given.

Help me please. Thanks in advance

Answer 1

Please read http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/06_ora.htm#sthref777

Answer 2

You would need to use dynamic SQL if your parameters define the table to use - something like:

procedure game(left in tab.left%type,right in tab.right%type,...)  
is  
    --some local variables  
    l_sql long;
begin  
    l_sql := 'merge into tgt_table'
             || ' using ' || left
             || ' on some condition'  
             || ' when matched then'  
             || ' update ' || right
             || ' when not matched then'  
             || ' insert the table';  
    execute immediate l_sql;
end game;

However, you have a lot more work left to do, since the condition, update and insert clauses all need to change according to the tables being used. I'm not convinced this procure will be particularly useful in fact.