Re: writing contents of oracle long variable to file

From: Yass Khogaly <ykhogaly_at_us.oracle.com>
Date: Sat, 20 Nov 1999 20:03:19 -0800
Message-ID: <817na7$fho$1@inet16.us.oracle.com>

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

1. sqlplus username/passwd
2. create a new table (varchar2_table) with varchar2 instead of long
3. set long xxx (value <= 2000)
4. 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
>
>
Received on Sat Nov 20 1999 - 22:03:19 CST