Home » SQL & PL/SQL » SQL & PL/SQL » Convert LONG to CLOB in a PL/SQL (Oracle 9i)
Convert LONG to CLOB in a PL/SQL [message #399094] Mon, 20 April 2009 09:26 Go to next message
Nau
Messages: 24
Registered: October 2004
Junior Member


I would like to know if there is possible to trasnforma a CLOB type variable intro a long type variable
I know, that LONG is obsolete in Oracle, but I need it, because in a PL/SQL 'execute immediate' sentence
a CLOB is not allowed

Any help will be greatly apreciatted.
Re: Convert LONG to CLOB in a PL/SQL [message #399095 is a reply to message #399094] Mon, 20 April 2009 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've got < 32K of text, just convert it to a Varchar2.

If you've got > 32k of text, I'd really ask what you're doing with that much dynamic sql.
Re: Convert LONG to CLOB in a PL/SQL [message #399097 is a reply to message #399094] Mon, 20 April 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would like to know if there is possible to trasnforma a CLOB type variable intro a long type variable

Why do you want to go to hell?

Quote:
I know, that LONG is obsolete in Oracle, but I need it, because in a PL/SQL 'execute immediate' sentence
a CLOB is not allowed

Use dbms_sql.

Regards
Michel
Re: Convert LONG to CLOB in a PL/SQL [message #399100 is a reply to message #399097] Mon, 20 April 2009 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also upgrade to 11g:
SQB> create table t (col clob);

Table created.

SQB> insert into t values('insert into t values(''michel'')');

1 row created.

SQB> 
SQB> commit;

Commit complete.

SQB> select * from t;
COL
----------------------------------------------------------------
insert into t values('michel')

1 row selected.

SQB> begin
  2     for rec in (select col from t) loop
  3       execute immediate rec.col;
  4     end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQB> select * from t;
COL
----------------------------------------------------------------
insert into t values('michel')
michel

2 rows selected.

SQB> @v

Version Oracle : 11.1.0.6.0

Regards
Michel
Re: Convert LONG to CLOB in a PL/SQL [message #503805 is a reply to message #399100] Tue, 19 April 2011 14:43 Go to previous messageGo to next message
csingsk
Messages: 1
Registered: April 2011
Location: Alexandria,VA
Junior Member
The given function helps you to select,search local as well as remote table with long text

create or replace
function long_column( p_query in varchar2,
p_owner in varchar2,
p_owner_value in varchar2,
p_name in varchar2,
p_value in varchar2 )
return clob
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val clob;
l_long_piece clob;
l_long_len number;
l_buflen number := 32760;
l_curpos number := 0;
l_return_value number;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );

dbms_sql.bind_variable( l_cursor,p_owner, p_owner_value );
dbms_sql.bind_variable( l_cursor,p_name, p_value );

dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);

if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
l_return_value := nvl(l_return_value,0) + l_long_len;

exit when l_long_len = 0;

l_long_piece := l_long_piece||l_long_val; -- added

end loop;
end if;
dbms_sql.close_cursor(l_cursor); -- added

return l_long_piece;
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end long_column;

To select the remote long table :
select owner,view_name, text_length,
long_column( 'select text
from all_views
where owner=:owner and view_name = :vname',
':owner',owner,':vname', view_name ) ll
from all_views

To search remote long column :
select * from (
select owner,view_name, text_length,
long_column( 'select text from all_views@deblink where owner=:owner and
view_name = :vname',
':owner',owner,':vname', view_name ) ll
from all_views@dblink
)
where ll like '%test%'

This function can be used to replicate remote table with long column:
create table long_t as
select owner,view_name, text_length,
long_column( 'select text from all_views@dblink where owner=:owner and
view_name = :vname',
':owner',owner,':vname', view_name ) ll
from all_views@dblink
Re: Convert LONG to CLOB in a PL/SQL [message #503809 is a reply to message #503805] Tue, 19 April 2011 14:51 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A non formatted code is useless as unreadble.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: ORA-04088 on - trigger execution
Next Topic: PL/SQL Mind Teaser HELP PLEASE
Goto Forum:
  


Current Time: Sun Dec 04 10:53:40 CST 2016

Total time taken to generate the page: 0.09670 seconds