Converting Long Data types

[TalentedFool]

Is there any way of converting a long data type to either varchar or varchar2?

I've read the documentation about longs and they say that you can't perform any functions on a long so I'm a bit stuck.

Cheers

Lee Walters

 

[wushutwist]

Copy and paste. Temporarily move the Long column contents into a temporary table and drop the column. Then add the column back as a varchar2. You may run into problems if you don't may the varchar2 column large enough to hold the biggest value in the current LONG column. Sometimes there is just no getting away from LONG. Wushutwist

 

[TalentedFool]

I've tried that and can't do it.

What I've got is a table that holds 2 fields, the first is the keys and the 2nd is the long field. This field contains Carriage return line feeds so when I try to create the new table it gives an error message of ORA-00997: illegal use of LONG datatype.

What I'm actually trying to do is split the long field so that every time I hit a CR/LF I write away a new record with the same key and a new sequence no to a different table i.e. if the long has 2 cr/lf then the new table will hold two records for this long. If that makes any sense.

Any thoughts ?

 

[mist139]

this is a extract from a piece of code to extract views text (long type) from data dictionary; a task similar to one you have.

Hope this will give you an idea how to solve your problem.

create table obj_generate (
oseq number,
oline varchar2(4000));



declare
output_line varchar2(4000) := ' ';
view_line varchar2(32000) := ' ';
line_length number :=0;
ptr number :=0;
vtype dba_source.type%type;

type my_table_type is table of number --varchar2(4000)
index by binary_integer;

instr_occur my_table_type;

Procedure prnt is
begin
insert into obj_generate
(oseq, oline)
values (oseq.nextval,
'!'||output_line);
end;

begin
select text,text_length
into view_line,line_length
from dba_views
where owner = upper('&obj_owner')
and view_name = upper('&object')
;
view_line := view_line||';';
view_line := replace(view_line,chr(10)||chr(10),chr(10));

if line_length < 4000 then
output_line := view_line;
prnt;
else
view_line := replace(view_line,chr(10),'~');
for i in 1..100 loop
ptr := instr(view_line,'~',1);
output_line := substr(view_line,1,ptr-1);
prnt;
view_line := substr(view_line,ptr+1);
if ptr=0 then
output_line := view_line;
prnt;
exit;
end if;
end loop;
end if;
end;
/