Home » SQL & PL/SQL » SQL & PL/SQL » select clob (9i linux)
select clob [message #336608] Mon, 28 July 2008 06:34 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
create table table_clob (colid number,test_col1_clob clob,test_col2_clob clob)



i want to select clob column
sample plsql code

create or replace procedure select_clob( 
testid number, testclob OUT varchar2 ) 
as 
l_clob clob; 
begin 
select test_col_clob into l_clob 
from table_clob 
where col_id = testid; 
testclob := dbms_lob.substr( l_clob, 32765, 1 ); 
end; 


SELECT DBMS_LOB.SUBSTR (test_col1_clob, 32765, 1) FROM table_clob where col_id=205;
Re: select clob [message #336610 is a reply to message #336608] Mon, 28 July 2008 06:37 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Where is the problem?? Confused

Regards,
Rajat
Re: select clob [message #336620 is a reply to message #336610] Mon, 28 July 2008 07:21 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i not able to select

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Re: select clob [message #336622 is a reply to message #336620] Mon, 28 July 2008 07:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What is the limit of varchar2 datattype??

Have you checked??That will solve your problem.

Regards,
Rajat Ratewal
Re: select clob [message #336633 is a reply to message #336622] Mon, 28 July 2008 08:08 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i know its around 32KB
when i use
SELECT DBMS_LOB.SUBSTR (test_col1_clob, 3999, 1) FROM table_clob where col_id=205;
it works

how can i select more then 4000
Re: select clob [message #336643 is a reply to message #336633] Mon, 28 July 2008 08:52 Go to previous message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
In pl/sql the limit for the size of the varchar2 datatype is 32767. However, in sql the limit for the size of the varchar2 datatype is 4000. So, when you try to execute the procedure resulting in a "testclob out varchar2" trying to output a string greater than 4000 characters to a sql or sql*plus varchar2 variable with a limit of 4000 you get the error. Just use a clob out data type instead of varchar2 or limit your output to 4000 characters. Also, it might help to know what your overall goal is as there may be a better way.

Previous Topic: Can we write two "Insert into.." on a single table
Next Topic: Index
Goto Forum:
  


Current Time: Tue Dec 06 16:26:07 CST 2016

Total time taken to generate the page: 0.08698 seconds