Home » SQL & PL/SQL » SQL & PL/SQL » String functions on Long column
String functions on Long column [message #379357] Tue, 06 January 2009 03:21 Go to next message
ramya29p
Messages: 100
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
Re: String functions on Long column [message #379359 is a reply to message #379357] Tue, 06 January 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: String functions on Long column [message #379360 is a reply to message #379359] Tue, 06 January 2009 03:38 Go to previous messageGo to next message
ramya29p
Messages: 100
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?
Re: String functions on Long column [message #379362 is a reply to message #379360] Tue, 06 January 2009 03:52 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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.
Re: String functions on Long column [message #379502 is a reply to message #379360] Tue, 06 January 2009 17:50 Go to previous message
Barbara Boehmer
Messages: 8625
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
Previous Topic: alter sequence when an exception occurs in PL\SQL
Next Topic: want to display fields using dbms_output.put_line
Goto Forum:
  


Current Time: Sun Dec 04 06:48:05 CST 2016

Total time taken to generate the page: 0.11308 seconds