Home » SQL & PL/SQL » SQL & PL/SQL » Maximum Size of LONG datatype in PL/SQL
Maximum Size of LONG datatype in PL/SQL [message #47187] Wed, 28 July 2004 02:37 Go to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Hi,
I've a procedure written in PL/SQL where in application numbers are getting concatenated to a variable in loop. And the length is exceeding 32767 ( Max for VARCHAR2) . Then I tried with LONG datatype for that variable but same happenes there. Then I made the variable as CLOB. IT works and even the functions like SUBSTR,LENGTH also works on variable of type CLOB. Surprised to see this

Can anybody explain me the reason :-
1 Why LONG doesn't accept value more than 32760 in Pl/SQL .?

2. Why substr and length functions worked with CLOB datatype when they were supposed to use DBMS_LOB package ?

3.Is there any performance impact of using variable as CLOB in Procedure for storing longer strings ?

Thanks

Sunil
Re: Maximum Size of LONG datatype in PL/SQL [message #47190 is a reply to message #47187] Wed, 28 July 2004 03:58 Go to previous messageGo to next message
Kapil
Messages: 145
Registered: May 2002
Senior Member
1.A Long Column in Database can store upto 2GB of data,but a variable of data type LONG can store up to 32760 in PL/SQL.
2.For your second query ,i think u have to mention which version of Oracle you are using.
Re: Maximum Size of LONG datatype in PL/SQL [message #47204 is a reply to message #47187] Wed, 28 July 2004 20:25 Go to previous messageGo to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Hi ,
I got the point . It worked in ORACLE 9i but when I used the same code in 8i it throws error. Now I 've to use DBMS_LOB package. Thanks for ur advice. I was not aware of this new feature in 9i .

One thing I need to ask . If I have stored a query in a table column whose datatype is LONG. AND in PL/SQL code I am retreiving this value in a Long datatype variable. Will it throw error if the value in column exceed 32760 ..? Pls advice..

Thanks
Sunil
Re: Maximum Size of LONG datatype in PL/SQL [message #47917 is a reply to message #47190] Fri, 01 October 2004 04:29 Go to previous messageGo to next message
Hema.V
Messages: 1
Registered: October 2004
Junior Member
It is true that a variable of datatype LONG can store upto 32760 in a PL/SQL. I have a procedure in which two variables go beyond the limit allowed in LONG. So I reassigned the variables a CLOB datatype, the code works fine. The procedure earlier took 25 seconds for a output but it now takes 10 minutes!!! Can someone please explain why ???
Re: Maximum Size of LONG datatype in PL/SQL [message #116632 is a reply to message #47187] Thu, 21 April 2005 00:39 Go to previous messageGo to next message
dhamayanthi
Messages: 17
Registered: April 2005
Location: India
Junior Member
Hi, the same kind of problem i am having....

i have seen that long pl/sql variable can have only 32760. but same time in oracle database, long (as database column) can have upto 2gb of data. as if having 2gb of size, there should be some way to insert that much data. so i want to know what is the way to store more than 32k by a pl/sql variable?

for that i tried clob as pl/sql variable and it gets >32k and shows length 43K. and i inserted this into long column and i didn't say any error. i selected the long database column into clob pl/sql variable and it gives only 4000 bytes of data.

and everybody replies me to change the data type to clob in table.But I can't change it into clob datatype since i have to do some modifications in existing appl. and the table is referred by many procedures and functions.

Thanks,
Dhamayanthi K.

Re: Maximum Size of LONG datatype in PL/SQL [message #354226 is a reply to message #116632] Fri, 17 October 2008 01:49 Go to previous messageGo to next message
smithakr
Messages: 2
Registered: October 2008
Junior Member
Hi,
am also facing the same issue of inserting >32K data into LONG column.Please can you let know ,how did you solve this problem as soon as possible


appreciate your help.


Thanks
Re: Maximum Size of LONG datatype in PL/SQL [message #354241 is a reply to message #354226] Fri, 17 October 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CLOB instead of LONG.

Regards
Michel
Re: Maximum Size of LONG datatype in PL/SQL [message #354245 is a reply to message #354241] Fri, 17 October 2008 03:08 Go to previous messageGo to next message
smithakr
Messages: 2
Registered: October 2008
Junior Member
hi,
Do you mean to use Clob in the Column.
I cant change the column datatype from long to Clob.

if am using clob variable to insert 40K of data into long column,its inserting without any errors but the data is truncating to 4k.

Please help,

thanks & regards,
Re: Maximum Size of LONG datatype in PL/SQL [message #354250 is a reply to message #354245] Fri, 17 October 2008 03:27 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do it wrong but as you didn't post what you did the is the end of the help.

Before posting any code 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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: table data verification
Next Topic: query
Goto Forum:
  


Current Time: Thu Dec 08 10:31:31 CST 2016

Total time taken to generate the page: 0.06017 seconds