Re: PL/SQL Table of records 7 ORA-1403

From: Rich Holoch <holoch_at_ccnet.com>
Date: 1998/05/16
Message-ID: <355E5010.5ADCECED@ccnet.com>#1/1

I found that the placement of the counter and the exit from the loop was wrong. I re-wrote the fetch loop like this, it worked:

/* Transform and load from Archive to LB_MV90_RAW */

PROCEDURE loadraw(p_usage_read_stop_time IN varchar2,

            p_rcode                 OUT varchar2,
      p_errmsg                OUT varchar2) IS

    v_lctr integer := 1;
TYPE
    t_mv90_intervaldata IS TABLE OF mv90_intervaldata%ROWTYPE     INDEX BY BINARY_INTEGER;
    v_mv90_intervaldata t_mv90_intervaldata;  v_tctr BINARY_INTEGER := 0;

   /* Explicit declaration of a cursor */     CURSOR c_mv90_intervaldata IS

        SELECT *
     FROM   mv90_intervaldata;

BEGIN
psdmlog(v_log_filedir, v_log_filename, 'Begin  : LB_MV90_RAW          load

with a usage_read_stop_time of '||p_usage_read_stop_time, v_rcode, v_errmsg);

/* Check to see if cursor is already open. If not, open it. */

    IF NOT c_mv90_intervaldata%ISOPEN
    THEN
        OPEN c_mv90_intervaldata;
  v_tctr := v_tctr +1;

        FETCH   c_mv90_intervaldata INTO v_mv90_intervaldata(v_tctr);
     DBMS_OUTPUT.PUT_LINE(v_tctr ||' '||

v_mv90_intervaldata(v_tctr).cm_custid);

    END IF;
    LOOP
     EXIT WHEN c_mv90_intervaldata%NOTFOUND;   v_tctr := v_tctr +1;

     /* Fetch Row */
  FETCH c_mv90_intervaldata INTO v_mv90_intervaldata(v_tctr);

     DBMS_OUTPUT.PUT_LINE(v_tctr ||' '|| v_mv90_intervaldata(v_tctr).cm_custid);

    END LOOP;     /* Close the cursor */
    CLOSE c_mv90_intervaldata;
COMMIT; EXCEPTION
    WHEN NO_DATA_FOUND THEN
    psdmlog(v_log_filedir, v_log_filename, 'Success: LB_MV90_RAW '|| v_tctr ||' rows loaded.', v_rcode, v_errmsg);

    IF v_tctr > 0 THEN
     p_rcode := '1';
    ELSIF v_tctr = 0 THEN
     p_rcode := '0';
    END IF;
    WHEN OTHERS THEN

        p_rcode   := SQLCODE;
        p_errmsg  := SQLERRM;
        v_errmsg  := SUBSTR(SQLERRM, 1, 65);
        psdmlog(v_log_filedir, v_log_filename, 'LOADRAW: ['||v_errmsg||'] at

row '||SQL%ROWCOUNT, v_rcode, v_errmsg); END loadraw;

Rich Holoch wrote:

> The following code compiles, and the SQL statement works in SQL*Plus.
> I've got the Urman and Feuerstein PL/SQL books, but they don't have
> good Table of Record examples using a cursor and fetch loop. I know
> this is just my being a beginner - the problem should be a piece of cake
> for a PL/SQL guru. I'm running 7.3 on Solaris 2.6 and NT 4.0. When I
> run this, I get an ORA-1403 - no data found. I must have the fetch
> goofed up.
>
> /* Transform and load from Archive to LB_MV90_RAW */
>
> PROCEDURE loadraw(p_usage_read_stop_time IN varchar2,
> p_rcode OUT varchar2,
> p_errmsg OUT varchar2) IS
>
> v_lctr integer := 1;
> TYPE
> t_mv90_intervaldata IS TABLE OF mv90_intervaldata%ROWTYPE
> INDEX BY BINARY_INTEGER;
> v_mv90_intervaldata t_mv90_intervaldata;
>
> /* Explicit declaration of a cursor */
> CURSOR c_mv90_intervaldata IS
> SELECT *
> FROM mv90_intervaldata;
>
> BEGIN
> psdmlog(v_log_filedir, v_log_filename, 'Begin : LB_MV90_RAW
> load with a usage_read_stop_time of '||p_usage_read_stop_time, v_rcode,
> v_errmsg);
>
> /* Check to see if cursor is already open. If not, open it. */
> IF NOT c_mv90_intervaldata%ISOPEN
> THEN
> OPEN c_mv90_intervaldata;
> FETCH c_mv90_intervaldata INTO v_mv90_intervaldata(v_lctr);
> v_lctr := v_lctr +1;
> DBMS_OUTPUT.PUT_LINE(v_lctr);
> DBMS_OUTPUT.PUT_LINE(v_mv90_intervaldata(v_lctr).id);
> END IF;
> WHILE c_mv90_intervaldata%FOUND LOOP
> /* Fetch Row */
> FETCH c_mv90_intervaldata INTO v_mv90_intervaldata(v_lctr);
> v_lctr := v_lctr +1;
> DBMS_OUTPUT.PUT_LINE(v_lctr);
> DBMS_OUTPUT.PUT_LINE(v_mv90_intervaldata(v_lctr).id);
>
> END LOOP;
> /* Close the cursor */
> CLOSE c_mv90_intervaldata;
> IF v_lctr > 0 THEN
> p_rcode := '1';
> ELSIF v_lctr = 0 THEN
> p_rcode := '0';
> DBMS_OUTPUT.PUT_LINE('No Data');
> END IF;
>
> /* DBMS_OUTPUT.PUT_LINE(v_mv90_intervaldata.COUNT); */
>
> commit;
> psdmlog(v_log_filedir, v_log_filename, 'Success: LB_MV90_RAW
> '||SQL%ROWCOUNT||' rows loaded.', v_rcode, v_errmsg);
> EXCEPTION
> WHEN OTHERS THEN
> p_rcode := SQLCODE;
> p_errmsg := SQLERRM;
> v_errmsg := SUBSTR(SQLERRM, 1, 65);
> psdmlog(v_log_filedir, v_log_filename, 'LOADRAW:
> ['||v_errmsg||'] at row '||SQL%ROWCOUNT, v_rcode, v_errmsg);
> END loadraw;
Received on Sat May 16 1998 - 00:00:00 CDT