RE: Spooling to a flat file via SQL*Plus

From: <Jared.Still_at_radisys.com>
Date: Wed, 12 Dec 2001 10:41:35 -0800
Message-ID: <F001.003DB0EE.20011212102543@fatcity.com>

Better yet, convert comma's into something else before dumping to the file.

look at dump.sql at www.cybcon.com/~jkstill

It does the same thing for double quotes. All double quotes are converted to something else ( I forget what ) and then the data is dumped to a double quote and comma delimited file.

Jared

                                                                                       
                             
                    Ken Janusz                                                         
                             
                    <ken.janusz_at_su       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>        
                    fsys.com>            cc:                                           
                             
                    Sent by:             Subject:     RE: Spooling to a flat file via 
SQL*Plus                      
                    root_at_fatcity.c                                                     
                             
                    om                                                                 
                             
                                                                                       
                             
                                                                                       
                             
                    12/12/01 07:40                                                     
                             
                    AM                                                                 
                             
                    Please respond                                                     
                             
                    to ORACLE-L

Cherie:

If there are any commas in the date fields you will have problems with a comma delimited file. You may want to try "~" tilde delimited instead.

My $0.02,

Ken

-----Original Message-----

Sent:           Wednesday, December 12, 2001 9: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: Ken Janusz
INET: ken.janusz_at_sufsys.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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.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