Generic stored proc

[Romanichine]

Hi,

I have a table containing 60+ fields and different parts of our application are updating this table. There's a part modifying all the fields, and the others only modify some fields.

We have many stored procs to handle this, one that has receives all the field values, another that has three parameters, another has ten etc...

It seems to me that we should be able to have only one procedure to handle all the situations.

Any idea?
--
Roman

 

[rrrkrishnan]

Hi Roman
Its been sometime since I have used Oracle, But I think it can done by writing a stored procedure with 60+ paramaters, one for each of the cols that are being updated. Default all to null and while calling the procedure you can use positional notation or explicit assignment. In the update stmt, you can use decode to check the value of the parameter and if it is null, update the column with the same value that exists for that column and if the value of the parameter is not null then update it with the new value. You might have to tweak this logic a bit depending on the application (ie. if a null value has to replace a non null value).
Of course decodes are expensive and hence performance should be weighed against efficient code.

Hope this helps.

 

[contactop]

no, the btter way is have different procedures, as out them up in a package, this not only gives u access rights of the user to control.
Yaa, if still u want to continue off, then u can write it. but that will be very much cucumbersome.
as one is updateing, other is inserting etc..
bye