Home » SQL & PL/SQL » SQL & PL/SQL » Using substr on LONG data type in PL/SQL
- Using substr on LONG data type in PL/SQL [message #132328] Thu, 11 August 2005 22:44 Go to next message
taxali
Messages: 5
Registered: March 2004
Junior Member
Can someone tell me - why is it that I can use the substr on a LONG type column in PL/SQL but not in plain SQL??
For example, on asktom.com, the following code is listed that works OK:
create or replace
1 function getlong1( p_rowid in rowid)
2 return varchar2
3 as
4 l_data long;
5 begin
6 select remarks into l_data from cr_claimheader where rowid = p_rowid;
7 return substr( l_data, 1, 4000 );
8 end;

Line 7 uses substr on a LONG column without any problems!

Report message to a moderator

- Re: Using substr on LONG data type in PL/SQL [message #132336 is a reply to message #132328] Fri, 12 August 2005 00:13 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Because the PL/SQL engine does not work the same way the SQL engine does. In PL/SQL you can also use a procedure call, in SQL not. In SQL you can use DECODE, in PL/SQL not, the SQL VARCHAR2 is limited to 4000 bytes, the PL/SQL variant to 32K...

Internally the PL/SQL substr and the SQL variant are not the same, although they carry the same name and do the same thing.

MHE

Report message to a moderator

- Re: Using substr on LONG data type in PL/SQL [message #132424 is a reply to message #132328] Fri, 12 August 2005 12:28 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hello.

This is the whole reason why Oracle "introduced" the CLOB datatype, which you can do string operations on in SQL.

Rgds

Report message to a moderator

Previous Topic: Bitmap indexes
Next Topic: Sequence Information
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 28 07:19:41 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.