Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01403: no data found - Returned from PL/SQL but data exists
Comments embedded.
oichi wrote:
> Hi
>
> I am new to PL/SQL.
>
> Here's the problem
> ..
> ..
> CURSOR Cur_Network IS
> select sznetwork, szdescription from EM_X_NETWORK WHERE
> ulmigrationid = MigrationID;
What, exactly, is MigrationID? It doesn't appear to be defined anywhere.
>
> NetworkCode EM_X_NETWORK.sznetwork%type;
> NetworkDesc EM_X_NETWORK.szdescription%type;
>
> ULID NUMBER;
> Network CHAR(4);
> BEGIN
> ReturnCode := 0;
>
> OPEN Cur_Network;
> FETCH Cur_Network into NetworkCode, NetworkDesc ;
>
> IF (Cur_Network%NOTFOUND) THEN
> RAISE No_Data_Found;
> END IF;
>
> WHILE Cur_Network%FOUND
> LOOP
> BEGIN
>
> Network := TRIM(NetworkCode);
>
> -- The problem sql that returns the no data found error
>
> SELECT SZNETWORK INTO Network FROM Eclipse.X_NETWORK WHERE
> TRIM(SZNETWORK)= TRIM(Network);
>
Why are you selecting a value into an already populated variable which you're also using in the WHERE clause? Also, you've already trimmed the value when it was assigned; it's useless to trim it again.
> -- The NetworkCode could be Names Like 'CNN' , 'ABC' etc...
> -- SZNETWORK is CHAR(4) and NetworkCode is also CHAR(4)
> -- I know the Eclipse.X_Network Table has equivalent matches
>
You think there are matches. You haven't proven there are.
>
> FETCH Cur_Network into NetworkCode, NetworkDesc;
>
> END LOOP;
>
>
> Any Help would be appreciated
>
I'd be outputting the value of Network after you assign it to see what REALLY is stored there. Also I'd be running:
SELECT SZNETWORK FROM Eclipse.X_NETWORK WHERE sznetwork in (select sznetwork from EM_X_NETWORK );
and see if any data is returned. My guess is there won't be.
> Thanx
> Oichi
David Fitzjarrell Received on Tue Jun 20 2006 - 20:05:54 CDT