Home » SQL & PL/SQL » SQL & PL/SQL » Updating different tables when the table name is passed as a Parameter
Updating different tables when the table name is passed as a Parameter [message #38123] Fri, 22 March 2002 05:42 Go to next message
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 Go to previous message
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.
Previous Topic: SQL ORA-00923
Next Topic: if then else
Goto Forum:
  


Current Time: Thu Mar 28 06:41:08 CDT 2024