FAQ Database Discussion Community

In pl/sql how can I execute a select statement stored in CLOB type of field?

In Oracle DB, I have a large select staetment with lots of joins and cases that is stored in a CLOB field in one of my tables. How can i execute this statement from the CLOB?

Convert datatype Clob to Varchar2 Oracle

I have an Oracle table with a column of type clob. I want to preserve the column order and change the datatype to a varchar2. The column just contains text. update IN_MSG_BOARD set MSG_TEXT = null; alter table IN_MSG_BOARD modify MSG_TEXT long; alter table IN_MSG_BOARD modify MSG_TEXT varchar2(4000); I'm getting...

How do I use Oracle utl_file to write an image clob

I have an Oracle Apex application which generates automated eMails. In Apex, the user inserts a JPG image into a rich text field. That image is saved into a CLOB field. When the stored procedure is called, it reads the JPG image and stores it into a local variable called...

Can't extract the data from xml values in oracle sql

I have the following 2 xml value which is similar and which is stored in the request_xml column and which is clob data type: <?xml version='1.0' encoding='utf-8'?> <S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"> <S:Body> <ns5:updateRechargeTicket xmlns:ns5="http://service.soap.CDRator.com" xmlns:ns2="http://core.result.service.soap.CDRator.com/xsd" xmlns="http://core.data.soap.CDRator.com/xsd" xmlns:ns4="http://data.soap.CDRator.com/xsd"...

Insert Binary String into CLOB column in Oracle using OLEDB

I have this binary string (see below) to be inserted into CLOB column in Oracle using OLEDB. binary string :...

ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000

I have column response_xml and request_xml which consist of large string. I have used substring function for this large string in order to fetch the SUBSCRIPTION_ID from response_xml and orderType from request_xml. The query is working fine. But now i want to put condition for this query such that it...

overcome 32k limit when inserting oracle clob IN parameter using spring StoredProcedure

Environment: oracle 11g, spring-jdbc-3.2.2-RELEASE.jar, JDK 1.7, Oracle UCP driver. I have a stored procedure which insert record to a table with CLOB column. The SP has a CLOB input argument among other IN and OUT arguments. My Java code uses Spring StoredProcedure to call the stored procedure: public class MyClass...

CLOB value in out/return from plsql (invalid LOB locator specified: ORA-22275)

I've got stored plsql procedure, that takes big text from file create or replace procedure dbst_load_a_file( p_file_name in varchar2, l_clob out clob ) as l_bfile bfile; dst_offset number := 1 ; src_offset number := 1 ; lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX; warning number; begin l_bfile := bfilename( 'SCHEMES_OF_PS', p_file_name ); dbms_lob.fileopen(...

Count CLOB Duplicates in a large Oracle Table

I have an Oracle database table LOG_MESSAGES with a CLOB column called MESSAGE. Some of the rows contain the same MESSAGE. For each MESSAGE which has at least a duplicate, I'd like to know the number of duplicates. Quite a number of these CLOBs are huge (> 100 kB), so...

Oracle: add CLOB to selection when using GROUP BY

I have a query similar to SELECT s.id, max (s.status) AS status, s.clob FROM my_table s INNER JOIN my_table_2 s2 ON s.id = s2.id GROUP BY s.id ORDER BY s.status DESC The real query is a bit more complex but it is not relevant here. The issue I have is...

CLOB in oracle not allowing to insert data more than 4000 chars

Below is my table create table MY_LOG(SQNO NUMBER, CLOB_FLD CLOB); Below is my insert statement executed from java side stmt.execute(new StringBuilder( "INSERT INTO MY_LOG VALUES ('").append(logMessage.getNo()).append("','").append(logMessage.getStackTrace()).append("')")); the stacktrace is too large i.e more than 4000 characters. So Its throwing SQL Error: ORA-01704: string literal too long 01704. 00000 - "string...