Re: dbms_sql ora 1483

Not sure about this but one possibility is that you are confusing the cursor by deleting while it's running. You obviously can do that but I've had odd problems when doing it too.

See if this makes any difference: create a pl/sql "table" for rowids and store the rowid of each ro to be deleted. (You'll obviously need a count as well.) Don't delete within the cursor but, once you've collected all your rowids, close the cursors and then delete by rowid using the list from the table.

The trouble is that this is more long-winded and, therefore, slower. If there could be activity on the database, there could also an issue of locking problems which you'd have to work around.

Len

>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
  Received on Fri Apr 13 2001 - 15:33:32 CDT