Updating Tables [message #1031] |
Fri, 22 March 2002 06:00 |
Amarsey
Messages: 17 Registered: March 2002
|
Junior 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 Tables [message #1033 is a reply to message #1031] |
Fri, 22 March 2002 11:32 |
Hersel Ahdout
Messages: 8 Registered: March 2002
|
Junior Member |
|
|
If you use this method, you need to use DBMS_SQL to compile the string representation of your SQL statement during execution.
If the tables are predefined (and few), the better method is to have a series of IF (if table_name='a') followed by the specific action for that table.
|
|
|
Re: Updating Tables [message #1062 is a reply to message #1031] |
Tue, 26 March 2002 09:52 |
oraboy
Messages: 97 Registered: October 2001
|
Member |
|
|
yeah..the logic is right!
but u need to know what is meant by 'late binding'!
I wud suggest you to read about EXECUTE IMMEDIATE which will just solve all your need.
Later on, for more complex tasks you can read and use DBMS_SQL . (not for this problem)
All the Best
oraboy
|
|
|