Re: Writing a general update procedure..

A few options:

1. Write a function which looks at all_tab_columns to generate the text for the procedure and the update statement; use DBMS_OUTPUT or UTIL_FILE to get this text out to the OS.
2. Use a scripting language with Oracle connectivity (oraperl, say) and do the same thing

You then edit that template to add the custom bits, and then put it back into the db.

With either option, if you have some common things you do with the update procedure, you could build custom functionality to generate that as well. If you get to the point where you can generate the entire procedure, you can directly put it in the database with DBMS_SQL

I think www.revealnet.com has something called PV/Generate which does some code generation of the sort you are looking. If you do this frequently enough to justify the cost, it may be worth the investment. Don't know how much it costs.

Arien

tjmxyz_at_my-deja.com wrote:

> Hi...
>
> I want to write a general Update Procedure.
>
> What I do now is for each table I have a function like.
>
> CREATE TABLE t1
> (
> col1 Number(2),
> col2 varchar2(20)
> );
>
> CREATE OR REPLACE PROCEDURE t1_update
> (incol1 number,
> incol2 char
> ) IS
>
> BEGIN
> update t1 set col1=incol1 and col2=incol2;
> END;
>
> More or less this is the idea.
> But what if I change the name of col1 to something else...
>
> I do this because there is some more processing for some records to
> other tables and such...
>
> But My problem is some tables have many columns and in this stage
> columns are changing frequently...
>
> How can I build a generalized function where
> the columns are generated at call time.
>
> Is there any other ideas for an update procedure???
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Aug 23 1999 - 11:17:46 CDT