Hi,

I want to insert the result of a select into a procedure-variable
within a while-loop. The loop works as far as I don´t insert following
line:

select tea.FDATE into ldTeaDate2 from ... tea where tea.ID = ldTeaId;

the error message:
ORA-01403: no data found
ORA-06512: at "...", line 70

The table and variables are created by the same user, who execute the
statement.

Have anyone an idea?

Hello, bert,

near 07:15 16-Jul from bertholomaeus (AT) yahoo (DOT) ca:

include theis statement in block with exception handler section.

--
wbr,
Wit.

bertholomaeus (AT) yahoo (DOT) ca (bert) wrote in message news:<ea3b40c.0407160615.253a680c (AT) posting (DOT) google.com>...
Well, as the message says, there is no data to match your query.

So I don't know your exact problem. If there is expected to be no data
in every loop you have to write a no_data_found exception. And handle
the exception there.

If you except thre should be data there it is hard for me to gess wat
your problem is. You could try running just the query in sql*plus to
verify that it is correct.

Just a few gess what could cause this problem

Matching against NULL

EG. if a = NULL
and b = NULL
then the boolean (a = b) = NULL which is not FALSE or TRUE
To solve this you could use nvl() statments, outer joins or OR in your
WHERE clause.

Not commited changes. If the insert statments to the table are done in
another session than the pl/sql prgram is run, the later session wil
not see the chsnges of the first session until they are commited.

Regards
Odd M

bertholomaeus (AT) yahoo (DOT) ca (bert) wrote in message news:<ea3b40c.0407160615.253a680c (AT) posting (DOT) google.com>...
There are no records in the tea table that meet your where clause.

You ought to catch any exceptions that this code could throw

The two that spring to mind are no data returned (which you have hit)
and exact fetch returns more than requested number of rows (when you
return more than one record).

Niall Litchfield
Oracle DBA
Http://www.niall.litchfield.dial.pipex.com

bertholomaeus (AT) yahoo (DOT) ca (bert) wrote in message news:<ea3b40c.0407160615.253a680c (AT) posting (DOT) google.com>...
How about you read the error message: the SELECT found NO DATA.

You'll need to add some EXCEPTION processing to your code. Look it up
and try it.

And I'd suggest you post any follow up questions in
comp.databases.oracle.misc since the comp.databases.oracle group is
supposed to be obsolete.