Home » SQL & PL/SQL » SQL & PL/SQL » How to convert select stmt into update (Oracle 9i, Windows xp)
icon14.gif  How to convert select stmt into update [message #346464] Mon, 08 September 2008 11:29 Go to next message
james_aron
Messages: 32
Registered: July 2007
Location: chennai
Member
Hi all,
I have a field called dml_stmt, i am getting the dml statement has input from the user.

My requirement is, if user is giving "update set col_name = 'xyz' from table_name where codition = 'aa'", before updating the table, i need to get old values from the table and put it in the audit table

For that,i need to convert those update statement into select stmt and need to execute the query to get the data and then i will put it in the audit table..

can anyone guide how to convert the update or delete stmt into select(need to write in pl/sql)

Please do needfull things ......

Regards,

Jame
Re: How to convert select stmt into update [message #346465 is a reply to message #346464] Mon, 08 September 2008 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: How to convert select stmt into update [message #346472 is a reply to message #346464] Mon, 08 September 2008 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Regards
Michel

[Edit: Fix URL]

[Updated on: Mon, 08 September 2008 23:28]

Report message to a moderator

Re: How to convert select stmt into update [message #346549 is a reply to message #346472] Mon, 08 September 2008 18:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Funny how Tom Kyte would have answered the question directly without re-directing you to a (dead) manual link or a FAQ guide...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:734825535375

Michel's chapter is a useful ref though...
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#g1043102

[Updated on: Mon, 08 September 2008 18:35]

Report message to a moderator

Re: How to convert select stmt into update [message #346578 is a reply to message #346549] Mon, 08 September 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Funny how Tom Kyte would have answered the question directly

Maybe this is the difference between someone that is paid to do it and those who do it for free. Wink

I don't think a generic trigger is what OP was searching for (he only talked about one table), he just did not know about OLD record, so the answers (sorry for wrong url which was correct but local, I think information given was sufficient to locate it, doesn't it?).

Regards
Michel
Re: How to convert select stmt into update [message #346605 is a reply to message #346578] Tue, 09 September 2008 02:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure Tom's not paid to run the AskTom site. I think it's something he does as a hobby.
Re: How to convert select stmt into update [message #346622 is a reply to message #346605] Tue, 09 September 2008 02:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't mean it is directly paid for this and I agree with your opinion. He himself said that he loves to explain things and make people learn.

Regards
Michel
Previous Topic: replace column data wserching tab
Next Topic: Problem with updating a table
Goto Forum:
  


Current Time: Fri Dec 09 13:43:43 CST 2016

Total time taken to generate the page: 0.20201 seconds