Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01403: no data found - Returned from PL/SQL but data exists

Re: ORA-01403: no data found - Returned from PL/SQL but data exists

From: <fitzjarrell_at_cox.net>
Date: 20 Jun 2006 18:05:54 -0700
Message-ID: <1150851954.616055.212780@y41g2000cwy.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US