Re: SQL-Proceudre: SELECT ... INTO... within a while loop - doesn´t work

bertholomaeus_at_yahoo.ca (bert) wrote in message news:<ea3b40c.0407160615.253a680c_at_posting.google.com>...
> 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?

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 Received on Sun Jul 18 2004 - 22:33:30 CEST