Re: Oracle7.2.3-Datatype problem

Option A

1. U can define a new table with the LONG column now defined as VARCHAR2(2000)
2. U can write a simple stored procedure that reads records from old table and insert into the new table for cur in (select * from old_table) loop insert into new_table values (rtrim(cur.name), rtrim(cur.descr) ....; end loop;
3. now do the SELECT

Option B

1. package utl_file allows u to write to a flat file. u have to set utl_file_dir in your init(sid).ora to a directory name and the flat file gets created there.
2. just write a procedure which opens a file in write mode,reads records from file and just write to the file ((problme is there is a limit of 1000+ characters per line). read utlfile.sql under $ORACLE_HOME/rdbms/admin

bye
bhooshan

Andreas Mueller wrote:

> Hello!!!
>
> I need to export a table with a column of type LONG to a comma-separated
> textfile. I use SQL*PLUS with spooling and the following SQL-query:
>
> SELECT rtrim(name) ||
> ',' ||
> rtrim(descr)||
> ',' ||
> rtrim(text) ||
> ',' ||
> code || --< this is the LONG-field.
> ';'
>
> I know that LONG-fields cannot be used with operators or functions, but
> the output should look like this:
>
> name,descr,text,code;
>
> I already tried copying the LONG-values to a new table where the
> respective column is defined as VARCHAR2(2000), however this seems to be
> impossible, too.
>
> Since I only have here Oracle7.2.3 on HP-UX10.20 with SQL*PLUS and no
> further tools, I need to know if there's any possibility to convert or
> use the LONG-type-field so I can write it in the desired way.
>
> Has anyone an idea??? Any hints are greatly appreciated!!!
>
> Thanks,
> Andreas Mueller
> SCR Princeton
Received on Wed Jun 30 1999 - 05:27:32 CDT