OraFAQ Forum: SQL & PL/SQL » Clob to string conversion

Home » SQL & PL/SQL » SQL & PL/SQL » Clob to string conversion

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

Clob to string conversion [message #10329]

Wed, 14 January 2004 23:22

Pauline
Messages: 6
Registered: February 2002

Junior Member

How can I convert clob data values to string (char/varchar2). I have tried the to_char function but this does not work.

Thanks.

Report message to a moderator

Re: Clob to string conversion [message #10332 is a reply to message #10329]

Thu, 15 January 2004 00:03

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

Senior Member

The following will retrieve the first 30 characters:

SELECT DBMS_LOB.SUBSTR (your_clob_column_name, 30, 1) FROM your_table_name;

The following will retrieve the next 30 characters:

SELECT DBMS_LOB.SUBSTR (your_clob_column_name, 30, 31) FROM your_table_name;

The first parrameter is the column name (lob_loc), the second (numeric) parameter is the length of the string you want to return (amount) and the third (numeric) parameter is the starting position (offset).

Report message to a moderator

Re: Clob to string conversion [message #10335 is a reply to message #10332]

Thu, 15 January 2004 03:19

Adrian Billington
Messages: 139
Registered: December 2002

Senior Member

In 9i you can use standard character functions on CLOB columns, so you can simply use SUBSTR instead of DBMS_LOB's much slower version.

Regards
Adrian

Report message to a moderator

Re: Clob to string conversion [message #10347 is a reply to message #10335]

Thu, 15 January 2004 21:17

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

Senior Member

Thanks, I didn't realize that!

Report message to a moderator

Re: Clob to string conversion [message #10367 is a reply to message #10335]

Sun, 18 January 2004 09:44

Pauline
Messages: 6
Registered: February 2002

Junior Member

Many thanks .

Report message to a moderator

Re: Clob to string conversion [message #10852 is a reply to message #10332]

Fri, 20 February 2004 07:11

Juan Sebastián Jaramillo
Messages: 1
Registered: February 2004

Junior Member

Can I assign the result of this select to a varchar2 variable like this

declare
mi_var varchar2(1000);
begin
SELECT DBMS_LOB.SUBSTR (your_clob_column_name, 30,1)
INTO
mi_var
FROM your_table_name;

That is because i need to write the text of the clob to a file with text_IO package.

thanks in advance

Report message to a moderator

Re: Clob to string conversion [message #10880 is a reply to message #10852]

Sat, 21 February 2004 12:50

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

Senior Member

Sure, you can DBMS_LOB.SUBSTR or, if you have Oracle 9i, then you can just use SUBSTR.

scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> DECLARE
  2    mi_var VARCHAR2(1000);
  3  BEGIN
  4    SELECT DBMS_LOB.SUBSTR (your_clob_column_name, 28, 1)
  5    INTO   mi_var
  6    FROM   your_table_name;
  7    DBMS_OUTPUT.PUT_LINE (mi_var);
  8    SELECT SUBSTR (your_clob_column_name, 29)
  9    INTO   mi_var
 10    FROM   your_table_name;
 11    DBMS_OUTPUT.PUT_LINE (mi_var);
 12  END;
 13  /
This is test data in column
your_clob_column_name of table your_table_name.

PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	change the datatype of a field without empty the existing one.
Next Topic:	Error Numbers

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:37:50 CDT 2024