PLSQL Update procedure

[simdan42]

How can I write an Update Procedure that can be used for every column and table in my database, by passing the table name, column name and new value to the procedure as arguements?

 

[karluk]

To get this to work, you would have to use some type of dynamic sql, probably "execute immediate". However, I would suggest you refrain from taking this approach. Writing this kind of "universal update" procedure defeats the purpose behind putting sql statements in a stored procedure. You lose most or all of the ability to edit the updates for validity. Instead of encoding business rules that determine whether it makes sense to have certain values in a given column, you end up blindly applying whatever updates get passed to the procedure.

 

[simdan42]

Well thats what I need to do unless you have any other sugestion on how to avoid writing a one update procedure for every column in every table. I do not want to have to write 60 different procedures to do that.

 

[Turkbear]

One major issue you will face is handling multiple updates to the same table..How are you going to indicate multiple values and columns if you only pass one parameter for each -If you create the SP with multiple IN parameters, you will need to pass all of them for each call or get an error..

It may be time to re-examine your data model and the methods used to update the tables...


The User Interface application used to update should be designed so as to be able to handle the DML statements generated by the user's actions..

 

[sem]

create procedure update_any(
ptable in varchar2,
pwhere in varchar2, -- you need thes to specify the row(s)
pcolumn in varchar2,
pvalue in varchar2)
is
begin
execute immediate 'update '||ptable
||' set '||pcolumn||'='||pvalue
||' where '||pwhere;
end;


The procedure is simple, though quite unuseful (I agree with Karluk and Turkbear). With this procedure you may update 1 column in a time, but to update 2 columns you should run it twice etc. Furthermore you should maintain value passed carefully or create the similar procedures for datatypes other than VARCHAR2. Another drawback is security reason: one who's granted EXECUTE on this procedure may update ANY table in your schema unless you use AUTHID clause.

So, again, I suggest you to create 60 DIFFERENT PROCEDURES if you have 60 tables and DO NEED to update using procedures only.