dbms_sql ora 1483

I am using dbms_sql and utl_file to pull redundant data from a database, spool it to a file and delete rows from associated tables. (dbms_sql seems to give me faster results than updates).

The weird thing is that I have two loops, an outer one to define the upper and parameters for my select and an inner one defined my array fetch size.

If I set my outer parameter as say 200 and my inner one as 20 It runs through fine

I then shutdown and restored a copy or the database and repeated the test I set my outer parameter at 10000 and the inner one at 100 it fails on occasional batches on dbms_sql.execute with the error

ORA-01483: invalid length for DATE or NUMBER bind variable

however it does not fail on batches

The problems I have are

1. How do I diagnose the problem

The Oracle application developers guide fundamentals ch 9

says "The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead resulting from data copying. "

error 1483 is a array space error but I cannot find how/where arrays are defined
2. Want to install a robust version
3. find a safe maximum value for array processing/allocate memory

any help gratefully received

Andy Bain Smith

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net