Home » SQL & PL/SQL » SQL & PL/SQL » error during inserting(ORA-01403: no data found)
error during inserting(ORA-01403: no data found) [message #282144] Wed, 21 November 2007 00:15 Go to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
hi

i m inserting a row in a table,the description of table is

SQL> desc fac_vch_m
Name Null? Type
----------------------- ---------------------
VCH_LOCN_CODE VARCHAR2(6)
VCH_VCH_TYPE VARCHAR2(6)
VCH_VCH_# VARCHAR2(18)
VCH_VCH_DATE NOT NULL DATE
VCH_ATHR_BY VARCHAR2(6)
VCH_ENTR_BY VARCHAR2(6)
VCH_ENTR_DATE DATE
DB_AMT NUMBER(16,2)
CR_AMT NUMBER(16,2)
NET_AMT NUMBER(16,2)
NET_AMT_SIGN VARCHAR2(6)
VCH_ATHR_FLAG VARCHAR2(1)
VCH_RJTD_FLAG VARCHAR2(1)
VCH_RJTD_BY VARCHAR2(6)
VCH_BANK_CODE VARCHAR2(6)

and the query which i m using for insertion is

SQL> ;
1 INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#
2 ,VCH_VCH_TyPe,VCH_VCH_DATE, DB_AMT,
3 CR_AMT,VCH_ENTR_BY,VCH_ENTR_DATE)
4 VALUES
5 ('400001','0700012','0110',SYSDATE,1,1,
6* 'A', SYSDATE)
SQL> /
INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#
*
ERROR at line 1:
ORA-01403: no data found


regards
jitender


[Updated on: Wed, 21 November 2007 00:25]

Report message to a moderator

Re: error during inserting(ORA-01403: no data found) [message #282148 is a reply to message #282144] Wed, 21 November 2007 00:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
check the first post in this forum on how to format your post.

i tried your code and it works for me.pls check again

regards,

Re: error during inserting(ORA-01403: no data found) [message #282150 is a reply to message #282148] Wed, 21 November 2007 00:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Any triggers on that table?

Ross Leishman
Re: error during inserting(ORA-01403: no data found) [message #282168 is a reply to message #282150] Wed, 21 November 2007 01:11 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
THX FOR REPLY

actually there is no trigger on that table ,the probleum is that when i run that statement on dummy user on different database which have same structure as original one it run sucessfully.

SQL> conn nds/nds@ora
Connected.
SQL> INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#, VCH_VCH_TyPe,VCH_VCH_DATE, DB_AMT,
2 CR_AMT, VCH_ENTR_BY, VCH_ENTR_DATE)
3 VALUES( '400001','0700012', '0110', SYSDATE, 1, 1,
4 'A', SYSDATE) ;

1 row created.

but it give error on original user

SQL> conn newton/newton@oracle
Connected.
SQL> INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#, VCH_VCH_TyPe,VCH_VCH_DATE, DB_AMT,
2 CR_AMT, VCH_ENTR_BY, VCH_ENTR_DATE)
3 VALUES( '400001','0700012', '0110', SYSDATE,1,1,
4 'A', SYSDATE) ;
INSERT INTO FAC_VCH_M(VCH_LOCN_CODE,VCH_VCH_#, VCH_VCH_TyPe,VCH_VCH_DATE, DB_AMT,
*
ERROR at line 1:
ORA-01403: no data found

i m also surprised why this is happness


regards
jitender
Re: error during inserting(ORA-01403: no data found) [message #282175 is a reply to message #282168] Wed, 21 November 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you format your post as already requested: OraFAQ Forum Guide.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Search the difference between the 2 databases.

Regards
Michel

Re: error during inserting(ORA-01403: no data found) [message #282176 is a reply to message #282168] Wed, 21 November 2007 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
INSERT statement, as you've written it, can not raise NO-DATA-FOUND. There *must* be something behind - a trigger, as Ross said, which SELECTS something and returns nothing (which is not properly handled).

Are you sure there's no trigger on that table?
Re: error during inserting(ORA-01403: no data found) [message #282180 is a reply to message #282176] Wed, 21 November 2007 01:31 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
THX FOR REPLY

YEAH I GOT THE SOLUTION , THERE IS TRIGGER ON THAT TABLE "AFTER INSERT" WHICH HAVE A SELECT STATEMENT WHICH CAUSE THE ERROR

REGARDS
JITENDER.
Re: error during inserting(ORA-01403: no data found) [message #282184 is a reply to message #282180] Wed, 21 November 2007 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DON'T POST IN UPPER CASE.
IT IS REGARDED AS SHOUTING.

Regards
Michel
Re: error during inserting(ORA-01403: no data found) [message #282213 is a reply to message #282144] Wed, 21 November 2007 03:32 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hi Jitendar,

I was fully doubt on this. Otherwise you will not get 'No Data Found' error while inserting data into a table.

And for your kind information, we are not suppose to use IM-words, THX!

Cheers,
Kiran.
Re: error during inserting(ORA-01403: no data found) [message #282240 is a reply to message #282213] Wed, 21 November 2007 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I was fully doubt on this. Otherwise you will not get 'No Data Found' error while inserting data into a table.

SQL> create table t1 (val varchar2(10));

Table created.

SQL> create table t2 (val varchar2(10));

Table created.

SQL> create trigger trg after insert on t1 for each row
  2  declare
  3    v varchar2(10);
  4  begin
  5    select val into v from t2;
  6  end;
  7  /

Trigger created.

SQL> insert into t1 values ('x');
insert into t1 values ('x')
            *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "MICHEL.TRG", line 4
ORA-04088: error during execution of trigger 'MICHEL.TRG'

Regards
Michel
Re: error during inserting(ORA-01403: no data found) [message #282241 is a reply to message #282144] Wed, 21 November 2007 04:15 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

I understood Michel. In OP's previuose reply he said there was not any trigger. I said i had fully doubt that there was i triger in that table, otherwise you will not get 'No_Data_found' error.
This will occure when we trying get null column into a variable.
Re: error during inserting(ORA-01403: no data found) [message #282246 is a reply to message #282144] Wed, 21 November 2007 04:23 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

No Kiran ,

It is happen when Select tries to access column not having data . No data doesn't mean thata value is NULL .

SQL> create table t1 (val varchar2(10));

Table created.

SQL> create table t2 (val varchar2(10));

Table created.

SQL> create trigger trg after insert on t1 for each row
  2    declare
  3      v varchar2(10);
  4    begin
  5      select val into v from t2;
  6  end;
  7  /

Trigger created.

SQL> insert into t1 values ('x');
insert into t1 values ('x')
            *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "MTN4C_TRG.TRG", line 4
ORA-04088: error during execution of trigger 'MTN4C_TRG.TRG'


SQL> insert into t2 values(NULL) ;

1 row created.

SQL> insert into t1 values ('x');

1 row created.

SQL>


Thumbs Up
Rajuvan

[Updated on: Wed, 21 November 2007 04:25]

Report message to a moderator

Previous Topic: Simple Table Manipluation
Next Topic: How can I store sql query result in different tabs of excel
Goto Forum:
  


Current Time: Thu Dec 08 21:53:03 CST 2016

Total time taken to generate the page: 0.13588 seconds