Home » SQL & PL/SQL » SQL & PL/SQL » Passing :NEW as a Record
Passing :NEW as a Record [message #432701] Wed, 25 November 2009 17:31 Go to next message
Messages: 505
Registered: March 2005
Senior Member
I have an INSTEAD OF trigger on a view that has around 100 columns. I want to be able to trim all the VARCHAR2 types. I will do that by passing them to a procedure with IN OUT parameter(s). I would like to be able to pass just one parameter to that procedure. I know I can define a record type and assign each :NEW.col to my_rec.col and pass my_rec. Is there anyway I can define the function so it accepts :NEW?
Re: Passing :NEW as a Record [message #432763 is a reply to message #432701] Thu, 26 November 2009 03:01 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm not quite sure what you're looking for.

If I had to trim all the Varchar2 strings in an Instead Of trigger, I'd just write the Insert/Update parts as normal and wrap each VC column in a trim or rtrim call - at worst it's 7000 chrs to type, and it's te sort of thing a decent text editor can speed up greatly.
Re: Passing :NEW as a Record [message #432878 is a reply to message #432701] Fri, 27 November 2009 00:04 Go to previous message
Kevin Meade
Messages: 2099
Registered: December 1999
Location: Connecticut USA
Senior Member
No, there is now way to pass :NEW as a record variable.

Not sure about 11g but I have not seen anything that says otherwise. Sorry, you will have to do it the old fasioned way.

If you are desperate for it, you can always "reselect" the row into a record variable and pass the record variable around. But this my not work in all versions of oracle because of the rules of mutating tables. It will work in an instead-of-trigger and indeed, it is a good idea to select for update the row off the view before you do anything with it when using instead of triggers anyway so the record variable will be there for you anyway.

Good luck, Kevin
Previous Topic: By Setting the Parameter in Seconds can we stop the execution of the program
Next Topic: Problem with Packages (merged 3)
Goto Forum:

Current Time: Wed Oct 26 12:21:54 CDT 2016

Total time taken to generate the page: 0.15178 seconds