Toad World® Forums

Export Data Set into Insert Statements


#1
  • I am generating the Dataset of around 10000 records using F5 and Exporting Dataset into Insert Statements [Include Null Text option checked] it is working fine but as soon as it is crossing 16000+ records its giving Out of Memory Exception. Then I have tried using Execution using F9 and Export Dataset[Include Null Text option checked] into Insert Statements its bring back results and I can able to save Data into .sql file but I can see there is a difference that its having NULL and TO_DATE conversion in values for date fields, which was not there when I was running it through F5.
    Thanks

#2

F5 grid output is a string representation of the actual output and does not accurately represent the datatypes. F9 output will give you proper representation of the datatypes you are using. Is there a reason you are exporting to insert statements? SQL Loader would be a much better option if you are trying to copy to another database.


#3

Yes there is another Tool we are using which is expecting only Insert Statements in .sql file as input.Is there any way we can solve Out Of memory exception which i am getting using F5 command? OR can we do something using F9 Execution which do not incorporate toDate function and NULL into .sql file which we are exporting ?


#4

Below is a script I use to dump a file - it uses SQL to generate SQL which toad supports and I’ve blogged about about a half dozen time (read on toad world under blogs):

set linesize 1000

set pagesize 0

set verify off

set feedback off

set trimspool on

set termout off

set arraysize 5000

col dummy1 noprint

col dummy2 noprint

spool dump.tmp

select 1 dummy1, 0 dummy2, ‘select ‘‘INSERT INTO &1 VALUES(’’||’’’’’

from user_tables where table_name = upper(’&1’)

union all

select 2, column_id, decode(column_id,1,’ ‘,’’’,’’’

)||’ || '||

decode(data_type,‘NUMBER’,‘NULL’,’’’’’’’’’’

)||’ || '||

decode(data_type,‘NUMBER’,‘decode(’||column_name||’,NULL,’‘NULL’’,’||column_name||’)’,

‘replace(’||column_name||’,’’’’’’’’,’’’’’’’’’’’’’||’)’

)||’ || '||

decode(data_type,‘NUMBER’,‘NULL’,’’’’’’’’’’

)||’ || ’

from user_tab_columns where table_name = upper(’&1’)

union all

select 3, 0, ‘’’’’||’’);’’ from &1;’

from user_tables where table_name = upper(’&1’)

order by 1, 2;

spool off

spool &1…sql

select ‘set define off’ from dual;

select ‘set feedback off’ from dual;

select ‘set autocommit on’ from dual;

@dump.tmp

select ‘commit;’ from dual;

select ‘set define ‘’&’’’ from dual;

select ‘set feedback on’ from dual;

select ‘set autocommit off’ from dual;

spool off

host del dump.tmp

set term on


#5

Ok , thats fine.

But I have a select statement to bring some specific data and the I need to Export them into Insert Statements using TOAD.But as I have mentioned there is a Out Of memory Exception while using F5 for Thousands of records and F9 is having diff issue as its not the String representation


#6

This script will work and not suffer out of memory error. It writes a SQL file via spool command that generates insert statements for all rows in the table. I’ve used this with million row tables without issue.


#7

One other question…is there a problem using the TO_DATE? That is actually the proper way to do the insert into a date field. If you’re looking for the string
representation, you could cast the date fields using the TO_CHAR function in your SQL that generates the insert statements. You could also use a DECODE function to force NULL values the way you desire.

From: Mayank Khare [mailto:bounce-mayanktoad@toadworld.com]

Sent: Wednesday, June 04, 2014 9:49 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Export Data Set into Insert Statements

RE: Export Data Set into Insert Statements

Reply by Mayank Khare

Ok , thats fine.

But I have a select statement to bring some specific data and the I need to Export them into Insert Statements using TOAD.But as I have mentioned there is a Out Of memory Exception while using
F5 for Thousands of records and F9 is having diff issue as its not the String representation

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.


#8

When you run with F9, what exactly are you seeing and desiring to see with regards to NULL and date values? Is there a bug here or just a matter of preferences?