Home » SQL & PL/SQL » SQL & PL/SQL » :NEW in cursor ?
:NEW in cursor ? [message #424857] Tue, 06 October 2009 04:39 Go to next message
dietbeck
Messages: 6
Registered: October 2009
Junior Member
Hi,

How can I create a cursor which contains the rows inserted in an "after insert" trigger ?

The :new does not seem to work or at least I get the error:

Error: ORA-04082: NEW or OLD references not allowed in table level triggers

In fact I am having joins in the statement thus I need values from the new rows combined with values from other tables which are joined by a foreign key

Thanks
dietbeck

[Updated on: Tue, 06 October 2009 04:46]

Report message to a moderator

Re: :NEW in cursor ? [message #424858 is a reply to message #424857] Tue, 06 October 2009 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
:NEW and :OLD are only allowed in row level triggers - ones with the 'FOR EACH ROW' clause included.
Re: :NEW in cursor ? [message #424867 is a reply to message #424857] Tue, 06 October 2009 05:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
it is not clear what your question is? To bad you did not post some of your "not working" code for us to look at. That might have clear things up a bit. It amazes me how someone can say "this don't work" but then fail to show the "this".

Please post a short sample of the none working code.

Kevin
Re: :NEW in cursor ? [message #424936 is a reply to message #424857] Tue, 06 October 2009 11:12 Go to previous messageGo to next message
dietbeck
Messages: 6
Registered: October 2009
Junior Member
Hi,

ok here some code and an error. How do I reference the ":new" fields ?

Thanks
Dietbeck

cursor sci_cur is
select :new.vendor, :new.catalog, :new.cost, abbr, :new.size, unit, :new.quantity, :new.username, :new.updateusername, createusername
etc

OPEN sci_cur;
FETCH sci_cur into sci;
IF sci_cur%FOUND THEN
emailmessage := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf || 'From: <'|| sci.createusername ||'>'
|| crlf || 'To: '|| 'Recepient'
|| crlf || ''
|| crlf || 'Vendor: ' || crlf || sci.vendor
|| crlf || 'Catalog: ' || crlf || sci.catalog
|| crlf || 'Cost: ' || crlf || sci.cost || sci.abbr
|| crlf || 'Size: ' || crlf || sci.size || sci.unit
|| crlf || 'Quantity: ' || crlf || sci.quantity
|| crlf || 'Updater: ' || crlf || sci.updateusername;

Error(33,62): PLS-00302: component 'VENDOR' must be declared
Re: :NEW in cursor ? [message #424937 is a reply to message #424936] Tue, 06 October 2009 11:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no vendor variable or element in your declarations you didn't post.

Regards
Michel
Re: :NEW in cursor ? [message #424939 is a reply to message #424857] Tue, 06 October 2009 11:21 Go to previous messageGo to next message
dietbeck
Messages: 6
Registered: October 2009
Junior Member
Maybe I am not understanding, but "vendor" is a field from the select statement in the cursor which I want to later assign in the message text referenced through the cursor variable.
Re: :NEW in cursor ? [message #424944 is a reply to message #424939] Tue, 06 October 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Maybe I am not understanding
Agreed!

>Error(33,62): PLS-00302: component 'VENDOR' must be declared
The problem is on line #33; which you did not provide.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: :NEW in cursor ? [message #424949 is a reply to message #424857] Tue, 06 October 2009 11:46 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
The :new and :old syntax is used exclusively by row level triggers to identify the values of the current row being modified. You can refer to them in such triggers but you can't refer to them anywhere else and selecting them in a cursor is basically meaningless.

So this:
CREATE OR REPLACE TRIGGER TABLE_TRIG
BEFORE INSERT ON <TABLE>
FOR EACH ROW
DECLARE

  l_val NUMBER;

BEGIN

  SELECT :NEW.col1 INTO l_val FROM <TABLE>
  WHERE <some criteria>;
  
END;


Is logically equivalent to:
CREATE OR REPLACE TRIGGER TABLE_TRIG
BEFORE INSERT ON <TABLE>
FOR EACH ROW
DECLARE

  l_col_val NUMBER := :new.col1;
  l_val NUMBER;

BEGIN

  SELECT l_col_val INTO l_val FROM <TABLE>
  WHERE <some criteria>;
  
END;


And if you're not in a row level trigger that syntax won't compile at all.

Which is to say you can reference the new/old stuff without needing to select it, assuming you can reference it at all.

Why don't you tell us what you're trying to achieve here because whatever you're trying to do your current approach is almost certainly flawed.

Also when posting code can you please use code tags - see the orafaq forum guide if you don't know how.
Re: :NEW in cursor ? [message #424963 is a reply to message #424857] Tue, 06 October 2009 15:38 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
if you are trying to send an email every time you enter a record then simply use a row level trigger and

emailmessage := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss')
|| crlf || 'From: <'|| :new.createusername ||'>'
|| crlf || 'To: '|| 'Recepient'
|| crlf || ''
|| crlf || 'Vendor: ' || crlf || :new.vendor
|| crlf || 'Catalog: ' || crlf || :new.catalog
|| crlf || 'Cost: ' || crlf || :new.cost || :new.abbr
|| crlf || 'Size: ' || crlf || :new.size || :new.unit
|| crlf || 'Quantity: ' || crlf || :new.quantity
|| crlf || 'Updater: ' || crlf || :new.updateusername;
Re: :NEW in cursor ? [message #425257 is a reply to message #424963] Thu, 08 October 2009 05:28 Go to previous messageGo to next message
dietbeck
Messages: 6
Registered: October 2009
Junior Member
Hi,

I wanted to thank everyone for their help so far. I am trying to get better posting what is really required and using tags.

I had a row-level after insert trigger and it finally does work, extracting information from a table, including it in a message body and send it via email. Thats was what I wanted to achieve.

I have a remaining problem, namely with the following:

    if :new.itemid is not null then
      select distinct name_upper into cartitem_var.substance from cartitemname 
        where cartitemid = :new.itemid
        and isdefault = 'Y' 
        and isactive = 'Y';

Exception message: ORA-20001: An error was encountered - 100 -ERROR- ORA-01403: no data found
ORA-06512: at "SUB.SENDEMAIL_TRG", line 73
ORA-04088: error during execution of trigger 'SUB.SENDEMAIL_TRG'


The problem is, I believe, that the select is on a table which is inserted at the same time as the table where the trigger is on. Thus I assume this is a "mutating" table and thus the data are not found, since they are inserted at the same time as the insert of the row where the trigger is fired. Is there any way I can get around this problem getting the value I need from the other table ?

Thanks
dietbeck

[Updated on: Thu, 08 October 2009 05:38]

Report message to a moderator

Re: :NEW in cursor ? [message #425280 is a reply to message #424857] Thu, 08 October 2009 07:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
If it was mutating table, it would have said mutating table somewhere not no-data-found. When oracle says no-data-found, the most likely cause is that you got a no-data-found.

You need to go find it. This means doing some basic detective work. You need to either put messages in your code or raise your own errors till you find the statement that is causing the error.

no-data-found has two common causes:

1) you have a singleton select that is returning no rows
2) you have referenced an associative array element that does not exist.

  1  declare
  2     v1 number;
  3  begin
  4     select 1 into v1 from dual where 1 = 0;
  5* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


  1  declare
  2     type ta_number is table of number index by binary_integer;
  3     va_number ta_number;
  4     v1 number;
  5  begin
  6     v1 := va_number(1);
  7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

find yours. If you believe that the select statement you show is causing the error then figure out why the row you are looking for is not in the table. My guess is the row is not there because it have not been inserted yet.

Additionally you should reconsider sending email in a table trigger. This has long been frowned upon because of the way table triggers work. There are two issues you need to be concerned about:

1) failure of the trigger after the email is sent.
2) silent rollback and restart of the trigger after the email is sent.

Consider this:

create or replace trigger <whatever>
...
begin

   send_mail;

   raise zero_divivde; -- transaction rolls back? what happens to email that was sent?

end;
/


In the above code, your email will be sent, even though the data change that caused the email to happen will itself appear to have never happened because it was rolled back. What does this mean to you?

Consider this:

create or replace trigger <whatever>
...
begin

   send_mail;

   -- silent trigger rollback and restart happens.

   -- trigger finally finishes without error

end;
/


The above code is my pitiful attempt to describe to you a feature of table based triggers. Oracle commonly does a silent rollback and restart of triggers in order to make sure things run smoothly. Locking often is a cause of this where oracle is required to re-read rows for various reasons. There is an example of how to cause this to happen on the web but I can't find it right now.

The important thing for you to understand that in the above scenario, your email will be sent twice. What does that mean to you?

Go to asktomhome and do some searching on this topic, he explains it well. Also do some googling about it.

In the end the basic issue is you are doing non-transaction work inside your transaction. This work cannot be rolled back if a rollback is done for some reason in your trigger (either because of failure, or becaues of silent restart). Either way one of the two things above will happen.

There are other ways to send email without this issue. One being to use a method that is transaction dependent. I think queueing may be the preferred method.

Anyone else got comments about this? Maybe know a webpage with more detail?

Kevin
Re: :NEW in cursor ? [message #425287 is a reply to message #424857] Thu, 08 October 2009 07:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, here is Tom Kyte talking about trigger restart.

here is what the oracle manuals have to say.

Kevin x79427

[Updated on: Thu, 08 October 2009 07:08]

Report message to a moderator

Re: :NEW in cursor ? [message #425288 is a reply to message #425280] Thu, 08 October 2009 07:09 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Kevin Meade wrote at Thu, 08 October 2009 17:31
Maybe know a webpage with more detail?


In Tom Kyte's 3 part blog

Part-I
Part-II
Part-III

And in his Asktom site.
Asktom
Previous Topic: VARCHAR2 to Date Format Conversion
Next Topic: Initializing varray inside another varray
Goto Forum:
  


Current Time: Fri Sep 30 00:28:40 CDT 2016

Total time taken to generate the page: 0.09069 seconds