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

You might want to check where you reference your pl/sql table. You can get the same error message if you try to reference a row in the pl/sql table that has not been initialized. Maybe right after your fetch in the loop. I made some notes in the code below for you to take a look at.

Hope this helps.
Patrick Flahan
flahan_at_leading.net

Rich Holoch wrote in message <355CEEA2.56944AFA_at_ccnet.com>...
>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);

• you increment here even if you don't fetch another row. This
  > v_lctr := v_lctr +1;
  

  ---

  ---

  
  > DBMS_OUTPUT.PUT_LINE(v_lctr);
  

  ---

  ---

  • this might be where the error is happening, if you are trying to reference
• an uninitialized row in the pl/sql table
  > DBMS_OUTPUT.PUT_LINE(v_mv90_intervaldata(v_lctr).id);
  

  ---