Home » Developer & Programmer » Precompilers, OCI & OCCI » Can update generate a no_data_found [MERGED]
Can update generate a no_data_found [MERGED] [message #286048] Thu, 06 December 2007 06:31 Go to next message
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 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
No Message body either. Confused

MHE
Re: Can update statement give a no data found error? [message #286056 is a reply to message #286048] Thu, 06 December 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No (alone).

Regards
Michel
Re: Can update statement give a no data found error? [message #286057 is a reply to message #286048] Thu, 06 December 2007 06:44 Go to previous messageGo to next message
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.
can update give a data not found error [message #286060 is a reply to message #286048] Thu, 06 December 2007 06:57 Go to previous messageGo to next message
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.
Re: can update give a data not found error [message #286062 is a reply to message #286060] Thu, 06 December 2007 07:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Check whether the table is having any Trigger associated with

Thumbs Up
Rajuvan.
Re: can update give a data not found error [message #286066 is a reply to message #286060] Thu, 06 December 2007 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: can update give a data not found error [message #286067 is a reply to message #286062] Thu, 06 December 2007 07:07 Go to previous messageGo to next message
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.
Re: can update give a data not found error [message #286071 is a reply to message #286066] Thu, 06 December 2007 07:09 Go to previous messageGo to next message
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);
}
Re: can update give a data not found error [message #286083 is a reply to message #286071] Thu, 06 December 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Can update statement give a no data found error? [message #286093 is a reply to message #286087] Thu, 06 December 2007 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The code is already two posts above yours.

Regards
Michel
Re: Can update statement give a no data found error? [message #286120 is a reply to message #286093] Thu, 06 December 2007 08:59 Go to previous messageGo to next message
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.
Re: Can update statement give a no data found error? [message #286121 is a reply to message #286120] Thu, 06 December 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Can update statement give a no data found error? [message #286125 is a reply to message #286121] Thu, 06 December 2007 09:24 Go to previous messageGo to next message
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.

Re: Can update statement give a no data found error? [message #286135 is a reply to message #286125] Thu, 06 December 2007 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, will try to find a system tomorrow.

Regards
Michel
Re: Can update statement give a no data found error? [message #286206 is a reply to message #286120] Thu, 06 December 2007 21:08 Go to previous messageGo to next message
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.
Re: Can update statement give a no data found error? [message #286220 is a reply to message #286206] Thu, 06 December 2007 23:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a standard case.
Updating no row is normal.
Selecting no row worths a warning.

Regards
Michel
Re: Can update generate a no_data_found [MERGED] [message #286260 is a reply to message #286048] Fri, 07 December 2007 01:06 Go to previous messageGo to next message
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 1820 times)
Re: Can update generate a no_data_found [MERGED] [message #286285 is a reply to message #286260] Fri, 07 December 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Can update generate a no_data_found [MERGED] [message #286303 is a reply to message #286260] Fri, 07 December 2007 02:29 Go to previous messageGo to next message
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?
Re: Can update generate a no_data_found [MERGED] [message #286305 is a reply to message #286303] Fri, 07 December 2007 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Can update generate a no_data_found [MERGED] [message #286308 is a reply to message #286260] Fri, 07 December 2007 02:32 Go to previous messageGo to next message
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.
Re: Can update generate a no_data_found [MERGED] [message #286311 is a reply to message #286308] Fri, 07 December 2007 02:46 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
manisha_g wrote on Fri, 07 December 2007 09:32

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.


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 Go to previous messageGo to next message
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?
Re: Can update generate a no_data_found [MERGED] [message #286326 is a reply to message #286314] Fri, 07 December 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, this is not an error but a warning.
Then, warnings do obviously not raise an error.

Regards
Michel
Re: Can update generate a no_data_found [MERGED] [message #286339 is a reply to message #286326] Fri, 07 December 2007 03:24 Go to previous messageGo to next message
manisha_g
Messages: 12
Registered: December 2007
Junior Member
Fine,
but why is the same exception not raised from a plsql block?
Re: Can update generate a no_data_found [MERGED] [message #286351 is a reply to message #286339] Fri, 07 December 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Can update generate a no_data_found [MERGED] [message #286356 is a reply to message #286351] Fri, 07 December 2007 03:45 Go to previous messageGo to next message
manisha_g
Messages: 12
Registered: December 2007
Junior Member
But why is this warning not generated in case of PLSQL blocks for update statement
Re: Can update generate a no_data_found [MERGED] [message #286358 is a reply to message #286356] Fri, 07 December 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because PL/SQL only raises the error and not the warnings (didn't I already say that?).

Regards
Michel
Re: Can update generate a no_data_found [MERGED] [message #286363 is a reply to message #286356] Fri, 07 December 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Can update generate a no_data_found [MERGED] [message #286382 is a reply to message #286363] Fri, 07 December 2007 04:16 Go to previous messageGo to next message
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
Re: Can update generate a no_data_found [MERGED] [message #286383 is a reply to message #286382] Fri, 07 December 2007 04:20 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: OCCI - problem to read user defined function return values
Next Topic: Pro*C : Sample1.pc do not works fine
Goto Forum:
  


Current Time: Thu Mar 28 17:13:40 CDT 2024