| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Pl/sql issue
Here's my 2 cents. The type rowid_table you are creating should be a table
of ROWID, not a table of CHAR. CHAR would only store 1 character and that
is why the bind is truncated. Even if you specify CHAR(32) or more, your
code may then be tied to a specific version of Oracle if the format or size
ever changes. Better practice is to use the pseudo-type ROWID.
Jason.
-----Original Message-----
From: Fuad Arshad [mailto:fuadar_at_yahoo.com]
Sent: Thursday, June 24, 2004 8:46 AM
To: oracle-l_at_freelists.org
Subject: Pl/sql issue
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.
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |