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

asdasd

Rich Holoch <holoch_at_ccnet.com> wrote in article <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);
> 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