Select/Insert Statements causing out of memory error run under VB Script

15 pts.
INSERT statement
Microsoft Windows Server 2003
Oracle 10g
Oracle 10g Database
SELECT statement
Windows Server 2003 32-bit
I'm using VB Script on a Windows 2003 Server against an Oracle 10g database to both query against a table and insert new rows of data against that same table but as it runs it starts eating up RAM and when it gets to about 1.5gb it causes the out of memory error.  As far as the Select Statement query goes I have to read from about 30 of the 120 column names and I only specify the 30 columns instead of the generic Select * From table_name.  The Insert statement I have to update about 10 of those 120 columns.

Are there any arguments or parameters that I can state to control the amount of data loaded into memory so that it doesn't create the error?

Software/Hardware used:
Windows 2003 Server 32bit and Oracle 10g database

Answer Wiki

Thanks. We'll let you know when a new response is added.

How many rows are returned by the SELECT statement? You’ll need enough memory to hold all this data.

Are you properly closing and disposing of all the objects after you have finished the database update?

Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Can we take a look at your code ?
    85,430 pointsBadges:
  • ItGuy400
    The best thing to do is at the database layer have a SP process and return only what you're looking for and hand the results off to the script. In MSSQL server i had a similar issue with record paging and pretty much had to make the SQL Server work horse return only what was needed for the record set instead of return 1000 records just to display 20.
    100 pointsBadges:
  • Paladine Solamnia
    Could you give us the Oracle exception error pertaining to the out of memory ? Additionally, please run this at the database server and show the results. C:>sqlplus "/ as sysdba" SQL*Plus: Release - Production on Thu Jan 14 23:07:39 2010 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release - Production With the Partitioning, OLAP and Data Mining options SQL> show parameter sga SQL> show parameter cursors SQL> select pool, sum(bytes) from v$sgastat group by pool; SQL> select name, current_size from v$buffer_pool;
    170 pointsBadges:
  • TheBandit
    First, thanks for responding. It ranges from 100,000 rows to 150,000 rows but the table itself may have 10 times that amount. One of the Primary Keys is a date column, which the program that populates it uses the last day of the month for periodic values. So my Select statement may look like Select PeriodKey, LocationKey, Account, Entity, Custom1, Amount From Table_Name Where PeriodKey = 12/31/2007 And LocationKey = 750 I'm actually pulling about 15 more columns than my example but thought I'd give the cliffnotes version lol. Is it possible to control how many rows I place in memory and then process the next defined number of rows and so on?
    15 pointsBadges:
  • jkeri
    Have you considered using FETCH cursor BULK COLLECT INTO ... LIMIT ?
    360 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: