How to query a CLOB column in Oracle

sql oracle plsql clob ora-06502
Catfish picture Catfish · Sep 24, 2010 · Viewed 429.8k times · Source

I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value.

I tried using DBMS_LOB.substr(column) and i get the error 

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

How can i query the CLOB column?

Answer

Chris picture Chris · Nov 17, 2010

This works

select DBMS_LOB.substr(myColumn, 3000) from myTable

Related questions

Performance of SUBSTR on CLOB

I have a PL/SQL procedure that does a lot of SUBSTRs on a VARCHAR2 parameter. I would like to remove the length limit, so I tried to change it to CLOB. Works fine, but performance suffers, so I did …

Read More
How to generate JSON in Oracle for a CLOB that is > 32k (e.g. 60,000 characters)?

1 ) I have to make json from oracle select query which has three approach i can follow . SELECT JSON_ARRAY(json_object('id' VALUE employee_id, 'data_clob' VALUE data_clob )) from tablename; also i have tried with this approach 2) If …

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More