OraFAQ Forum: Precompilers, OCI & OCCI » Can update generate a no_data

Home » Developer & Programmer » Precompilers, OCI & OCCI » Can update generate a no_data_found [MERGED]

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can update generate a no_data_found [MERGED] [message #286048]

Thu, 06 December 2007 06:31

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Can update statement give a no data found error?

[Updated on: Thu, 06 December 2007 06:41]

Report message to a moderator

Re: Can update statement give a no data found error? [message #286054 is a reply to message #286048]

Thu, 06 December 2007 06:39

Maaher
Messages: 7065
Registered: December 2001

Senior Member

No Message body either. Confused

MHE

Report message to a moderator

Re: Can update statement give a no data found error? [message #286056 is a reply to message #286048]

Thu, 06 December 2007 06:42

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No (alone).

Regards
Michel

Report message to a moderator

Re: Can update statement give a no data found error? [message #286057 is a reply to message #286048]

Thu, 06 December 2007 06:44

manisha_g
Messages: 12
Registered: December 2007

Junior Member

am using an update statement in a pro c code.
It gives me a data not found error.
Needed to know on this as according to me update should not give a data not found error.
same update when used in a PLSQL block does not give any error.

Report message to a moderator

can update give a data not found error [message #286060 is a reply to message #286048]

Thu, 06 December 2007 06:57

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Report message to a moderator

Re: can update give a data not found error [message #286062 is a reply to message #286060]

Thu, 06 December 2007 07:01

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Check whether the table is having any Trigger associated with

Thumbs Up

Rajuvan.

Report message to a moderator

Re: can update give a data not found error [message #286066 is a reply to message #286060]

Thu, 06 December 2007 07:07

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Pro*C does not give any error.
You search for the error in sqlca (for instance) but only if you search for the right sqlca and in the right place.

Post your code if you want some help.

Regards
Michel

Report message to a moderator

Re: can update give a data not found error [message #286067 is a reply to message #286062]

Thu, 06 December 2007 07:07

manisha_g
Messages: 12
Registered: December 2007

Junior Member

It does not have any trigger associated.
Also if it had a trigger the same error would have come even from teh plsql block.

Report message to a moderator

Re: can update give a data not found error [message #286071 is a reply to message #286066]

Thu, 06 December 2007 07:09

manisha_g
Messages: 12
Registered: December 2007

Junior Member

code is something like this:
exec sql
update emp set ename='abc'
where ename='qwe';

if(sqlca.sqlcode != SUCCESSFUL)
{
sprintf(msg,"Error in updating , process exiting\n");
dump_error(msg);
exit(1);
}

Report message to a moderator

Re: can update give a data not found error [message #286083 is a reply to message #286071]

Thu, 06 December 2007 07:24

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't see how you can get a "no data found" with your code.
It is just a OK/NOK test.

Regards
Michel

[Updated on: Thu, 06 December 2007 07:25]

Report message to a moderator

Re: Can update statement give a no data found error? [message #286087 is a reply to message #286057]

Thu, 06 December 2007 07:38

rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand

Junior Member

hi

send your sql/procedure

table scripts as well and use codes

e.g

 update mytable
   set col1='the_value-u_want_to_update_to'
   where cols1=var1
   and col2 in(select cd1 from table_name )

and the output you are trying to archieve.
rebeccah

Report message to a moderator

Re: Can update statement give a no data found error? [message #286093 is a reply to message #286087]

Thu, 06 December 2007 07:56

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The code is already two posts above yours.

Regards
Michel

Report message to a moderator

Re: Can update statement give a no data found error? [message #286120 is a reply to message #286093]

Thu, 06 December 2007 08:59

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

code is something like this

So, is the real code a bit different ?

Now, a "no data found" means a value of +100 in sqlca.sqlcode (or in some rare cases +1403 if I'm not mistaken).

And this is possible when no records in your database matches the WHERE-clause of your UPDATE-statement.

Report message to a moderator

Re: Can update statement give a no data found error? [message #286121 is a reply to message #286120]

Thu, 06 December 2007 09:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't think that even in this case a code other than 0 is returned.
There is another sqlca/sqlda field to know how many rows are updated.
I don't have a server to check it but I'm pretty sure of this.

Regards
Michel

Report message to a moderator

Re: Can update statement give a no data found error? [message #286125 is a reply to message #286121]

Thu, 06 December 2007 09:24

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Same here: no system anymore to check this out.

Did found some code where after an UPDATE sqlca.sqlcode was tested against 0 and +100.

Didn't find any code that checked sqlca.sqlerrd[2] to verify the number of rows updated being 0.

Report message to a moderator

Re: Can update statement give a no data found error? [message #286135 is a reply to message #286125]

Thu, 06 December 2007 10:12

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

OK, will try to find a system tomorrow.

Regards
Michel

Report message to a moderator

Re: Can update statement give a no data found error? [message #286206 is a reply to message #286120]

Thu, 06 December 2007 21:08

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Yes i agree that there are no records matching taht condition in the table,but as per my knowledge update does not throw a no data found error.hence i have the doubt.
The same update given in a plsql block does not throw this error even though it does not find any records to match its condition.

Report message to a moderator

Re: Can update statement give a no data found error? [message #286220 is a reply to message #286206]

Thu, 06 December 2007 23:13

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is a standard case.
Updating no row is normal.
Selecting no row worths a warning.

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286260 is a reply to message #286048]

Fri, 07 December 2007 01:06

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Couldn't resist Razz

With a little help I managed to set up a small test-case in PRO*C on one of our Linux-servers.

I've attached the source-code.
The program does the following:

DROP TABLE
CREATE TABLE
INSERT ONE ROW
UPDATE EXISTING ROW
UPDATE NON-EXISTING ROW

Output looks like this:

Connected to 'scott/tiger@mydb'
TABLE DROPPED
TABLE CREATED
ROW INSERTED
UPDATED 1 rows SET VAL 1 WHERE ID 1
SQL ERROR:  UPDATE
	(100) ORA-00100: no data found

As you notice the SQLCODE has a positive value of 100.
It's up to the application to verify if the "NO DATA FOUND" situation is a real error or not.

Attachment: TestProc.pc
(Size: 3.16KB, Downloaded 2435 times)

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286285 is a reply to message #286260]

Fri, 07 December 2007 01:54

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You're right, I reread the documentation and it is said that a code +100 (MODE=ANSI) or +1403 (MODE=ORACLE) is returned if a WHERE clause, a SELECT or a FETCH returns nothing.

Thanks to put this back in my mind.
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286303 is a reply to message #286260]

Fri, 07 December 2007 02:29

manisha_g
Messages: 12
Registered: December 2007

Junior Member

True.so that means that we can get a no data found error for update statement when no records exist when used in Pro C.
But the same thing if done through PLSQL does not give this error?

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286305 is a reply to message #286303]

Fri, 07 December 2007 02:31

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286308 is a reply to message #286260]

Fri, 07 December 2007 02:32

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Also please can you explain this a little more:
"It's up to the application to verify if the "NO DATA FOUND" situation is a real error or not."

If there is some document that i can read,please share the same with me.

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286311 is a reply to message #286308]

Fri, 07 December 2007 02:46

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

manisha_g wrote on Fri, 07 December 2007 09:32

What I meant to say is that it is application-specific behavior whether a NO_DATA_FOUND situation should be considered as an appliation error or just a warning.

A little sample: a client-table needs to be updated for some reason. Like: every client that lives in the United States:

UPDATE CLIENT
SET some_column = some_value
WHERE CLIENT_COUNTRY = 'US' ;

It could be considered perfectly normal that no records are updated as you don't have any clients in the US.

But what if you have a table PROCESS_FLOW where you keep track of the state of a PROCESS?
You expect a specific row (or number of rows) to be in there, and they aren't?

UPDATE PROCESS_FLOW
SET FLOW_STATUS = 'IN PROGRESS'
WHERE PROCESS_NAME = 'INVOICING' ;

I assume that the INVOICING record should be there at all times. If this update failes, then something could be very wrong with my data.

Hope this clarifies my point a bit

[Updated on: Fri, 07 December 2007 02:47]

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286314 is a reply to message #286311]

Fri, 07 December 2007 02:48

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Yes i understand ..thanks so much...
but why is the same error not thrown from a plsql block?

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286326 is a reply to message #286314]

Fri, 07 December 2007 03:13

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First, this is not an error but a warning.
Then, warnings do obviously not raise an error.

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286339 is a reply to message #286326]

Fri, 07 December 2007 03:24

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Fine,
but why is the same exception not raised from a plsql block?

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286351 is a reply to message #286339]

Fri, 07 December 2007 03:35

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Exception raises only on error not on warning.

(Reply to the next question: I can't see any other way to say it.)

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286356 is a reply to message #286351]

Fri, 07 December 2007 03:45

manisha_g
Messages: 12
Registered: December 2007

Junior Member

But why is this warning not generated in case of PLSQL blocks for update statement

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286358 is a reply to message #286356]

Fri, 07 December 2007 03:48

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because PL/SQL only raises the error and not the warnings (didn't I already say that?).

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286363 is a reply to message #286356]

Fri, 07 December 2007 03:55

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In the end, it behaves differently in PL/SQL and Pro*C, you have to accept and do with this.
Full stop.

Regards
Michel

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286382 is a reply to message #286363]

Fri, 07 December 2007 04:16

manisha_g
Messages: 12
Registered: December 2007

Junior Member

Can i get a list of warnings in PLSQL?
As in PLSQL the same is raised for select statements

Report message to a moderator

Re: Can update generate a no_data_found [MERGED] [message #286383 is a reply to message #286382]

Fri, 07 December 2007 04:20

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

From PL/SQL doc:

Quote:

NO_DATA_FOUND:
A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.

PL/SQL User's Guide and Reference
Chapter 10 Handling PL/SQL Errors
Section Summary of Predefined PL/SQL Exceptions

Regards
Michel

Report message to a moderator

Previous Topic:	OCCI - problem to read user defined function return values
Next Topic:	Pro*C : Sample1.pc do not works fine

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 28 07:25:24 CST 2025