- 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
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.
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 ?
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
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
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.
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
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.
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?