writing contents of oracle long variable to file

[Niels Stout]

Hi

I need to write contents of oracle long variable to a flat file.

Any suggetions would be welcome

Cheers,

Niels

[Yass Khogaly]

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@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.s...@voltaire.nl> wrote in message
news:uy#xqe6M$GA.140@net025s...

[Connor McDonald]

Just to be accurate...its 32767 chars not 2000 chars
--
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_...@yahoo.com

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

[sunri...@my-deja.com]

> I need to write contents of oracle long variable to a flat file.

This is code from this book: Orcale 8 PL/SQL Programming By Scott Urman.
If you are using Oracle 7.34 ... it does not show you how to get around
the 1023 Utl_file limit. We had to write out a special record and
write a separate C program to get around that problem.

CREATE OR REPLACE FUNCTION dump_doc(docid IN NUMBER,
filename IN VARCHAR2)
RETURN VARCHAR2
IS
data_chunk VARCHAR2(254);
chunk_size NUMBER:=254;
chunk_size_returned NUMBER;
location VARCHAR2(20) := '/tmp';
mycursor NUMBER;
stmt VARCHAR2(1024);
cur_pos NUMBER:=0;
rows NUMBER;
dummy NUMBER;
file_handle UTL_FILE.FILE_TYPE;
status VARCHAR2(50);
BEGIN
file_handle:=utl_file.fopen(location,filename,'w');
-- open the file for writing

stmt:='SELECT DOCUMENT FROM ASCII_DOCS WHERE ID = :doctoget';

mycursor:=dbms_sql.open_cursor;
dbms_sql.parse(mycursor, stmt, dbms_sql.v7);
dbms_sql.bind_variable(mycursor, ':doctoget', docid);
-- bind the doctoget host variable with the plsql parameter
docid
-- which is passed into the function

dbms_sql.define_column_long(mycursor,1);
dummy:=dbms_sql.execute(mycursor);
rows:=dbms_sql.fetch_rows(mycursor);
-- only doing one fetch for the primary key as assuming the
whole
-- document is stored in one row
loop
-- fetch 'chunks' of the long until we have got the lot
dbms_sql.column_value_long(mycursor,
1,
chunk_size,
cur_pos,
data_chunk,
chunk_size_returned);
utl_file.put(file_handle, data_chunk);
cur_pos:=cur_pos + chunk_size;
exit when chunk_size_returned = 0;
end loop;
dbms_sql.close_cursor(mycursor);
utl_file.fclose(file_handle);
return('Success');
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose(file_handle);
return ('Failure');
END dump_doc;
/

Sent via Deja.com http://www.deja.com/
Before you buy.