Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Spooling to a flat file via SQL*Plus
Tom,
Virtual beverage of choice to you. The insert worked like a charm. I was having difficulty with the substring command but this worked fine. Didn't need to do the SQL*Load.
Many thanks. It's a zoo here today.
Cherie Machler
Oracle DBA
Gelco Information Network
"Mercadante, Thomas F" To: "'ORACLE-L_at_fatcity.com'"
<ORACLE-L_at_fatcity.com>
<NDATFM_at_labor.st cc: "'Cherie_Machler_at_gelco.com'"
<Cherie_Machler_at_gelco.com>
ate.ny.us> Subject: RE: Spooling to a flat file via SQL*Plus 12/12/01 09:35 AM
CHerie,
Why not just move the data directly using sqlplus rather than go the sqlloader route:
insert new_cd_experience(KS_EXPERIENCEID,EXP_NOTES,
EXP_QA_STATE,TEMP_RN,EXP_REVIEW_NEEDED) select KS_EXPERIENCEID,substr(EXP_NOTES,1,255), EXP_QA_STATE,TEMP_RN,EXP_REVIEW_NEEDEDfrom old_cd_experience;
otherwise to spool a file you would:
set head off
set lines 200
set pages 10000 <=== make larger than the number of rows to export
spool cd_exp.dat
select KS_EXPERIENCEID || ',' ||substr(EXP_NOTES,1,255) || ',' ||
EXP_QA_STATE || ',' ||TEMP_RN || ',' ||EXP_REVIEW_NEEDED
from old_cd_experience;
spool off;
hope this helps
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, December 12, 2001 10:00 AM
To: Multiple recipients of list ORACLE-L
I need to chang a column in an existing table from varchar2(2000) instead
to varchar2(255).
I'm to truncate any data that exists that is longer than 255.
I renamed the original table to old_table. I created a new table with the
same definitions as
old_table except made the single column that was varchar2(2000) is now
varchar2(255).
I want to spool the data to a flat file in comma-delimited form. I only
want to spool out 255
characters of the column in question. Then I'll just load it back in
using SQL*Loader.
I haven't done this for a long time and I can't remember all the sql*plus
commands to
set up the output properly so that the lines can wrap, etc. Also, I'm not
sure how to tell it
to only list 255 characters of the 2000 character column in the output
file.
I know I can spend some time looking this up in my docs but it's a terrible
day today with
a lot of fires burning in various directions.
I'd appreciate it if some kind sole could help me out with the commands to
spool this
out properly.
desc orig_cd_experience;
Name Null? Type ----------------------------------------- -------- ---------------------------- KS_EXPERIENCEID NOT NULL NUMBER(38) EXP_NOTES VARCHAR2(2000) EXP_QA_STATE VARCHAR2(100) TEMP_RN CHAR(1) EXP_REVIEW_NEEDED NOT NULL CHAR(1)
In everlasting gratitude,
Cherie
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Dec 12 2001 - 10:22:19 CST