Oracle Input Buffer Limitation

[David Cogley_1]

A Perl script which uses DBI to copy information from production servers to a data warehouse functions without problem on Sybase but freezes on Oracle when the UPDATE statement gets to be too big.

For a particular day and server, I successfully copy over 200 records and the script freezes at the attached update statement. Upon inspection, it seems that this statement is just a little longer than the successful statements. When I paste this statement into SQL*Plus and add a semicolon, Oracle complains that a quoted string is not ended properly. If I just insert a carriage return in the middle of the statement, it executes successfully.

Is there a way to change the input buffer so it will successfully process a longer statement?

The data warehouse is implemented with Oracle 8.1.7 on WIN2K. The WIN2K box has only 128 MB RAM. This might be a contributing factor.

[Andreas D. Skjervold]

Hi

Have seen some references to multicolumn insterts spinning with more than 256 columns.

As I could see yours has 140, so you should be in the safeside here.

128 MB RAM is low but still your able to run the other updates... Beats me!


Andreas

[Bill Hassell]

Although this is an HP-UX (Unix) forum, experience with large scale databases such as Oracle indicate that memory should be measured in gigabytes for reasonable performance. Also, WinXX platforms have a long history of poor memory management, that is, when most of memory is used, swapping is not always effective and the lock-up scenario you describe is common to all programs when RAM is almost gone. I would increase RAM to a minimum of 512 megs but 1000 megs would be better.