OraFAQ Forum: SQL & PL/SQL » How do I retrieve a substring from a clob variable

Home » SQL & PL/SQL » SQL & PL/SQL » How do I retrieve a substring from a clob variable

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

How do I retrieve a substring from a clob variable [message #9779]

Thu, 04 December 2003 03:17

Yi Xu
Messages: 5
Registered: October 2003

Junior Member

I am writing PL/SQL. I need to retrieve a substring of a clob starting with a particular "STRING" until the end of the clob.

I tried the following statement, but my results is always an empty string. It seems to me that the INSTR function is not returning the appropriate location, but I don't know what other functions to use. I even tried dbms_lob.trim, but that fails me in unexpected ways as well. Please help!

DBMS_LOB.SUBSTR(CLOB, DBMS_LOB.INSTR(CLOB, 'STRING'),
DBMS_LOB.GETLENGTH(COM.COMMENTS_CLOB))

FYI:
I converted this colb column from a long by doing the following:
create TABLE NEW_CLOB as
select TO_LOB(column_long) COLUMN_CLOB
from ORIG_TABLE;

Report message to a moderator

Re: How do I retrieve a substring from a clob variable [message #9780 is a reply to message #9779]

Thu, 04 December 2003 04:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Invert the arguments in dbms_lob.substr:
Dbms_lob.substr(lob_column, amount, offset)
offset = the starting point = instr(....)
amount = length of the substr = dbms_lob.getlength()

MHE

Report message to a moderator

Re: How do I retrieve a substring from a clob variable [message #9782 is a reply to message #9779]

Thu, 04 December 2003 04:38

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

Here is an example for you, one piece at a time:

Code: [Select all] [Show/ hide]

scott@ORA92> CREATE TABLE new_clob
  2  AS
  3  SELECT TO_LOB (text) column_clob
  4  FROM   user_views
  5  /

Table created.

scott@ORA92> DESC new_clob
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 COLUMN_CLOB                                                    CLOB

scott@ORA92> SELECT * FROM new_clob
  2  /

COLUMN_CLOB
--------------------------------------------------------------------------------
select dname from dept where deptno= 10
select ename from emp where deptno = 10

scott@ORA92> -- starting position of string 'where' within clob:
scott@ORA92> SELECT DBMS_LOB.INSTR (column_clob, 'where', 1, 1) FROM new_clob
  2  /

DBMS_LOB.INSTR(COLUMN_CLOB,'WHERE',1,1)
---------------------------------------
                                     24
                                     23

scott@ORA92> -- length of clob:
scott@ORA92> SELECT DBMS_LOB.GETLENGTH (column_clob) FROM new_clob
  2  /

DBMS_LOB.GETLENGTH(COLUMN_CLOB)
-------------------------------
                             39
                             39

scott@ORA92> -- length of clob starting from string 'where':
scott@ORA92> SELECT (DBMS_LOB.GETLENGTH (column_clob) - DBMS_LOB.INSTR (column_clob, 'where')) + 1
  2  FROM   new_clob
  3  /

(DBMS_LOB.GETLENGTH(COLUMN_CLOB)-DBMS_LOB.INSTR(COLUMN_CLOB,'WHERE'))+1
-----------------------------------------------------------------------
                                                                     16
                                                                     17

scott@ORA92> -- sql:
scott@ORA92> SELECT DBMS_LOB.SUBSTR
  2  (column_clob,
  3  (DBMS_LOB.GETLENGTH (column_clob) - DBMS_LOB.INSTR (column_clob, 'where')) + 1, -- length
  4  DBMS_LOB.INSTR (column_clob, 'where')) -- starting position
  5  FROM   new_clob
  6  /

DBMS_LOB.SUBSTR(COLUMN_CLOB,(DBMS_LOB.GETLENGTH(COLUMN_CLOB)-DBMS_LOB.INSTR(COLUMN_CLOB,'WHERE'))+1,
----------------------------------------------------------------------------------------------------
where deptno= 10
where deptno = 10

scott@ORA92> -- pl/sql:
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    v_string VARCHAR2 (10) := 'where';
  3  BEGIN
  4    FOR rec IN
  5  	 (SELECT DBMS_LOB.SUBSTR
  6  	  (column_clob,
  7  	  (DBMS_LOB.GETLENGTH (column_clob) - DBMS_LOB.INSTR (column_clob, v_string)) + 1,
  8  	  DBMS_LOB.INSTR (column_clob, v_string)) AS partial_clob
  9  	  FROM	 new_clob)
 10    LOOP
 11  	 DBMS_OUTPUT.PUT_LINE (rec.partial_clob);
 12    END LOOP;
 13  END;
 14  /
where deptno= 10
where deptno = 10

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	How to get all dates between two dates in Oracle?
Next Topic:	Migration from oracle 8i to 9

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 05:20:45 CDT 2025