| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_sql ora 1483
Thanks Leonard, it gives me a lot to think about. and a way of working round the problem.
regards
Andy
In article <3ad75f9b.4017967_at_125.0.0.1>, Leonard F Clark <lfc_at_zoom.co.uk>
writes:
>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
 |
 |