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
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.
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.
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.
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.
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.
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.
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.
February 24, 2005 - 5:07 pm UTC
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?
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?
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....???
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?
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
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?
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.
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?
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?
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.
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?
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.
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 ?
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
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 ?
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 .
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 ?
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
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
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
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
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
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!
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
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
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
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.
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
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
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 :)
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,
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,
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
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
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
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?
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
March 18, 2019 - 10:16 am UTC
No test case... no details ... no anything :-(
A Reader, April 29, 2019 - 2:46 pm UTC
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
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.
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;
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.
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.