Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01403: no data found + During Trigger Execution. (Oracle 10g, SQL*Plus: Release 10.2.0.1.0, Windows 2003 Server)
ORA-01403: no data found + During Trigger Execution. [message #336764] Mon, 28 July 2008 23:12 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi,

I have created following trigger on oracle for reportincomingsms table.

CREATE OR REPLACE TRIGGER trg_ServiceRecovery AFTER INSERT ON dbo_SMSConnect.reportincomingsms
for each row
DECLARE
   pl_mobileno             VARCHAR2(50);
   pl_incomingmsgkey       NUMBER(20);
   pl_surveykey            VARCHAR2(20);
BEGIN
   SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber and rownum=1 ORDER BY requestreceivetime desc;

	insert into SurveyResponseAssociation values (pl_surveykey ,:new.pkey) ;
End;
/



but when i am performing insert


insert into reportincomingsms 
values(seq_reportincomingsms.nextval,'001','9969413222','hello from jigar','757525','15-Jan-08','done')



i am getting following error.

values(seq_reportincomingsms.nextval,'001','9969413222','hello
       *
ERROR at line 2:
ORA-01403: no data found
ORA-06512: at "DBO_SMSCONNECT.TRG_SERVICERECOVERY", line 6
ORA-04088: error during execution of trigger
'DBO_SMSCONNECT.TRG_SERVICERECOVERY'


Can anybody help me out solving this problem ???
Re: ORA-01403: no data found + During Trigger Execution. [message #336766 is a reply to message #336764] Mon, 28 July 2008 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I conclude that
 SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber and rownum=1 
           ORDER BY requestreceivetime desc;


is not returning any data.
I am just curious about the usefulness about having an ORDER BY clause when restricting result set to 1 row.
Would not you get the same results by eliminating ORDER BY?
Re: ORA-01403: no data found + During Trigger Execution. [message #336768 is a reply to message #336764] Mon, 28 July 2008 23:28 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ya you are right, I tried with

CREATE OR REPLACE TRIGGER trg_ServiceRecovery AFTER INSERT ON dbo_SMSConnect.reportincomingsms
for each row
DECLARE
   pl_mobileno             VARCHAR2(50);
   pl_incomingmsgkey       NUMBER(20);
   pl_surveykey            VARCHAR2(20);
BEGIN
   SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;
 insert into SurveyResponseAssociation values (pl_surveykey ,:new.pkey) ;
End;


but still result is same. no data found error.
Re: ORA-01403: no data found + During Trigger Execution. [message #336771 is a reply to message #336764] Mon, 28 July 2008 23:30 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

What value :new.sendernumber will return if i fire the bellow insert statement.

insert into reportincomingsms 
values(seq_reportincomingsms.nextval,'001','9969413222','hello from jigar','757525','15-Jan-08','done')



SQL> desc reportincomingsms 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PKEY                                               NUMBER(10)
 SMSID                                              VARCHAR2(20 CHAR)
 SENDERNUMBER                                       VARCHAR2(20 CHAR)
 MESSAGE                                            VARCHAR2(400 CHAR)
 DNIS                                               VARCHAR2(20 CHAR)
 RECEIVETIME                                        DATE
 STATUS                                             VARCHAR2(20 CHAR)

[Updated on: Mon, 28 July 2008 23:31]

Report message to a moderator

Re: ORA-01403: no data found + During Trigger Execution. [message #336774 is a reply to message #336764] Mon, 28 July 2008 23:39 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

I have removed the two unused declared variable and now the error is pointing to line no 4 that is FOR EACH ROW.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER trg_ServiceRecovery
  2   AFTER INSERT
  3   ON dbo_SMSConnect.reportincomingsms
  4   FOR EACH ROW
  5  DECLARE
  6     pl_surveykey            VARCHAR2(20);
  7  BEGIN
  8     SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
  9             WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;
 10   INSERT INTO SurveyResponseAssociation VALUES (pl_surveykey ,:new.pkey) ;
 11* End;
 12  /

Trigger created.

SQL> insert into reportincomingsms 
  2  values(seq_reportincomingsms.nextval,'001','9969413222','hello from jigar','757525','15-Jan-08'
,'done')
  3  ;
values(seq_reportincomingsms.nextval,'001','9969413222','hello from jigar','757525','15-Jan-08','don
       *
ERROR at line 2:
ORA-01403: no data found
ORA-06512: at "DBO_SMSCONNECT.TRG_SERVICERECOVERY", line 4
ORA-04088: error during execution of trigger
'DBO_SMSCONNECT.TRG_SERVICERECOVERY'

[Updated on: Mon, 28 July 2008 23:41]

Report message to a moderator

Re: ORA-01403: no data found + During Trigger Execution. [message #336778 is a reply to message #336774] Mon, 28 July 2008 23:54 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Can't you see where is the problem.

Why no data found error comes??

It's clearly states that NO DATA.

Regards,
Rajat
Re: ORA-01403: no data found + During Trigger Execution. [message #336780 is a reply to message #336764] Tue, 29 July 2008 00:02 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

oh yeah..

i got you..

thanks a lot...
Re: ORA-01403: no data found + During Trigger Execution. [message #336781 is a reply to message #336764] Tue, 29 July 2008 00:05 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 Guideline as stated in URL above


If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Re: ORA-01403: no data found + During Trigger Execution. [message #336782 is a reply to message #336764] Tue, 29 July 2008 00:12 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

As Rajat stated my select statement was not returning any value,

i tried to run it separately passing the :new.sendernumber manually.

as bellow and it returned no value...

SQL> SELECT COUNT(*) FROM reportsmssurvey WHERE MOBILENO='9969413222';

  COUNT(*)
----------
         0
Re: ORA-01403: no data found + During Trigger Execution. [message #336788 is a reply to message #336764] Tue, 29 July 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trigger is a very bad way to do complex stuff, use procedure.
In addition, this will lead you to inconsistent result (in the meaning of what you're trying to do) in multiuser environment.

Regards
Michel
Re: ORA-01403: no data found + During Trigger Execution. [message #336821 is a reply to message #336788] Tue, 29 July 2008 02:30 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Michel,

Thanks for your suggestion, Will definitely consider.
Re: ORA-01403: no data found + During Trigger Execution. [message #336959 is a reply to message #336768] Tue, 29 July 2008 08:09 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
naikjigar wrote on Tue, 29 July 2008 00:28

CREATE OR REPLACE TRIGGER trg_ServiceRecovery AFTER INSERT ON dbo_SMSConnect.reportincomingsms
for each row
DECLARE
   pl_mobileno             VARCHAR2(50);
   pl_incomingmsgkey       NUMBER(20);
   pl_surveykey            VARCHAR2(20);
BEGIN
   SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;
 insert into SurveyResponseAssociation values (pl_surveykey ,:new.pkey) ;
End;


You may have solved your problem, but it was by accident. You still have an ORDER BY clause in here. If your SELECT returns more than one row, you will get a different exception. I am not convinced that you have solved your problem. My guess is that it works for the single example that you tried.
Re: ORA-01403: no data found + During Trigger Execution. [message #336961 is a reply to message #336959] Tue, 29 July 2008 08:12 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Yeah i made it order by <my timestamp database fields> desc and rownum=1 which means the last record inserted in the database.
Re: ORA-01403: no data found + During Trigger Execution. [message #336963 is a reply to message #336961] Tue, 29 July 2008 08:13 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
naikjigar wrote on Tue, 29 July 2008 09:12
Yeah i made it order by <my timestamp database fields> desc and rownum=1 which means the last record inserted in the database.


Incorrect. WHERE clause gets executed before ORDER BY clause.
Re: ORA-01403: no data found + During Trigger Execution. [message #336965 is a reply to message #336963] Tue, 29 July 2008 08:16 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

yeah that is right.. I just gave a logical line which will return one row only.
Re: ORA-01403: no data found + During Trigger Execution. [message #336968 is a reply to message #336965] Tue, 29 July 2008 08:23 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Then, as anacedent asked, what is the purpose of an ORDER BY clause?
Re: ORA-01403: no data found + During Trigger Execution. [message #336975 is a reply to message #336968] Tue, 29 July 2008 08:47 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

I used order by to get the last inserted row,

Actually i had the same Trigger in SQL Server

and the Select query was like

Select TOP 5 .. .. from ... .. to get the same result in oracle i used order by and then rownum.

i guess this will give me the same result as SQL Server's TOP 1 query....
Re: ORA-01403: no data found + During Trigger Execution. [message #336977 is a reply to message #336975] Tue, 29 July 2008 09:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
naikjigar wrote on Tue, 29 July 2008 15:47
i guess this will give me the same result as SQL Server's TOP 1 query....

joy_division wrote on Tue, 29 July 2008 15:13
Incorrect. WHERE clause gets executed before ORDER BY clause.

Maybe you need to repeat it once more:
First, the WHERE clause (within the ROWNUM condition) is evaluated.
Then, the resultset is ordered by the columns in the ORDER BY clause.

The correct TOP N query is described e.g. in http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_select_the_TOP_N_rows_from_a_table.3F. The third example shows using ROWNUM in this type of query.
Re: ORA-01403: no data found + During Trigger Execution. [message #336994 is a reply to message #336764] Tue, 29 July 2008 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
 SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;
 SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime;
 SELECT  pkey INTO pl_surveykey FROM reportsmssurvey
           WHERE mobileno = :new.sendernumber;

Since the INTO clause guarantees at most 1 row/value is returned,
then all 3 statements above produce the same results. Right?
So, once again, why is the ORDER BY included/needed/(ab)used?
Re: ORA-01403: no data found + During Trigger Execution. [message #337111 is a reply to message #336994] Tue, 29 July 2008 23:33 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Quote:
SELECT pkey INTO pl_surveykey FROM reportsmssurvey
WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;
SELECT pkey INTO pl_surveykey FROM reportsmssurvey
WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime;
SELECT pkey INTO pl_surveykey FROM reportsmssurvey
WHERE mobileno = :new.sendernumber;



all of the three queries might return exception as they all might return more then one row.

SELECT pkey INTO pl_surveykey FROM reportsmssurvey
WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc;

To make this query return only one row,I have used where rownum=1 and order by requestreceivetime desc will give me the last row inserted.


rownum requestreceivetime

1 ... 16:15
2 ... 16:14
3 ... 16:13
4 ... 16:12
5 ... 16:09
6 ... 16:05
7 ... 16:01

The query will return me the first record.

Re: ORA-01403: no data found + During Trigger Execution. [message #337112 is a reply to message #336764] Tue, 29 July 2008 23:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The query will return me the first record.

Post DDL, DML, complete cut & paste SQL to substantiate your claim.
Re: ORA-01403: no data found + During Trigger Execution. [message #337113 is a reply to message #337111] Tue, 29 July 2008 23:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
To make this query return only one row,I have used where rownum=1
OK
Quote:
and order by requestreceivetime desc will give me the last row inserted.

Wrong. It has already been stated (several times) that this is wrong, you have even been pointed towards the correct way to perform TOP N in Oracle. Please read the link.
Re: ORA-01403: no data found + During Trigger Execution. [message #337115 is a reply to message #336764] Wed, 30 July 2008 00:05 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

yes you were right, i would be in trouble, i didn't really noticed that,

SELECT  pkey into pl_surveykey from (select pkey FROM reportsmssurvey
          WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc)
        where rownum=1;


Thanks a lotttt....
Re: ORA-01403: no data found + During Trigger Execution. [message #337119 is a reply to message #337112] Wed, 30 July 2008 00:29 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

  1  CREATE OR REPLACE TRIGGER trg_ServiceRecovery
  2   AFTER INSERT
  3   ON dbo_SMSConnect.reportincomingsms
  4   FOR EACH ROW
  5  DECLARE
  6     pl_mobileno             VARCHAR2(50);
  7     pl_incomingmsgkey       NUMBER(20);
  8     pl_surveykey            VARCHAR2(20);
  9  BEGIN
 10     SELECT  pkey into pl_surveykey from (select pkey FROM reportsmssurvey
 11               WHERE mobileno = :new.sendernumber ORDER BY requestreceivetime desc)
 12             where rownum=1;
 13   INSERT INTO SurveyResponseAssociation VALUES (pl_surveykey ,:new.pkey) ;
 14* End;
 15  /

Trigger created.

Re: ORA-01403: no data found + During Trigger Execution. [message #337130 is a reply to message #337119] Wed, 30 July 2008 00:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add the explicit column-list in the insert. That way you will not run into problems if you add a nullable column to your table.
It's a good habit to stick to.
Previous Topic: Create random Date/and Time
Next Topic: Collections
Goto Forum:
  


Current Time: Sat Dec 10 05:08:29 CST 2016

Total time taken to generate the page: 0.14741 seconds