OraFAQ Forum: SQL & PL/SQL » String functions on Long column

Home » SQL & PL/SQL » SQL & PL/SQL » String functions on Long column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

String functions on Long column [message #379357]

Tue, 06 January 2009 03:21

ramya29p
Messages: 146
Registered: November 2007
Location: Chennai

Senior Member

Hi ,
can anyone tell me how to use string functions like replace,substr on LONG Datatype column..?
i tried with execute immediate but getting error.

SQL> /
declare
i varchar2(20) := 'STPMTTS1006000E9';
begin
execute immediate 'select to_lob(notification_message) from gwtb_notifications_log where pkey_values=:1' using i;
end;

ORA-00932: inconsistent datatypes: expected - got LONG
ORA-06512: at line 4

Report message to a moderator

Re: String functions on Long column [message #379359 is a reply to message #379357]

Tue, 06 January 2009 03:28

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can't use string functions (or any function) on LONG.
TO_LOB is only to move LONG data to CLOB field.

Regards
Michel

Report message to a moderator

Re: String functions on Long column [message #379360 is a reply to message #379359]

Tue, 06 January 2009 03:38

ramya29p
Messages: 146
Registered: November 2007
Location: Chennai

Senior Member

Is there any way to use string function on LONG Column..?If we cannot use any function on LONG column then what is the use of that datatype?

Report message to a moderator

Re: String functions on Long column [message #379362 is a reply to message #379360]

Tue, 06 January 2009 03:52

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

That is exactly the point: Oracle and everybody else is moving away from LONG as it's too limited. As Michel said, you really should change to LOBs.

Report message to a moderator

Re: String functions on Long column [message #379502 is a reply to message #379360]

Tue, 06 January 2009 17:50

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

If this is something you are just creating, then use a CLOB datatype. It this is something that you are allowed to change, then change it from LONG to CLOB. There are various ways to do that. If you cannot change the datatype immediately, then Tom Kyte has some workarounds in the meanwhile:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55212348054

Report message to a moderator

Previous Topic:	alter sequence when an exception occurs in PL\SQL
Next Topic:	want to display fields using dbms_output.put_line

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Oct 04 19:52:27 CDT 2025