Updating different tables when the table name is passed as a Parameter [message #38123] |
Fri, 22 March 2002 05:42 |
Godwin
Messages: 37 Registered: January 2002
|
Member |
|
|
Hello,
Can any one please help me. Am just one and half yrs old in practising Oracle.
My problem is that I have some specific tables which have two null fields(ie signature and sign date fields) that need to be updated by a different user other than the real data entry clerk. In fact those fields must be hidden from the entry clerk but be availabe to the rignt user. Thus I thought of writing PLSQL procedure that will have 4 parameters (i.e enter_table_name,table_primary_key,signature,sign_date)
Thus the draft procedure should be like this:
procedure(enter_table_name varchar2,table_primary_key varchar2,signature varchar2,sign_date date)
as
mytable varchar2;
begin
select TABLE_NAME into mytable
from ALL_OBJECT
where TABLE_NAME = enter_table_name;
begin
if mytable = enter_table_name then
update enter_table_name
set signature = signature
set sign_date = signdate
where table_name.primary_key =enter_primary_key;
commit;
end;
end;
Well this is the rough idea i have about this problem.
Am i on the right path? Can you offer some suggestions?
Thank u in advance.
|
|
|
Re: Updating different tables when the table name is passed as a Parameter [message #38130 is a reply to message #38123] |
Fri, 22 March 2002 14:27 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
You should use dynamic SQL (this example uses native dynamic SQL available in 8i and up) and bind variables to handle this:
create or replace procedure my_proc(
p_table_name in all_tables.table_name%type,
p_key in varchar2,
p_signature in varchar2,
p_sign_date in date)
as
begin
execute immediate
'update ' || p_table_name ||
' set signature = :signature,' ||
' sign_date = :signdate' ||
' where primary_key = :keyvalue'
using p_signature, p_sign_date, p_key;
commit;
end;
This assumes that the primary key column has the same name for all of the tables involved.
|
|
|