OraFAQ Forum: SQL & PL/SQL » CLOB extraction

Home » SQL & PL/SQL » SQL & PL/SQL » CLOB extraction (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CLOB extraction [message #608865]

Tue, 25 February 2014 14:11

OraFaq13
Messages: 14
Registered: May 2013

Junior Member

CREATE TABLE "DEV"."clob_test" 
  ( 
     "id"       NUMBER NOT NULL ENABLE, 
     "clob_val" CLOB, 
     CONSTRAINT "CLOB_TEST_PK" PRIMARY KEY ("id") 
  );

The clob_val column will have value like given below and we wanted to extract the FIELD, OLD_VALUE, NEW_VALUE tag values and stored them in a different table.

"(CLOB) {
	"MODIFIED_DATA" : [{
			"FIELD" : "END DATE",
			"OLD_VALUE" : "",
			"NEW_VALUE" : "11/22/2013"
		}, {
			"FIELD" : "ADDRESS LINE 1",
			"OLD_VALUE" : "35606 CRESTVIEW DR",
			"NEW_VALUE" : "35606 CRESTVIEW APT 222"
		}, {
			"FIELD" : "CITY",
			"OLD_VALUE" : "YUCAIPA",
			"NEW_VALUE" : "RANCHO CORDOVA"
		}, {
			"FIELD" : "ZIP CODE",
			"OLD_VALUE" : "92399",
			"NEW_VALUE" : "95670"
		}
	], {
		"BUTTON" : "SAVE AND RETURN BUTTON"
	}
}"

Its not going to get stored as XML tags so not sure how to extract those tag values. Any help would be appreciated.

PS: Version info

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Report message to a moderator

Re: CLOB extraction [message #608866 is a reply to message #608865]

Tue, 25 February 2014 14:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what is minimum number of "FIELD" occurrences in CLOB_VAL?
what is maximum number of "FIELD" occurrences in CLOB_VAL?

post CREATE TABLE statement for the "different table"

Report message to a moderator

Re: CLOB extraction [message #608867 is a reply to message #608866]

Tue, 25 February 2014 14:37

OraFaq13
Messages: 14
Registered: May 2013

Junior Member

The minimum no of field could be 1 and max could be 25.

The other table structure would be like

CREATE TABLE CLOB_Ext( ID number , Field Varchar2(50), OLD_VALUE Varchar2(4000), NEW_VALUE varchar2(4000));

Report message to a moderator

Re: CLOB extraction [message #608868 is a reply to message #608865]

Tue, 25 February 2014 14:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not XML but JSON.
I don't there is a built-in PL/SQL package for this but it is not hard to create one (check what has be done for XML functions in Oracle).
I also think there is some chance you find some (PL/SQL or not PL/SQL) ones in the web, and if it is in Java you can embed it into the DB.

[Updated on: Tue, 25 February 2014 15:00]

Report message to a moderator

Re: CLOB extraction [message #608869 is a reply to message #608868]

Tue, 25 February 2014 16:42

OraFaq13
Messages: 14
Registered: May 2013

Junior Member

How do can we achieve this?

select  substr(clob_val, 1, 5) first10char, substr(clob_val, 10, 15) from clob_test;

I just tried extracting the 1st 10 characters and the next 5 characters using the above. The 2nd substr prints "(CLOB) FIED_DATA" : [{" , not sure why this CLOB comes everywhere I extract , no matter from which position.

Report message to a moderator

Re: CLOB extraction [message #608871 is a reply to message #608869]

Tue, 25 February 2014 17:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I am not sure what your problem is.


  1  declare
  2  vclob clob := '1234567890abcdefghijklnopqrstuvwxyz';
  3  begin
  4  dbms_output.enable(31000);
  5  dbms_output.put_line(substr(vclob,1,10)|| ' --- ' || substr(vclob,11,12));
  6* end;
SQL> /
1234567890 --- abcdefghijkl

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	Query for the below
Next Topic:	how to make it by help of procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 08:14:02 CDT 2024