Re: writing contents of oracle long variable to file

Yass Khogaly wrote:
>
> How to convert a long datatype to a varchar2.
>
> Here are two methods for converting a long datatype column to a
> varchar2(2000):
>
> 1. Use pl/sql.
> (This method assumes that the LONG column <= 2000 characters)
>
> create or replace procedure procedure_name is
> cursor c0 is
> select rowid, long_column from table_name;
> begin
> for c1 in c0 loop
> update table_name
> set varchar_column = c1.long_column
> where rowid=c1.rowid;
> end loop;
> end;
>
> (This example of pl/sql demonstrates a way to perform string
> manipulation on the long.)
>
> declare
> cursor my_cursor is
> select long_col
> from my_table;
> my_var varchar2(32767);
> begin
> open my_cursor;
> loop
> fetch my_cursor into my_var;
> exit when my_cursor%notfound;
> my_var := substr(my_var,1,2000);
> insert into new_table values (my_var);
> end loop;
> close my_cursor;
> end;
>
> 2. Use export and import.
> a. export the table with the long column
> b. drop or rename the table with the long column
> c. import with show=y and log option to write the table definition to
> a file.
> imp username/passwd file=exp_file_name log=imp.out show=y
> d. edit the log file (imp.out) and change the long column type to
> varchar2. Also edit it so that it is a script.
> e. run the log file (imp.out) as a script to create the table with
> the same name, but the datatype being changed from long to varchar2
> f. import data with ignore=y
> ex. imp username/passwd file=exp_file_name log=imp.out
> tables=name_of_table(s)_importing
>
> You cannot convert a long datatype to a varchar2 in the following ways:
> 1. alter table command
> alter table long_table modify col2 varchar2(300);
>
> gives errors:
> ERROR at line 1:
> ORA-01439: column to be modified must be empty to change datatype
>
> 2. copy command
>
> a. sqlplus username/passwd
> b. create a new table (varchar2_table) with varchar2 instead of long
> c. set long xxx (value <= 2000)
> d. copy to scott/tiger_at_t:machine:SID -
> insert varchar2_table (col1, col2) -
> using select * from long_table;
>
> gives error:
> CPY0005: Source and destination column attributes don't match
>
> The column types do not match. Copy requires that the column types be
> the same.
>
> "The Views expressed here are my own and not necessarily those of Oracle
> Corporation"
>
> Niels Stout <n.stout_at_voltaire.nl> wrote in message
> news:uy#xqe6M$GA.140_at_net025s...
> > Hi
> >
> > I need to write contents of oracle long variable to a flat file.
> >
> > Any suggetions would be welcome
> >
> > Cheers,
> >
> > Niels
> >
> >

Just to be accurate...its 32767 chars not 2000 chars

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."