Toad 12.0.0.61 Access Violation when importing data

Toad for Oracle
1

Hi,

I am trying to use the Import Table Data functionality to import data from an Excel sheet into a data warehouse table.

As I’m connecting to a data warehouse over a limited WAN connection, using the ‘Apply changes to the database object’ is taking far too long so I tried to use the ‘Create file with SQL statements’ instead.

There are essentially 30,000 rows in the Excel data set so the SQL statement is quite large.

However when running the SQL statement I getting an access violation message in Toad.exe and it aborts the run.

Surely Toad can handle this many multiple insert statements in a script?

Any help would be greatly appreciated.

George Woods.

2

Yes, Toad can execute scripts with multiple insert statements. I am not sure what is going wrong there, but running a script full of insert statements will
not be as fast as the Insert Table Data wizard.

Go back to Insert Table Data, use the “apply to database object” option, and be sure to choose the “Use Array DML” option under it. That will give you the
fastest possible import speed.

From: gwoods [mailto:bounce-gwoods@toadworld.com]

Sent: Wednesday, July 10, 2013 7:27 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Toad 12.0.0.61 Access Violation when importing data

Toad 12.0.0.61 Access Violation when importing data

Thread created by gwoods

Hi,

I am trying to use the Import Table Data functionality to import data from an Excel sheet into a data warehouse table.

As I’m connecting to a data warehouse over a limited WAN connection, using the ‘Apply changes to the database object’ is taking far too long so I tried to use the ‘Create file with SQL statements’
instead.

There are essentially 30,000 rows in the Excel data set so the SQL statement is quite large.

However when running the SQL statement I getting an access violation message in Toad.exe and it aborts the run.

Surely Toad can handle this many multiple insert statements in a script?

Any help would be greatly appreciated.

George Woods.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

WOW!

53 minutes to 20 seconds!

Thanks so much John that made such a difference!

Can you tell me what that option is doing? It also has an Array size which I left at the default of 500. Should this be a specific size?

4

Most of your execution time in insert statements is spend in trips across the network. That “array” setting causes the bind values of the insert statements
to contain an array of values, so each insert statement will insert 500 rows (or whatever you have “array” set to).

You can set it higher if you want, but I think 500 is a good value. You see a point of diminished returns beyond that.

From: gwoods [mailto:bounce-gwoods@toadworld.com]

Sent: Wednesday, July 10, 2013 8:49 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12.0.0.61 Access Violation when importing data

RE: Toad 12.0.0.61 Access Violation when importing
data

Reply by gwoods

WOW!

53 minutes to 20 seconds!

Thanks so much John that made such a difference!

Can you tell me what that option is doing? It also has an Array size which I left at the default of 500. Should this be a specific size?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Good to know.

Didn’t even know you could do multi row inserts in a single statement. Just been Googling it.

So is this equivalent to doing the following?:

insert all

into table_name () values ()

into table_name () values ()

etc.

6

Similar, but not equivelant. That method uses literals which means oracle will have to parse every insert statement. It also repeats the entire statement
for each insert, which makes the SQL very big. The method used by Toad uses bind variables, so there is only one statement, but with multiple variables. It’s parsed only once.

It’s more like bulk inserts in plsql

http://www.akadia.com/services/ora_bulk_insert.html

From: gwoods [mailto:bounce-gwoods@toadworld.com]

Sent: Wednesday, July 10, 2013 9:45 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Toad 12.0.0.61 Access Violation when importing data

RE: Toad 12.0.0.61 Access Violation when importing
data

Reply by gwoods

Good to know.

Didn’t even know you could do multi row inserts in a single statement. Just been Googling it.

So is this equivalent to doing the following?:

insert all

into table_name () values ()

into table_name () values ()

etc.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

7

Interesting and enlightening.

Thanks for your help and quick responses today John.

Appreciate it.

8

You’re welcome George.