Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Surrinder .

Asked: March 15, 2001 - 11:11 am UTC

Last updated: August 11, 2022 - 4:43 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Can you give me a solution for converting CLOBS datatype to VARCHAR datatypes, all the documents I refer to talk about converting BLOBS to VARCHAR and when I try and apply the examples to CLOBS, get errors

and Tom said...


dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );


for example:

select dbms_lob.substr( x, 4000, 1 ) from T;

will get me the first 4000 bytes of the clob. Note that when using SQL as I did, the max length is 4000. You can get 32k using plsql:


declare
my_var long;
begin
for x in ( select X from t )
loop
my_var := dbms_lob.substr( x.X, 32000, 1 );
....




Rating

  (82 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Converting CLOBS TO VARCHAR

Surrinder Singh, March 16, 2001 - 7:02 am UTC

Extremely useful all the documentation that refered to LOB conversions in the ORACLE Docs , and that stated that the dbms_lob.substr should be returned into a RWS(32767), not a LONG for CLOBS, thanks once again.

Gururaj, March 16, 2001 - 1:00 pm UTC


Converting CLOBS TO VARCHAR

Naveen, March 16, 2001 - 3:15 pm UTC

As usual, TOM makes it look easy.

Converting CLOBS TO VARCHAR

Naveen, March 16, 2001 - 3:15 pm UTC

As usual, TOM makes it look easy.

How to display long data after <xml> tag in text file via sql*plus?

Teresa Felch, June 05, 2001 - 2:31 pm UTC

Dear Tom,

How does one display a long datatype column variable of a table on the same line after an xml tag? For example, we are using sql*plus to write a file containing xml tags at the beginning of each record via a SELECT statement. I get "inconsistent datatypes" when I try to write out the long variable.

COLUMN newline_tag newline;
COLUMN date_submit newline;
COLUMN mmdd_date_submit newline;
COLUMN narrative_desc format A1000 justify left;

SELECT '<DATE>' || TO_CHAR (synopsis.date_submit, 'MMDD') as mmdd_date_submit,
'<DESC>' || NULL newline_tag, synopsis.narrative_desc,
....
FROM synopsis
...
;


the variables are declared as follows:
date_submit date NOT NULL
mmdd_date_submit date NOT NULL
narrative_desc long NOT NULL <--(18000 char max)

For now, I'm getting
<DESC>
narrative_desc text paragraph.

My goal is to get the long variable written on the same line as the <DESC> tag with NO spaces between the <DESC> and the text data, as this output will serve as a template to XML processing.

Any help you can provide me is greatly appreciated. I just started using sql*plus about a week ago, and I'm having fun with it. I've looked at using the ConText option, but my co-worker thinks there's an easier way of displaying this information then using ConText.

Sincerely,
Teresa Felch
teresf@bellsouth.net

very good

Satya K, June 25, 2001 - 5:06 pm UTC

This means, Oracle cursor can not have any column more than 4000 chars width..

Converting CLOBS TO VARCHAR

Karl Bergerson, March 18, 2002 - 5:31 pm UTC

It worked for me. Quick efficient. Thanks.

dbms_lob.substr problem

A reader, June 10, 2003 - 7:26 am UTC

Hi

How to get around with this . database is 817
We have a table lobsq(col clob).

select dbms_lob.getlength(col) from lobsq
gives me 4245.

my plsql program is likt this

declare
res varchar2(32767);
begin
select dbms_lob.substr(col,dbms_lob.getlength(col),1) into res from lobsq;

dbms_output.put_line(res);
end;

gives me the following error
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at line 10


Tom Kyte
June 10, 2003 - 8:55 am UTC

dbms_output.put_line has a documented limit of 255 characters/line.  you have exceeded that.


create or replace procedure p ( p_str in varchar2 )
is
   l_str   long := p_str;
begin
   loop
      exit when l_str is null;
      dbms_output.put_line( substr( l_str, 1, 250 ) );
      l_str := substr( l_str, 251 );
   end loop;
end;
/

is a little dump routine I use.  Furthermore, SQL is limited to returning 4000 characters from VARCHAR types.  

ops$tkyte@ORA817DEV> create table t ( x clob );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_data long;
  3          l_clob clob;
  4  begin
  5          l_data := rpad( '*', 4245, '*' );
  6          insert into t values ( empty_clob() )
  7          returning x into l_clob;
  8          dbms_lob.writeAppend( l_clob, 4245, l_data );
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select dbms_lob.getLength(x) from t;

DBMS_LOB.GETLENGTH(X)
---------------------
                 4245

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_data varchar2(10000);
  3  begin
  4          select dbms_lob.substr(x,4245,1) into l_data from t;
  5          dbms_output.put_line( 'length = ' || length(l_data) );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
ORA-06512: at line 4


ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_data varchar2(10000);
  3          l_clob clob;
  4  begin
  5          select x into l_clob from t;
  6          l_data := dbms_lob.substr( l_clob, 4245, 1 );
  7          dbms_output.put_line( 'length = ' || length(l_data) );
  8  end;
  9  /
length = 4245

PL/SQL procedure successfully completed.

would be the way to fetch that. 

Very weird behaviour if LONG columns were imported from ACCESS.

Ken Chiu, October 21, 2004 - 4:20 pm UTC

I used ms-Access to import a table into oracle, the table consists of a Access data type of memo, which is mapped to Long during the import.

Now to do a describe on this imported table, I need to surround the tablename with double quotes:
SQL> desc Day1_1A1_1A4;
ERROR:
ORA-04043: object Day1_1A1_1A4 does not exist

SQL> desc "Day1_1A1_1A4";
 Name                                      Null?    Type
 ----------------------------------------- -------- ------
 Field1                                             LONG

That's question #1, why does it need to be double quoted ?

Then when I wanted to convert the column from LONG to CLOB, I need to have the correct casing for the column name..ie.

SQL> Alter Table "Day1_1A1_1A4" MODIFY "field1" CLOB;
Alter Table "Day1_1A1_1A4" MODIFY "field1" CLOB
*
ERROR at line 1:
ORA-00904: "field1": invalid identifier

However, noticed the first letter in the column name is now upper cased :

SQL> Alter Table "Day1_1A1_1A4" MODIFY "Field1" CLOB;

Table altered.

That's question#2, what caused this case sensitvity.  I know this probably have to do with the importing from Access, but please advise from an Oracle's perspective what's happening.  Thanks Tom !

Ken.


 

Tom Kyte
October 22, 2004 - 3:14 pm UTC

because ACCESS created the table with mixed case -- ACCESS created the table using double quotes. Access is case sensitve, we are not -- unless you force the issue with quotes.

rename the table from "lowercase name" to uppercase_name

in Access, you created the table with that case of name - they "kept" it when it moved to Oracle. You need to use their exact case.



converting LONG to Varchar2

Frank, February 09, 2005 - 6:52 pm UTC

Hi Tom,

I am connected to:
Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.0.0 - Production

I have 2 tables, one which has survey info and other which has text for questions (which is a LONG field). I want to unload the data by joining the 2 tables but always get this error:

select question_id||','||q1.text
from survey s, questions_text q1
where s.question_id = q1.qkey(+)
/

select question_id||','||q1.text
*
ERROR at line 1:
ORA-00932: inconsistent datatypes

The TEXT column in questions_text table is of LONG datatype. How can I get this data unloaded? Please help.

Thanks.

Tom Kyte
February 10, 2005 - 12:55 am UTC

set colsep ','
select question_id, text from ......


would be one approach.

A reader, February 15, 2005 - 1:49 pm UTC

Thanks for your answer Tom but here is the next question.

The data entered in the field is all formatted, with carriage returns. How can I get rid of that? Because this CSV file is going to be loaded into other database using some loading tool. Hence I want the data in one continous string.

Thanks in advance.

Tom Kyte
February 15, 2005 - 3:46 pm UTC

a csv file with no newlines?

what other database (i've worked with lots of them) wants that???????




A reader, February 15, 2005 - 10:07 pm UTC

I mean, there are other fields alongwith this long field. This long field has data entered into it with carriage returns. Of course, every row will have the carriage return. I just want to get rid of the carriage returns in the long field.

Sorry for the confusion.

Tom Kyte
February 16, 2005 - 7:43 am UTC

you'll be writing a program then to create this custom formatted datafile. You'll find sqlplus to be "not the tool you want to use", it is a very generic command line reporting tool.

what is this other database? In Oracle we would just unload the data

"hello","world","This has
line breaks
in it","5"|
"hello","world","This has
line breaks
in it","5"|


and use an end of record terminator of "|\n" upon reload. no data loss (you'll lose CR's)




Using function or PL/SQL for above

A reader, February 23, 2005 - 4:24 pm UTC

Tom,

With continuation to the above problem, I wrote a function to convert the long to string (with the help of one of your functions). Using the substr function, is there any limit to return the string?

create or replace function long2str (p_rowid in rowid) return varchar2 as
l_str long;
begin
select text into l_str from questions_text where rowid = p_rowid;
return substr(l_str, 1, 32000);
end;
/

Now I pass the rowid for each row and get the data. Is this the correct way or some other better method?

Thanks.

Tom Kyte
February 24, 2005 - 5:28 am UTC

4000 characters if called from SQL

32k if called from PLSQL.

rowid will be the fastest path back to the long, you could use primary key but that would be an index range scan+table access by index rowid, rowid will just be "table access by rowid"

A reader, February 24, 2005 - 10:09 am UTC

Thanks a lot Tom!

But I get the below error:

ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

Now if I change it as below I do not get any errors.

return substr(ltrim(rtrim(l_str)), 1, 2000);

The query that I am running is:

select long2str(rowid)
from questions_text
where question_id < 100;

Now I fear by reducing from 32k to 4000 will I lose any data from the fields.

Tom Kyte
February 24, 2005 - 4:52 pm UTC

yes you will lose - but you cannot get over 4,000 bytes in SQL, it is just the way SQL is -- longs are longs, sql is sql, the limit of varchar2 is 4,000 bytes.

A reader, February 24, 2005 - 12:45 pm UTC

Is it also possible to find the length of the long variable?

Thanks for all the help.

clob in pl/sql

A reader, July 28, 2006 - 3:19 am UTC

I have a table with this structure

CREATE TABLE SQLTEXT_FULL
(
SQL_TEXT CLOB,
QID NUMBER
)

I then tried to get all execution plan using this pl/sql

begin
for i in (select * from SQLTEXT_FULL where rownum < 2)
loop
execute immediate 'explain plan set statement_id =''' || i.qid || ''' for ' || i.sql_text;
end loop;
end;
/

but I get this error:

in 9.2.0.6
-----------
ERROR at line 4:
ORA-06550: line 4, column 36:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 12:
PL/SQL: Statement ignored

in 8.1.7.4:
-------------
ERROR at line 4:
ORA-06550: line 4, column 36:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 4, column 12:
PL/SQL: Statement ignored

How can I obvercome this?

Tom Kyte
July 28, 2006 - 7:57 pm UTC

why would you not use the real plans - which are stored in v$sql_plan

You'll literally KILL your system by running an explain plan on every query.

That, and you'll not get the real plans (explain plan cannot bind variable peek, explain plan cannot tell the bind datatype, explain plan will get the wrong answer)

and in 8i, just skip it, since v$sql_plan doesn't exist. You'll not be getting the right information.

clob in pl/sql

A reader, July 29, 2006 - 12:03 pm UTC

Hi

We are using RULE optimizer so I dont mind get the explain plan. Also i am not running on production, I a running in a replicated environment (using split mirror technology) which are used for these purposes.

Your comments is interesting however, we are migrating from 8i to 9i and we want to check all execution plan changes. If in 8i Explain plan is not valid then how could we accomplish our goal?

Our queries use to_date accordingly so I guess the "explain plan cannot tell the bind datatype" does not apply?


Tom Kyte
July 29, 2006 - 4:27 pm UTC

you never bind a number?

clob in pl/sql

A reader, July 30, 2006 - 5:16 am UTC

Hi

We do bind numbers, if explain plan has such limitations what is it used for....???

Tom Kyte
July 30, 2006 - 8:45 am UTC

You have to use it cautiously as it does not understand anything about the bind datatype (always assumes varchar2) AND it never bind peeks.


ops$tkyte%ORA10GR2> create table t ( x varchar2(20) primary key, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select /*+ rule */ * from t where x = :x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3826520415

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0012505 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=:X)

Note
-----
   - rule based optimizer used (consider using cbo)


because the explain plan said ":x is a varchar2", it thinks the index is OK to use, however, tkprof shows reality:

select /*+ rule */ *
from
 t where x = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 175

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=60 us)


The index could not be used. 

Su Baba, February 13, 2007 - 2:50 pm UTC

How do I convert a VARCHAR2 column to a CLOB?
Tom Kyte
February 13, 2007 - 4:21 pm UTC

ops$tkyte%ORA9IR2> create table t ( x varchar2(30) );

Table created.

ops$tkyte%ORA9IR2> insert into t values ( 'hello world' );

1 row created.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2> alter table t add y clob;

Table altered.

ops$tkyte%ORA9IR2> update t set y = x, x = null;

1 row updated.

ops$tkyte%ORA9IR2> alter table t set unused column x;

Table altered.

ops$tkyte%ORA9IR2> alter table t rename column y to x;

Table altered.

ops$tkyte%ORA9IR2> desc t;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 CLOB

Is this a bug?

Su Baba, February 13, 2007 - 6:53 pm UTC

I tried to do the above suggestion using a PL/SQL block, but was not able to do it. Anyway to get around this problem?

SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

5 rows selected.

SQL> 
SQL> CREATE TABLE x (
  2     col1 VARCHAR2(1000)
  3  );

Table created.

SQL> 
SQL> 
SQL> BEGIN
  2     FOR x IN (SELECT COUNT(*) cnt
  3               FROM   user_tab_columns
  4               WHERE  table_name  = 'X' AND
  5                      column_name = 'COL1' AND
  6                      data_type   = 'CLOB')
  7     LOOP
  8        IF (x.cnt = 0) THEN
  9           EXECUTE IMMEDIATE 'ALTER TABLE X ADD col2 CLOB';
 10  
 11           UPDATE x
 12           SET    col2 = Col1;
 13  
 14           EXECUTE IMMEDIATE 'ALTER TABLE x DROP COLUMN col1';
 15           EXECUTE IMMEDIATE 'ALTER TABLE x RENAME COLUMN col2 TO col1';
 16        END IF;
 17     END LOOP;
 18  END;
 19  /
         SET    col2 = Col1;
                *
ERROR at line 12:
ORA-06550: line 12, column 17:
PL/SQL: ORA-00904: "COL2": invalid identifier
ORA-06550: line 11, column 10:
PL/SQL: SQL Statement ignored


SQL> 
SQL> desc x
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------
 COL1                                                                             VARCHAR2(1000)

SQL> 
SQL> 
SQL> -- -----------------------------------------------------
SQL> -- Same code but comment out a big portion of it
SQL> -- -----------------------------------------------------
SQL> BEGIN
  2     FOR x IN (SELECT COUNT(*) cnt
  3               FROM   user_tab_columns
  4               WHERE  table_name  = 'X' AND
  5                      column_name = 'COL1' AND
  6                      data_type   = 'CLOB')
  7     LOOP
  8        IF (x.cnt = 0) THEN
  9           EXECUTE IMMEDIATE 'ALTER TABLE X ADD col2 CLOB';
 10  /*
 11           UPDATE x
 12           SET    col2 = Col1;
 13  
 14           EXECUTE IMMEDIATE 'ALTER TABLE x DROP COLUMN col1';
 15           EXECUTE IMMEDIATE 'ALTER TABLE x RENAME COLUMN col2 TO col1';
 16   */
 17        END IF;
 18     END LOOP;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> desc x
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- --------------
 COL1                                                                             VARCHAR2(1000)
 COL2                                                                             CLOB

Tom Kyte
February 14, 2007 - 8:22 am UTC

col2 doesn't exist when the block is compiled.

you would have to perform the update using dynamic sql as well.

how to convert clob filed to varchar2 filed

Amao, February 28, 2007 - 9:43 pm UTC

I have one procedure whose function is to generate dynamic statemetn for materialized view.

for example:
execute immediate l_sqlstmt; l_sqlstmt is clob field whose lenght is almost 72k.
As you know, Varchar2 variable only store 32k data. So I can not define l_sqlstmt as varchar2.

I run above statement with below errors:
detail :
ORA-06502: PL/SQL: numeric or value error
Could you tell me how to convert this clob variable to varchar2 variable, and run this statement successfully?

Tom Kyte
February 28, 2007 - 10:09 pm UTC

right now, execute immediate is 32k - period.

you'll need to use dbms_sql.

dbms_sql can take a plsql table type in the parse call - and that can be larger than 32k.

michaels, March 01, 2007 - 5:28 pm UTC

> right now, execute immediate is 32k - period.

hmm ..what about this:

SQL> DECLARE
   stmt1   LONG;
   stmt2   LONG;
BEGIN
   stmt1 := 'BEGIN null; ' || LPAD (' ', 32000, ' ');
   stmt2 := RPAD (' ', 32000, ' ') || ' END;';
   DBMS_OUTPUT.put_line ('Statement length: ' || LENGTH (stmt1 || stmt2));

   EXECUTE IMMEDIATE stmt1 || stmt2;
END;
/
Statement length: 64017
PL/SQL procedure successfully completed.

Tom Kyte
March 04, 2007 - 12:35 pm UTC

it will in general fail - don't go over 32k, it'll just not work at the worst possible times.

execute immediate is 32k - period.

dbms_lob.substr returns different length in two oracle instances

htu, April 04, 2008 - 10:39 am UTC


BEGIN
n := dbms_lob.getlength(s);
m := LENGTH(DBMS_LOB.SUBSTR(s));
IF n < 32767 THEN
EXECUTE IMMEDIATE DBMS_LOB.SUBSTR(s);
ELSIF n < 64000 THEN
EXECUTE IMMEDIATE DBMS_LOB.SUBSTR(s, 32000, 1)||DBMS_LOB.SUBSTR(s, n-32000, 32001);
ELSE
dbms_output.put_line('ERR('||v_prg||'): skipped');
END IF;
END;
/

The m returns 8191 even n > 10000 in 10.2.0.3 Oracle instance on Solaris 10. It returns correct length in a instance on Window 2003. Why? How to correct it?

Tom Kyte
April 04, 2008 - 12:34 pm UTC

why the heck would you even do that.

Let me say this simply:

prior to 11g, execution of SQL dynamically - using native dynamic sql - that is greater then 32k is NOT SUPPORTED - does not work. If you've gotten it to work by accident here and there - DO NOT RELY on it. It is explicitly "not a feature"

You have to use DBMS_SQL and the parse interface that takes a plsql table - in 10gr2 and before THAT IS THE ONLY way - the ONLY way - to execute dynamic sql in plsql larger than 32k.

So, not even going to address the original request - you just cannot do what you are doing - period.

dbms_lob.substr returns different length in two oracle instances

htu, April 04, 2008 - 10:42 am UTC

Here is the procedure header:

CREATE OR REPLACE PROCEDURE exec_now (
s CLOB, -- SQL statements
p_lvl INTEGER DEFAULT 0 -- message level
) IS
v_prg VARCHAR2(100) := 'exec_now';
n NUMBER;
m NUMBER;
msg VARCHAR2(200);

dbms_lob.substr returns different length in two oracle instances

htu, April 04, 2008 - 1:05 pm UTC


DECLARE
s CLOB;
s1 LONG;
s2 LONG;
n NUMBER;
BEGIN
dbms_lob.createtemporary(s, TRUE);
dbms_lob.open(s, dbms_lob.lob_readwrite);
s1 := 'BEGIN null; ' || LPAD (' ', 32000, ' ');
n := length(s1||' END;'); dbms_lob.writeappend(s, n, s1||' END;');
exec_now(s,5);
dbms_lob.close(s);

dbms_lob.createtemporary(s, TRUE);
dbms_lob.open(s, dbms_lob.lob_readwrite);
-- n := dbms_lob.getlength(s); dbms_lob.erase(s, n, 1);
n := length(s1); dbms_lob.writeappend(s, n, s1);
s2 := RPAD (' ', 12000, ' ') || ' END;';
n := length(s2); dbms_lob.writeappend(s, n, s2);
exec_now(s,5);
dbms_lob.close(s);

END;
/

dbms_lob.substr returns different length in two oracle instances

htu, April 04, 2008 - 1:08 pm UTC

I used the above test code, and here are the different outputs:

in instance on Window 2003 server:
----------------------------------
INFO(exec_now): 32017 characters.
INFO(exec_now): executing codes (1)...
INFO(exec_now): 32017/32017 characters.
INFO(exec_now): Codes in was executed.
INFO(exec_now): 44017 characters.
INFO(exec_now): executing codes (2)...
INFO(exec_now): Codes was executed.

PL/SQL procedure successfully completed.

In instance on Solaris server
-------------------------------
INFO(exec_now): 32017 characters.
INFO(exec_now): executing codes (1)...
INFO(exec_now): 8191/32017 characters.
ERR(exec_now): ORA-06550: line 1, column 8191:
PLS-00103: Encountered the symbol
"end-of-file" when expecting one of the following:

What parameter sets the limit on what dbms_lob.substr returns?





Tom Kyte
April 04, 2008 - 1:16 pm UTC

it is supposed to default to 32k but I don't really care

because all of your code must be erased. did you not read what I wrote. You cannot - CANNOT - use native dynamic sql with strings exceeding 32k in 10gr2 and before

you HAVE to use dbms_sql.

so, just stop this approach, you cannot use it.

dbms_lob.substr returns different length in two oracle instances

htu, April 04, 2008 - 2:32 pm UTC

Sorry, I did not make it clear. I just want to test if it really works if it exceeds 32k. My question is why it only returns 8191 characters in Solaris 10 Oracle 10gR2? Is there a parameter setting the limit? I compare the PL/SQL setting in both instances. They are the same.
Tom Kyte
April 04, 2008 - 10:14 pm UTC

nope, I don't believe you - you posted code that clearly - CLEARLY - shows what you are doing.

using dbms_lob.substr() the way you are is just silly, I don't get it, just call dbms_lob.get_length and be done with it, calling dbms_lob.substr in this case is not useful at all.

I'm not even going to bother looking at this - you are doing something that just isn't going to work - so stop it.

DBMS_LOB

A reader, April 05, 2008 - 4:46 pm UTC


dbms_lob.substr returns different length in two oracle instances

htu, April 07, 2008 - 10:57 am UTC

I was just following michael's example to test Oracle's limit on 'execute immediate'. Curiosity leads to discovery.

This is fine if you do not want to look deeper into this. But I still want to report my finding: dbms_lob.substr returns 8k in all my Oracle instances on Solaris servers while it returns the default 32k on all Oracle instances on Window servers. The Oracle versions I tested are 9.2.0.1, 9.2.0.8, and 10.2.0.3. Here is the code:

DECLARE
s CLOB;
s1 LONG;
s2 LONG;
n NUMBER;
m NUMBER;
BEGIN
dbms_lob.createtemporary(s, TRUE);
dbms_lob.open(s, dbms_lob.lob_readwrite);
s1 := 'BEGIN null; ' || LPAD (' ', 32000, ' ')||' END;';
n := length(s1); dbms_lob.writeappend(s, n, s1);
m := LENGTH(DBMS_LOB.SUBSTR(s));
dbms_output.put_line('N='||TO_CHAR(n)||','||'M='||TO_CHAR(m));
dbms_lob.close(s);
END;
/

Just wonder why and how?

Tom Kyte
April 08, 2008 - 11:28 am UTC

the default is supposed to be 32k, it would be a 'bug' if it returns 8k. Please utilize support (although I personally think the default should just be removed, it doesn't make sense to have a default substr length)

Milo van der Leij, April 24, 2008 - 12:24 pm UTC

dbms_lob.substr, according to the 11g documentation, returns at most a substring of length (32767/n), where n is the (maximum) byte-width for the character set.

dbms_lob.substr is returning at most 8191 (8k - 1) characters for me since I'm using AL32UTF8, which has a max byte-width of 4 bytes per character, and 32767/4 = 8191.75.

Similar restrictions apparently exist when converting (both implicitly and explicitly) CLOBs to VARCHAR2s. The following fails unless the number is reduced to 8191, even when s is defined as VARCHAR2(32767 BYTE):
-- Raises ORA-06502: PL/SQL: numeric or value error
s := to_char(to_clob(rpad('x',32767,'x')))
Why all this is, I do not know. At least the dbms_lob.substr thing is documented.
Tom Kyte
April 28, 2008 - 12:13 pm UTC

ahh, i did not even think about that - the characterset, of course, that makes total sense (the os was a red herring entirely)

A reader, June 17, 2008 - 4:46 pm UTC


CLOB to VARCHAR over DBLink ???

Jack, September 22, 2008 - 2:47 pm UTC

can you do the following:

select dbms_lob.substr(l_clob, 4000, l_var)
from t@<dblinkname>

is the substring function valid over a database link to a remote CLOB field ?
Tom Kyte
September 23, 2008 - 1:02 pm UTC

you would typically want to use the remote dbms_lob package - select dbms_lob.substr@remote( ... )

CLOB select over db link using DBMS_LOB pkg

Jack, October 10, 2008 - 12:15 pm UTC

created dblink dblink_qarememdy to remote database
Tried to select substr of Notes field which stored as CLOB in remote database.

SQL> /
select dbms_lob.substr@dblink_qaremedy(notes,1,50) from sprt_service_request@dblink_qaremedy
                                       *
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

Tom Kyte
October 13, 2008 - 2:43 am UTC

confirmed, that used to work - not sure if that is a bug or not, I'll file one to see.

In the meanwhile if you create a view on the remote site - that does definitely work.

DBMS_SQL cursor to Dynamic cursor in 10g ?

Kiran, October 14, 2008 - 10:24 pm UTC

Hi Tom,

Since we are discussing about CLOB in this page.. i got a question. I have learnt from this page that dbms_sql is better to dynamically execute CLOB fields and i want to know if there is a way to return dynamic cursor instead of dbms_sql cursor in oracle 10gR2. I see that in Oracle 11g we have to_refcursor function .. but i dont see any similar function in Oracle10gR2 ... is there a work around ?
Tom Kyte
October 15, 2008 - 5:16 pm UTC

there is not.

CLOB in WHERE

KK, October 16, 2008 - 7:30 am UTC

Hi Tom,

Thanks for the reply.I have a requirement ..where a CLOB is passed as input to stored proc. I need to use this parameter in where condition and return a refcursor/dynamic cursor/pl/sql cursor to the calling application.

I do not know if there is any other way to do it other than using dbms_sql.And if i use dbms_sql i cannot return a dynamic cursor.And i cannot use EXEC IMMEDIATE as CLOB parameter is more than 32k size.

I tried using GLOBAL TEMPORARY TABLE as a temporary table to store the resultant data and then use OPEN FOR on the GTT, but since we are using coonection pooling .. this doesn't work either. Any ideas ?

We are using Oracle10gR2 and application is .Net .
Tom Kyte
October 17, 2008 - 8:50 pm UTC

if the clob plus the rest of the query exceeds 32k, you will not be doing this in 10g and before using plsql.

connection pooling should not affect the use of a global temporary table at all here - you would be processing the entire result in a single connection pool grab - not across pages (if a ref cursor could work - BY DEFINITION a global temporary table can work - they are both 'private' to a connection)

for CLOB using GTT in Connection Pooling

K Kiran, October 20, 2008 - 11:50 am UTC

Hi Tom,

Referring to your suggestion :

Followup August 9, 2006 - 4pm US/Eastern:

but, in a connection pool environment - perhaps using session based gtt's would be an all around
bad idea from the very beginning.

----------------------
So if i am using session based GTT's to generate a dynamic cursor in a .Net connection pooling environment .. then will it not cause a problem? In your review to previous question you said GTT's are independent and should not cause a problem in connection pooling.

Can you justify or clear my confusion ?
Tom Kyte
October 21, 2008 - 12:08 pm UTC

session based - not transaction based.

Transaction based should be 100% safe in a connection pool environment.

Session based would be bad as this would happen a lot:

a) I grab a connection
b) I populate gtt
c) I give connection back
d) You grab connection and get the one I got in a)

You now have to deal with my mess, the junk I left.



That said, if you use a global temporary table (transaction based, commit or rollback and poof - data disappears) it is connection pool 'safe' since you would NEVER give a connection back without resolving the transaction that is in place. You would finish your transaction.


So, if you can use a cursor that is returned to the client - you can in fact use a global temporary table. The client would exhaust the cursor, the client would exhaust the global temporary table.

GTT Session based

K Kiran, October 22, 2008 - 9:11 am UTC

Hi Tom,

I realized that i didnot mention you.. am using session based GTT not Transaction based GTT to return back cursor to the application ... and now this might cause a problem as you explained before .. if i issue a truncate statement each time before i populate the table .. will that cause any issues ?

Truncate in a session based GTT will erase data created in that session . Am i right ? Will there be any leftover since its in a connection pooling? Do you anticipate any problems here ?

Excellent solution

Chris, October 31, 2008 - 8:14 am UTC

Just to say thanks - I had had trouble with CLOBs before, but this makes everything so simple.

Converting CLOB to Varchar

Janet A, March 31, 2009 - 11:16 am UTC

Thank you so much! It was so simple yet working

How to insert Varchar2 into CLOB

Samy, April 02, 2009 - 9:12 am UTC

Create table ClobTab
(
RwId Number,
ClobCol Clob
);

Create or replace Procedure InsertVarchar2CLob
as
vString1 Varchar2(32767) ;
vString2 Varchar2(32767) ;
Begin
VString1 := Lpad('A',32767,'A');
VString2 := Lpad('B',32767,'B');
insert into ClobTab(RwId ,ClobCol ) Values (1,vString1||'~~'||vString2||'~~');

End;


ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at "INSERTVARCHAR2CLOB", line 8
ORA-06512: at line 1

Tom Kyte
April 02, 2009 - 10:39 am UTC

the longest string you can have is 32k. You are building a string via concatenation that is much larger than that.

ops$tkyte%ORA10GR2> Create or replace Procedure InsertVarchar2CLob
  2  as
  3      vString1 Varchar2(32767) ;
  4      vString2 Varchar2(32767) ;
  5      l_clob   clob;
  6  Begin
  7      VString1 := Lpad('A',32767,'A');
  8      VString2 := Lpad('B',32767,'B');
  9      insert into ClobTab(RwId ,ClobCol ) Values
 10      (1, empty_clob() ) returning clobCol into l_clob;
 11
 12      dbms_lob.writeAppend( l_clob, length(vString1), vString1 );
 13      dbms_lob.writeAppend( l_clob, length('~~'), '~~' );
 14      dbms_lob.writeAppend( l_clob, length(vString2), vString2 );
 15      dbms_lob.writeAppend( l_clob, length('~~'), '~~' );
 16
 17      l_clob := vString1;
 18      l_clob := l_clob ||'xx';
 19      l_clob := l_clob ||vString2;
 20      l_clob := l_clob ||'xx';
 21      insert into clobTab( rwid, clobcol ) values ( 2, l_clob );
 22
 23      dbms_lob.createTemporary( l_clob, TRUE );
 24      dbms_lob.writeAppend( l_clob, length(vString1), vString1 );
 25      dbms_lob.writeAppend( l_clob, length('yy'), 'yy' );
 26      dbms_lob.writeAppend( l_clob, length(vString2), vString2 );
 27      dbms_lob.writeAppend( l_clob, length('yy'), 'yy' );
 28      insert into clobTab( rwid, clobcol ) values ( 3, l_clob );
 29      dbms_lob.freeTemporary(l_clob);
 30
 31
 32      l_clob := to_clob(vString1) || to_clob('$$') || to_clob(vString2) || to_clob('$$');
 33      insert into clobTab( rwid, clobcol ) values ( 4, l_clob );
 34  End;
 35  /

Procedure created.

ops$tkyte%ORA10GR2> exec InsertVarchar2CLob;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rwid, dbms_lob.getlength( clobcol ), dbms_lob.substr( clobcol, 10, 32765 ) x from clobtab;

      RWID DBMS_LOB.GETLENGTH(CLOBCOL) X
---------- --------------------------- --------------------
         1                       65538 AAA~~BBBBB
         2                       65538 AAAxxBBBBB
         3                       65538 AAAyyBBBBB
         4                       65538 AAA$$BBBBB



USE CLOB for output from a stored procedure.

Abhijit, August 05, 2009 - 5:06 pm UTC

Hi Tom,

This is a concise explantion of what I want to do.
However, instead of saving the CLOB to a table, I wish to use it as an output from a stored procedure.
This is what I have written so far.

create or replace procedure proc_getClob(o_clob out clob)
IS
    --l_data long;
    l_clob clob;
    begin
      for c1 in (select lease_no, lease_line_seq, comments 
                from lease_comments  where lease_no like '52952%') 
      loop 
            dbms_lob.writeAppend(l_clob, length(c1.comments), c1.comments);
      end loop;                  
            o_clob := l_clob;
 end;

comments is a long column in lease_comments table.
Here's the structure for lease_comments.

create table lease_comments(lease_no varchar2(6), lease_line_seq varchar2(3), comments long);


My objective is to get a clob output and then use this clob output by the stored proc in a java program.
What am I missing?

Thanks,
Abhijit
Tom Kyte
August 05, 2009 - 5:16 pm UTC

ops$tkyte%ORA10GR2> create or replace procedure proc_getClob(o_clob out clob)
  2  IS
  3  begin
  4        dbms_lob.createTemporary( o_clob, true );
  5        for c1 in (select username comments from all_users)
  6        loop
  7              dbms_lob.writeAppend(o_clob, length(c1.comments), c1.comments);
  8        end loop;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x clob
ops$tkyte%ORA10GR2> exec proc_getclob( :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

X
--------------------------------------------------------------------------------
BIG_TABLEDIPTSMSYSLOTTOUSERMDDATAFOO$TKYTEQUOTARRT_TESTTESTHRAUSER_1OPS$TKYTEBCO
RACLE_OCMTESTCASE1TESTCASE2OPS$ORA10GR2SCOTTIMDMSYSDBSNMPWMSYSEXFSYSCTXSYSXDBANO
NYMOUSOLAPSYSORDSYSORDPLUGINSSI_INFORMTN_SCHEMAMDSYSSYSMANPERFSTATSYSSYSTEMOUTLN
MGMT_VIEWMY_USER



don't forget to freetemporary the clob from java else you'll have lots of dangling clobs out there until your session ends.

Excellent

Abhijit, August 06, 2009 - 8:47 am UTC

Thanks for the tip, Tom.

Abhijit, August 06, 2009 - 3:12 pm UTC

I am getting an error in the code I posted earlier. My guess is one of the rows has more than 40000 characters in the comments column.
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 789
ORA-06512: at "SPACE.PROC_GETCLOB", line 9
ORA-06512: at line 1

How do I handle this situation?

Thanks,
Abhijit
Tom Kyte
August 06, 2009 - 3:21 pm UTC

why are you not using my code

If comments is a varchar2, then it does not have more than 4000 characters.

Abhijit, August 06, 2009 - 3:56 pm UTC

Hi Tom,
I am using your code. The only part I have changed is the query in the code. The code works, except when it hits a row that has a more than certain number of characters.
Comments is a long column and not a varchar2. I was thinking, if writing some kind of loop to go after the contents of the comments column, before I append it to the CLOB might work.

Thanks,
Abhijit
Tom Kyte
August 06, 2009 - 4:32 pm UTC

more likely - you hit a null.

I made the comment about the code because you said "your code" and you show an error on line 9, my code didn't have a line 9.

ops$tkyte%ORA10GR1> create or replace procedure proc_getClob(o_clob out clob)
  2  IS
  3  begin
  4        dbms_lob.createTemporary( o_clob, true );
  5        for c1 in (select username comments from all_users)
  6        loop
  7                dbms_output.put_line( 'processing ' || c1.comments );
  8              if (c1.comments = USER )
  9              then
 10                  c1.comments := null;
 11              end if;
 12              dbms_lob.writeAppend(o_clob, length(c1.comments), c1.comments);
 13        end loop;
 14  end;
 15  /

Procedure created.

ops$tkyte%ORA10GR1> variable x clob
ops$tkyte%ORA10GR1> exec proc_getclob(:x);
processing LOTTOUSER
processing OPS$TKYTE
BEGIN proc_getclob(:x); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at "OPS$TKYTE.PROC_GETCLOB", line 12
ORA-06512: at line 1




so, deal with nulls if you have nulls - the long will silently be truncated to 32k at most by plsql so that is not it.

questions

saty, December 17, 2009 - 2:20 pm UTC

We are extracting the data from legacy systems. some times the database values are embedding with some funky charcters

examples "�Pete-077�" .

I am getting <ORA-12899: value too large for column> error and our jobs are failing . How do we replace these special charcters
Tom Kyte
December 17, 2009 - 2:31 pm UTC

you might start by backing up and asking yourself "how did they get there in the first place", your applications put them in there - are you not concerned about ongoing 'bad data'????


As for 'how to replace these bad characters', imagine for a moment you were not using Oracle - in fact not using a database of any sort. Use the same technique you would for that.

For you see, there is no such thing as a 'bad character', there is data that does not fit into your characterset - and well - I don't know what we'd do with it for you. To us, there are NO bad characters.

CLOB

A reader, December 17, 2009 - 3:21 pm UTC


Exactly what i searched for

Andreas Meyer, March 15, 2010 - 7:53 am UTC

Thank you!

John, August 13, 2010 - 4:08 pm UTC

I am trying to use dbms_lob.substr to copy data from a clob to a varchar2(4000 char), but I am getting "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

I have a table with a column defined as varchar2(4000 char), let's say target_table.description.
I have a cursor that selects a clob.
I LOOP over the cursor and FETCH the CLOB into a CLOB variable and then

INSERT INTO target_table (id, acolumn, description )
VALUES ( target_seq.nextval, v_value, DBMS_LOB.SUBSTR(v_clob,4000,1));

If I try the same thing with a regular SUBSTR(v_clob,1,4000), I get "ORA-01461: can bind a LONG value only for insert into a LONG column"

How can 4000 chars of a CLOB not fit into a VARCHAR2(4000 CHAR)?

I am using Oracle 10.2.0.4.0

Thanks!
Tom Kyte
August 19, 2010 - 12:41 am UTC

what is your character set?

and - you must have an example - can you provide it - it should be one row of data only to reproduce with.

CLOB to Varchar

Rahul, December 01, 2010 - 3:40 am UTC

superb method Tom.....

Problem while selecting

Achilles, February 16, 2011 - 3:14 pm UTC


I get the exception
ORA-01465: invalid hex number


I am executing the following Stmt

selectutl_raw.cast_to_varchar2( dbms_lob.substr( MSG_TXT, 32000, 1 ) ),MSG_EXPRT_ID from     
  K_OF01.K_OFT_PRC_SUBPRC_MSG

Tom Kyte
February 16, 2011 - 3:28 pm UTC

couple of things...

o varchar2 will be limited to 4000 bytes in SQL - 32000 - not going to happen.

o dbms_lob.substr( CLOB ) returns a varchar2 - I'm assuming MSG_TEXT is a CLOB here (you don't say). Now cast_to_varchar2 is expecting a RAW type (binary). If a function expects a raw but gets a string - the implicit conversion will assume that the string is hex data (two characters of ascii hex data per character of binary data. So, your MSG_TEXT must not contain hex.


ops$tkyte%ORA11GR2> select utl_raw.cast_to_varchar2( '3A' ) from dual
  2  /

UTL_RAW.CAST_TO_VARCHAR2('3A')
-------------------------------------------------------------------------------
:

ops$tkyte%ORA11GR2> select utl_raw.cast_to_varchar2( '3B' ) from dual;

UTL_RAW.CAST_TO_VARCHAR2('3B')
-------------------------------------------------------------------------------
;

ops$tkyte%ORA11GR2> select utl_raw.cast_to_varchar2( '3G' ) from dual;
select utl_raw.cast_to_varchar2( '3G' ) from dual
                                  *
ERROR at line 1:
ORA-01465: invalid hex number



when the string is valid HEX codes - all is good, when not - your error.

You are the best, great explanation

achilles, February 24, 2011 - 6:53 pm UTC

You are the best, great explanation

Problem while inserting to CLOB

achilles, February 24, 2011 - 7:03 pm UTC

I am trying to insert a string into CLOB field using
insert into K_OF01.K_OFT_PRC_SUBPRC_MSG (MSG_TXT)
values
(
'testtest')
)

It is working if I insert upto 4000 characters.
When I go beyond 4000 characters I get ,
SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.

I have searched it over web and tried few ways myself and failed.
Following is the modified SQL statement I am executing

insert into K_OF01.K_OFT_PRC_SUBPRC_MSG (MSG_TXT)
values
(
dbms_lob.write (MSG_TXT,32707,2147483647, 'testtest')

)

I get the exception : ORA-00904: "DBMS_LOB"."WRITE": invalid identifier


Tom Kyte
February 24, 2011 - 7:16 pm UTC

you NEED to, you MUST, you HAVE TO - use bind variables. Stop building unique inserts right here, right now and promise to never even consider doing that again.

tell me what language you are programing in and I'll gladly try to find an example for you. Short of that:

http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e18294/toc.htm

achilles, February 24, 2011 - 7:45 pm UTC

I promise that I never build the unique inserts.

I am using TIBCO to read the messages from a Queue and insert to the DB
Tom Kyte
February 24, 2011 - 7:50 pm UTC

yes, you are

or you would not be hitting the issue, it would happen at 32k with a bind

you gave us this example:

I am trying to insert a string into CLOB field using 
insert into K_OF01.K_OFT_PRC_SUBPRC_MSG (MSG_TXT)
  values 
  (
   'testtest')
    )


there are no binds in there at all. You are not binding.

what PROGRAMMING language are you using, tibco is not a language.

achilles, February 24, 2011 - 8:03 pm UTC

you are right , I am not using binding variables till now.
I promised that I will not even consider using unique inserts from now.

I am not using any programming language. I am using an activity in TIBCO tool to read messages and then to insert.

Can you show me the way to insert to the CLOB using bind variables. I can call this from my TIBCO tool.
Tom Kyte
February 25, 2011 - 8:30 am UTC

"an activity in TIBCO" is your language then - there has to be a programming language in there somewhere. And that is one that I'm not familiar with at all. You'll want to get in touch with TIBCO and ask them how to use their tool correctly - sorry, I just don't have any experience with it.

all i can say is that is DOES support the use of binding - it has too. Tibco is a tool used to attempt to achieve large scale message processing and they would not be large scale if they didn't support the use of bind variables

clobs

A reader, February 25, 2011 - 7:39 am UTC


dbms_lob.substr with minus

Anirban, June 27, 2011 - 7:30 am UTC

Hi Tom,

The following query is throwing error while doing minus with dbms_lob.substr although 4000 characters are being selected. Please advice.

SQL> select count(1) from llums_orbit_feed;
724754

SQL> select count(1) from llums_orbit_feed_prv;
0

SQL> SELECT asset_id,dbms_lob.substr(billing_inventory,4000,1),dbms_lob.substr(billing_inventory,8000,4001) 
FROM llums_orbit_feed
minus 
select asset_id, dbms_lob.substr(billing_inventory,4000,1) ,dbms_lob.substr(billing_inventory,8000,4001)
from llums_orbit_feed_prv;

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





Tom Kyte
June 27, 2011 - 11:27 am UTC

do you have a multi-byte characterset or something? version, all environment information would be useful:

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t ( x int, y clob );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_data long := rpad( 'x', 9000, 'x' );
  3  begin
  4          insert into t values ( 1, l_data );
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select x, dbms_lob.substr( y, 4000, 1 ), dbms_lob.substr( y, 4000, 4001 ) from t
  2  minus
  3  select x, dbms_lob.substr( y, 4000, 1 ), dbms_lob.substr( y, 4000, 4001 ) from t;

no rows selected

more details on the previous query

Anirban, June 27, 2011 - 7:59 am UTC

Tom,
The error is happening for only one row having > 8000 bytes ...

select dbms_lob.getlength(billing_inventory) from llums_orbit_feed
where dbms_lob.getlength(billing_inventory) > 8000;
8192
Tom Kyte
June 27, 2011 - 11:29 am UTC

oh, I see, you used 8000 as the length

dbms_lob.substr( STR, for_#_of_characters, starting_at_character)

your second parameter can be only 4000, you have 8000

convert table column -> clob to varchar2

Moses Valle, September 15, 2011 - 9:09 pm UTC

Hi sir! I've read the posts above and I haven't found a solution to my prob yet. Can you please give me a

Here's a simple scenario (but my actual data is a LOT more than this):

-> I have a table named 'TEST' in the HR schema with these columns/datatypes.

CHAR_CONTENT VARCHAR2(4000)
CLOB_CONTENT CLOB
NUM_CONTENT NUMBER

-> I want to convert CLOB_CONTENT to a varchar2 WITHOUT dropping then recreating the table. Just convert it thru a ALTER or column replacement, etc. If possible, i wish to preserve the contents of the clob_content column (they don't exceed 4000bytes by the way).

-> Is this possible through a simple sql statement sir tom? Thank you in advance :)

Tom Kyte
September 16, 2011 - 2:11 pm UTC

alter table t add temp varchar2(4000);
update t set temp = clob_content, clob_content = null;
alter table t drop column clob_content;
alter table t rename temp to clob_content;

Extremly Useful

ABOOBACKER KARIPPOOR, September 20, 2011 - 10:26 pm UTC

We work in GIS. We where getting Network I/O Error in showing attribute information of one ESRI Feature Class. The reason was there an CLOB field in the feature class. This asnwer helped us to identify the issue and resolve it.

A reader, January 30, 2012 - 4:43 pm UTC

Hi Guys,

In dotnet, using
dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );
is not working for me. I would like to bind data to Datagridview in .Net. If someone knows how to get this done, please comment.

A reader, February 10, 2012 - 6:35 pm UTC

Thanks for your clear answer

Need help

Giri, June 05, 2012 - 8:50 am UTC

Hi Tom,

I have a similar issue. Am using following query.

v_file_data VARCHAR(4000)

select to_char(substr(file_data,i*4000+1,4000)) into v_file_data from tc_que_req where req_id = ${request_id};

When the bytes size is more than 4000, its throwing me error.
I tried using substrb, it dint work .

I dont want reduce or increase the size of v_file_data. Please let me know what else can be done .

Thanks,

Tom Kyte
June 05, 2012 - 1:08 pm UTC

this makes no sense.

what error is it throwing?

how can the byte size be more than 4000? are we dealing with multi-byte data here?

give an entire test case please.

xml data

A reader, July 12, 2012 - 5:04 pm UTC

Hello,

In a SOA architecture that may have Oracle and SQL Server databases (besides lot of other services), XML format is the preferred one to exchange data between all the services. In the database, what is the good way to store data? Should we use CLOB, XMLTYPE or regular VARCHAR? If VARCHAR is used, can the developers write a program so that the data they extract from the database is later converted into XML?

The important priority is performance. This should not be compromised. Your input is highly appreciated.

Thanks,

Tom Kyte
July 12, 2012 - 6:42 pm UTC

rows and columns. rows and columns.

not in xml, not unless you are just storing this junk for an audit trail. If you actually want to use it - rows and columns.



if you persist with persisting xml (ugh) then

a) varchar2 would be fastest but only if you have 4000 bytes or less.
b) clob would be second
c) xmltype would be 3rd


but that only talks to insert performance which is only one myoptic view of performance. You don't actually say what you need to do to these things.

Very helpful

Suddhasatwa Bhaumik, July 18, 2012 - 1:33 am UTC

Above solution by Tom helped resolve extracting lost data from previous backups. Thanks again!

Perfect

Cyril T, August 24, 2012 - 4:07 am UTC

Exactly what I needed!!! Very useful and easy to use.
Thanks Tom!

Saved my life!!

Adriano Gonçalves, October 03, 2012 - 3:18 pm UTC

Thank you very much! Finally I got a solution for this foggy problem!

A reader, September 13, 2013 - 4:29 am UTC


dbms_lob.substr behaviour on 11G

Devendra, September 18, 2013 - 4:27 pm UTC

I have antoher issue with this. I am getting wierd result from 11G.

My Table structure is something like this.

POLICYN VARCHAR2(10)
TYPE VARCHAR2(2)
SUBTYPE VARCHAR2(2)
LAST_NAME VARCHAR2(30)
PREM NUMBER(10,2)
MIN_PREM NUMBER(10,2)
LOAN NUMBER(10,2)
LOAN_INT NUMBER(10,2)

but dbms_metadata.get_dependent_ddl for constratints gives me below result.

ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("LOAN_INT" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("LOAN" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("MIN_PREM" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("PREM" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("LAST_NAME" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("SUBTYPE" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("TYPE" NOT NULL ENABLE)
ALTER TABLE "UNIACC"."LOAN_BILLS" MODIFY ("POLICYN" NOT NULL ENABLE)

Here Column names are appearing in reverse order. In 10G it works find but for 11G it is a Problem.Any suggestion on that please
Tom Kyte
September 24, 2013 - 11:26 pm UTC

so? why do you care what order they are in? the order is not relevant. any order is suitable. it is working fine in both releases.

Using Distinct

A reader, November 30, 2013 - 5:54 am UTC

It's applicable to use distinct after converting ClOB Datatype to VARCHAR Datatype?

Thanks for your valuable contribution

It is not that easy....I wish it was

Linda Hall, August 23, 2017 - 9:47 am UTC

I used DBMS_LOB.substr(MY_LOB,4000,1) it returns ORA-06502 string buffer too small because the NCLOB contains a special character
Sergiusz Wolicki
August 23, 2017 - 11:48 pm UTC

If you deal with non-ASCII characters, you need to account for the fact that 4000 characters can, in the most pessimistic case occupy 16000 bytes, depending on the characters and the database character set. If you read a value for use in PL/SQL only, just declare the variable as VARCHAR2(4000 CHAR). Such variable will not exceed the PL/SQL limit of 32767 bytes. If you have to store the value in a column, migrate to extended data types (aka VARCHAR2(32K)) in Oracle Database 12c or limit the read size to 1000 characters and use a VARCHAR2(1000 CHAR) variable.

I didnt got the result as i wanted

shubham, December 11, 2018 - 12:43 pm UTC

i have a clob column which i converted from blob result came from a table. Now i want to convert this clob into string. I tried select dbms_lob.substr(),tochar(),cast() but everything is giving me the clob result only i.e. when i am pasting the result into the notepad i am seeing only few special charcters

select dbms_lob.substr(clob_test2.clob_test,4000)
as Field_Name from clob_test2

but it isn't giving me the string I wanted in the result
Connor McDonald
December 12, 2018 - 3:48 am UTC

Complete test case required.

A reader, December 20, 2018 - 9:15 am UTC

I have a one clob column in table and i want to clob column modified to varchar2 data type.
So I not want create new column with varchar2 data-type and convert clob to varchar2 using dbms_clob.substr. I want to use DBMS_REDEFINITION of table so is it possible?
Connor McDonald
January 07, 2019 - 6:58 am UTC

Clob query

Gopikrishnan, March 17, 2019 - 11:26 am UTC

Hi,
I am storing a xml data in a clob column how can I extract large xml form that column. Now I'm directly copying from the table column value of clob it show 158 more lines but how can I extract complete text inside the column
Connor McDonald
March 18, 2019 - 10:16 am UTC

No test case... no details ... no anything :-(

A Reader, April 29, 2019 - 2:46 pm UTC

Hello,

In below testcase, DBMS_LOB.SUBSTR is returning more than 4000 bytes. Am i missing something here ?

https://livesql.oracle.com/apex/livesql/s/ibf64h6wr1k76alatxvc0151f

Regards
Connor McDonald
April 30, 2019 - 2:33 am UTC

4000 characters can be more than 4000 bytes, because a single character can require more than 1 byte to represent.

TANKS

MOJTABA, May 16, 2020 - 9:30 pm UTC

TANKS YOU TOM YOU ARE VERY GOOD
Connor McDonald
May 18, 2020 - 3:01 am UTC

glad we could help

working fine

A reader, October 07, 2020 - 11:12 am UTC


Where did the data go?

Carie, October 13, 2021 - 6:39 pm UTC

Hello,
Yesterday a customer asked, "Where did the data go?" I looked in the Oracle table and the data was there but when I looked in Spotfire (the app used to present data to the customers) the cells are blank. I created a temp table using to_char(clob_column) and dbms_lob.substr(clob_column,2000,1), and both showed data in the table, but neither showed data in Spotfire. I tried exporting to Excel from Oracle SQL Developer and got data like this: A?l?a?r?m?... that is supposed to be the word Alarm. Never had this happen before, not sure where this is coming from.
Chris Saxon
October 14, 2021 - 11:39 am UTC

Hard to say without more details of what's happening, but at a guess you've got a character set conversion issue somewhere.

converting CLOB datatype to VARCHAR2 datatype having multi-byte characters

Reader, August 09, 2022 - 6:15 pm UTC

Hi Tom,
I'm using oracle version 19c.
My case is to fetch first 15k characters from clob(might be having more than 15k) as string in sql.
1. To achieve this I'll have to convince source oracle DB team to extend the string size using system parameter at DB level.
Do we get into any issues if we extend this size ?
Please advise if there is any alternative.
2. Even with 4K length, when I try to convert clob to varchar2 using DBMS_LOB.SUBSTR, its throwing ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
seems like few rows are having multi-byte characters.
when I mention 3300, its working fine.

SELECT 
DBMS_LOB.SUBSTR(DESCRIPTION_OF_ACCOUNTS,4000,1)
FROM ETL.TBL_STG
where nvl(dbms_lob.getLength(DESCRIPTION_OF_ACCOUNTS),0)<=4000;


seems like getLength and SUBSTR functions are considering different length units char/bytes.
How can we handle this ?
3. If we use the below function, we are fetching less than 4k characters for few rows which are having multi-byte characters. Please advise.

create or replace FUNCTION SUBSTR_MULTIBYTE_CLOB
(
  P_DATA IN CLOB 
, P_START_INDEX IN NUMBER 
) RETURN VARCHAR2 AS 
P_OUT VARCHAR2(4000 BYTE);
P_LENGTH NUMBER := 4000;
BEGIN
    FOR loop_counter IN 1..400 LOOP    
        BEGIN
             P_OUT := DBMS_LOB.SUBSTR(P_DATA,P_LENGTH-((loop_counter-1)*10),P_START_INDEX);            
            RETURN P_OUT;           
         EXCEPTION
            WHEN OTHERS THEN
               IF SQLCODE = -12801 OR SQLCODE = -6502 OR SQLCODE = -1401 OR SQLCODE = -1489 THEN
                  --DBMS_OUTPUT.PUT_LINE(SQLCODE || '-' || SQLERRM); -- suppresses ORA-12801 "error signal from parallel server" or ORA-06502 exception "character string buffer too small" and some others I've got...
                  NULL;
               ELSE
                  DBMS_OUTPUT.PUT_LINE(SQLCODE || '-' || SQLERRM);
                  RAISE;
               END IF;
         END;         
    END LOOP;  
END SUBSTR_MULTIBYTE_CLOB;
/

select 
ETL.SUBSTR_MULTIBYTE_CLOB(DESCRIPTION_OF_ACCOUNTS,1)
FROM ETL.TBL_STG;


Connor McDonald
August 10, 2022 - 3:22 am UTC

My first question is

If you have 15k characters, then why do the migration at all? With extended varchar2, we *still* will store the data as a clob if the data is larger than 4k.

Database Globalization Support Guide

emaN, August 10, 2022 - 4:31 am UTC

Even with 4K length, when I try to convert clob to varchar2 using DBMS_LOB.SUBSTR, its throwing ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
seems like few rows are having multi-byte characters.
when I mention 3300, its working fine.


dbms_lob.substr and substr(clob) count a characters. The database limit for SQL is 4000 bytes (32767 bytes with max_string_size=extended). The length of the characters can be up to 4 bytes. So, the safe limit for substr(CLOB) is 1000 (8191) chars.
In the multibyte database AL32UTF8, the CLOB has the charset AL16UTF16. Some characters in these charsets have different lengths in "characters". As result, the varchar2 length of substr(clob, LEN) may be less than LEN and the last incomplete character may be truncated.

Reader, August 10, 2022 - 6:57 am UTC

Thanks Connor for the response.
We are using informatica to read from source and load into the target DB. it is taking more time to read clob compared to varchar2. so informatica suggested to convert clob to varchar2 and read the data.
Hence we are trying to use any functions in select query to convert clob to varchar2.
Hope this answers your question. Thanks.
Connor McDonald
August 11, 2022 - 4:43 am UTC

. it is taking more time to read clob compared to varchar2


I would trace both to find out why you are seeing this difference. If I compare 2 x 3.5k varchars with 1x7k clob, I get comparable results, even when varying array fetch sizes


SQL>
SQL> create table t1 ( x int, y varchar2(4000), z varchar2(4000));

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rpad(rownum,3500), rpad(rownum,3500)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> set autotrace traceonly stat
SQL> set timing on
SQL> set arraysize 10
SQL> select * from t1;

100000 rows selected.

Elapsed: 00:00:05.35

Statistics
----------------------------------------------------------
         19  recursive calls
         17  db block gets
     153689  consistent gets
          0  physical reads
    4665364  redo size
  704329429  bytes sent via SQL*Net to client
     110367  bytes received via SQL*Net from client
      10001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set arraysize 100
SQL> select * from t1;

100000 rows selected.

Elapsed: 00:00:04.16

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     100139  consistent gets
          0  physical reads
          0  redo size
  702583429  bytes sent via SQL*Net to client
      11367  bytes received via SQL*Net from client
       1001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set arraysize 1000
SQL> select * from t1;

100000 rows selected.

Elapsed: 00:00:04.52

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     100139  consistent gets
          0  physical reads
          0  redo size
  702408829  bytes sent via SQL*Net to client
       1467  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> create table t2 ( x int, y varchar2(12000));

Table created.

Elapsed: 00:00:00.02
SQL>
SQL> insert into t2
  2  select rownum, rpad(rownum,7000)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

Elapsed: 00:00:23.05
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> set autotrace traceonly stat
SQL> set arraysize 10
SQL> select * from t2;

100000 rows selected.

Elapsed: 00:00:10.79

Statistics
----------------------------------------------------------
         11  recursive calls
         11  db block gets
     110937  consistent gets
      26823  physical reads
      15004  redo size
  703429394  bytes sent via SQL*Net to client
     110367  bytes received via SQL*Net from client
      10001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set arraysize 100
SQL> select * from t2;

100000 rows selected.

Elapsed: 00:00:06.66

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     101696  consistent gets
       3976  physical reads
          0  redo size
  701683754  bytes sent via SQL*Net to client
      11367  bytes received via SQL*Net from client
       1001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set arraysize 1000
SQL> select * from t2;

100000 rows selected.

Elapsed: 00:00:06.75

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     100797  consistent gets
        188  physical reads
          0  redo size
  701512754  bytes sent via SQL*Net to client
       1467  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL>
SQL>

To Connor

emaN, August 12, 2022 - 7:45 am UTC

If I compare 2 x 3.5k varchars with 1x7k clob, I get comparable results

SQL> create table t1 ( x int, y varchar2(4000), z varchar2(4000));
...
SQL> create table t2 ( x int, y varchar2(12000));


Both tables are VARCHAR2. Also make sure that the TRACEONLY fetches the entire LOB, not just the locator (bytes sent to client?). Since there is no array fetch for the LOB contents, the client should send additional open, multiply reads, and close on each row.

My test

emaN, August 12, 2022 - 10:36 am UTC

create table t1(a number, b varchar2(32767 byte)); -- max_string_size=extended
create table t2(a number, b clob);
insert into t1 select rownum, rpad(rownum,32000,rownum) from dual connect by level<=1000;
insert into t2 select rownum, rpad(rownum,32000,rownum) from dual connect by level<=1000;

set long 100000 -- default 80
set longchunksize 65536 -- default 80
set autotrace traceonly
set timing on

SQL*Plus:
select * from t1; --varchar2
Elapsed: 00:00:02.96 
       4082  consistent gets
   32021239  bytes sent via SQL*Net to client
        774  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client

select * from t2; --clob
Elapsed: 00:00:09.16
       4009  consistent gets
   64433072  bytes sent via SQL*Net to client
     733392  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client

select a, to_char(b) b from t2; -- clob -> varchar2
Elapsed: 00:00:03.07
         82  consistent gets
   32021239  bytes sent via SQL*Net to client
        774  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client

SQLcl:
select * from t1;
              98  SQL*Net roundtrips to/from client
            1835  bytes received via SQL*Net from client
        32077062  bytes sent via SQL*Net to client
            4079  consistent gets
Elapsed: 00:00:00.817

select * from t2;
              99  SQL*Net roundtrips to/from client
            1940  bytes received via SQL*Net from client
        64225991  bytes sent via SQL*Net to client
              80  consistent gets
        65536000  securefile direct read bytes
            1016  securefile direct read ops
Elapsed: 00:00:01.798

select a, to_char(b) b from t2;
              98  SQL*Net roundtrips to/from client
            1849  bytes received via SQL*Net from client
        32077083  bytes sent via SQL*Net to client
              79  consistent gets
        65536000  securefile direct read bytes
            1016  securefile direct read ops
Elapsed: 00:00:00.834


In this test the clob size 64000 bytes (2 bytes per digit char AL16UTF16) and the varchar2 size 32000 bytes (1 bytes per digit char AL32UTF8), both do not fit into block with enable storage in rows. T1 lob segment size is 42MBytes, T2 lob segment size is 75MBytes.

SQLcl takes twice as long to fetch a clob due to size of ascii in AL16UTF16. SQL*Plus additionally shows many roundtrips for clob.


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here