PL/SQL Table of records 7 ORA-1403

From: Rich Holoch <holoch_at_ccnet.com>
Date: 1998/05/15
Message-ID: <355CEEA2.56944AFA@ccnet.com>#1/1

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 Fri May 15 1998 - 00:00:00 CDT