Pl/sql issue

From: Fuad Arshad <fuadar_at_yahoo.com>
Date: Thu, 24 Jun 2004 07:45:40 -0700 (PDT)
Message-ID: <20040624144540.65896.qmail@web80501.mail.yahoo.com>

our developers are trying to write seom code to do some deletes whilst readin from a external tables which contains only field for rowid's the pl/sql is as follows

CREATE OR REPLACE procedure abc as

i PLS_INTEGER;

row_cnt PLS_INTEGER;

k_commit_rows CONSTANT PLS_INTEGER := 10;

TYPE rowid_table IS TABLE OF char INDEX BY BINARY_INTEGER;

CURSOR c1 IS SELECT delete_rowid FROM pars_ext_rowid;

v_rowids ROWID_TABLE;

v_inline varchar2(80);

v_lower_bound BINARY_INTEGER;

v_upper_bound BINARY_INTEGER;

BEGIN execute immediate('alter session set timed_statistics = true');

execute immediate('alter session set sql_trace = true');

OPEN c1;

row_cnt := 0;

LOOP
/* The following statement fetches 10 rows (or less). */

FETCH c1 BULK COLLECT INTO v_rowids LIMIT 5;

EXIT WHEN c1%NOTFOUND;

v_upper_bound := c1%ROWCOUNT;

row_cnt := row_cnt + v_upper_bound;

/* FORALL i IN 1..v_upper_bound

delete from ra_line_item where rowidtochar(rowid) = v_rowids(i);

COMmit; */

END LOOP; close c1;

COMmit;

DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'MM/DD/YY HH:mi:ss') ||' DELETED from table ${TABLE_NAME} these rows

: ' || to_char(row_cnt));

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (to_char(sysdate,'MM/DD/YY HH:mi:ss') ||' Error in deleting table ${TABLE_NAME} . SQLCODE : ' ||

SQLCODE || '. SQLERROR ^M Message : ' || SQLERRM);

RAISE; END; /

the error we are getting is below

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at "TEST_R3_C.ABC", line 41
ORA-06512: at line 2

any help on what the developers are doing wrong. i thought it was an external table issue but on further drill down i think the code has some problem with the bulk collect. i am unable to pinpoint what.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jun 24 2004 - 09:43:23 CDT