Converting CLOBS TO VARCHAR
March 16, 2001 - 7am Central time zone
Reviewer: Surrinder Singh from UK
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.
March 16, 2001 - 1pm Central time zone
Reviewer: Gururaj from Virginia USA
Converting CLOBS TO VARCHAR
March 16, 2001 - 3pm Central time zone
Reviewer: Naveen from Raleigh,NC
As usual, TOM makes it look easy.
Converting CLOBS TO VARCHAR
March 16, 2001 - 3pm Central time zone
Reviewer: Naveen from Raleigh,NC
As usual, TOM makes it look easy.
How to display long data after <xml> tag in text file via sql*plus?
June 5, 2001 - 2pm Central time zone
Reviewer: Teresa Felch from Huntsville, Alabama 35824
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
June 25, 2001 - 5pm Central time zone
Reviewer: Satya K from San Francisco, CA USA
This means, Oracle cursor can not have any column more than 4000 chars width..
Converting CLOBS TO VARCHAR
March 18, 2002 - 5pm Central time zone
Reviewer: Karl Bergerson from Bellevue WA USA
It worked for me. Quick efficient. Thanks.
dbms_lob.substr problem
June 10, 2003 - 7am Central time zone
Reviewer: A reader
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
Followup June 10, 2003 - 8am Central time zone:
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.
October 21, 2004 - 4pm Central time zone
Reviewer: Ken Chiu from Canada
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.
Followup October 22, 2004 - 3pm Central time zone:
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
February 9, 2005 - 6pm Central time zone
Reviewer: Frank
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.
Followup February 10, 2005 - 12am Central time zone:
set colsep ','
select question_id, text from ......
would be one approach.
February 15, 2005 - 1pm Central time zone
Reviewer: A reader
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.
Followup February 15, 2005 - 3pm Central time zone:
a csv file with no newlines?
what other database (i've worked with lots of them) wants that???????
February 15, 2005 - 10pm Central time zone
Reviewer: A reader
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.
Followup February 16, 2005 - 7am Central time zone:
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
February 23, 2005 - 4pm Central time zone
Reviewer: A reader
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.
Followup February 24, 2005 - 5am Central time zone:
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"
February 24, 2005 - 10am Central time zone
Reviewer: A reader
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.
Followup February 24, 2005 - 4pm Central time zone:
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.
February 24, 2005 - 12pm Central time zone
Reviewer: A reader
Is it also possible to find the length of the long variable?
Thanks for all the help.
clob in pl/sql
July 28, 2006 - 3am Central time zone
Reviewer: A reader
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?
Followup July 28, 2006 - 7pm Central time zone:
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
July 29, 2006 - 12pm Central time zone
Reviewer: A reader
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?
Followup July 29, 2006 - 4pm Central time zone:
you never bind a number?
clob in pl/sql
July 30, 2006 - 5am Central time zone
Reviewer: A reader
Hi
We do bind numbers, if explain plan has such limitations what is it used for....???
Followup July 30, 2006 - 8am Central time zone:
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.
February 13, 2007 - 2pm Central time zone
Reviewer: Su Baba
How do I convert a VARCHAR2 column to a CLOB?
Followup February 13, 2007 - 4pm Central time zone:
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?
February 13, 2007 - 6pm Central time zone
Reviewer: Su Baba
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
Followup February 14, 2007 - 8am Central time zone:
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
February 28, 2007 - 9pm Central time zone
Reviewer: Amao from China
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?
Followup February 28, 2007 - 10pm Central time zone:
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.
March 1, 2007 - 5pm Central time zone
Reviewer: michaels from Germany
> 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.
Followup March 4, 2007 - 12pm Central time zone:
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
April 4, 2008 - 10am Central time zone
Reviewer: htu from NJ USA
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?
Followup April 4, 2008 - 12pm Central time zone:
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
April 4, 2008 - 10am Central time zone
Reviewer: htu from NJ USA
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
April 4, 2008 - 1pm Central time zone
Reviewer: htu from NJ USA
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
April 4, 2008 - 1pm Central time zone
Reviewer: htu from NJ USA
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?
Followup April 4, 2008 - 1pm Central time zone:
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
April 4, 2008 - 2pm Central time zone
Reviewer: htu from NJ USA
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.
Followup April 4, 2008 - 10pm Central time zone:
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
April 5, 2008 - 4pm Central time zone
Reviewer: A reader
dbms_lob.substr returns different length in two oracle instances
April 7, 2008 - 10am Central time zone
Reviewer: htu from NJ USA
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?
Followup April 8, 2008 - 11am Central time zone:
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)
April 24, 2008 - 12pm Central time zone
Reviewer: Milo van der Leij from MI, USA
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.
Followup April 28, 2008 - 12pm Central time zone:
ahh, i did not even think about that - the characterset, of course, that makes total sense (the os was a red herring entirely)
June 17, 2008 - 4pm Central time zone
Reviewer: A reader
CLOB to VARCHAR over DBLink ???
September 22, 2008 - 2pm Central time zone
Reviewer: Jack from Atlanta,GA
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 ?
Followup September 23, 2008 - 1pm Central time zone:
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
October 10, 2008 - 12pm Central time zone
Reviewer: Jack from Atlanta, GA
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
Followup October 13, 2008 - 2am Central time zone:
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 ?
October 14, 2008 - 10pm Central time zone
Reviewer: Kiran from South Bend,IN
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 ?
Followup October 15, 2008 - 5pm Central time zone:
there is not.
CLOB in WHERE
October 16, 2008 - 7am Central time zone
Reviewer: KK from SBN,IN
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 .
Followup October 17, 2008 - 8pm Central time zone:
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
October 20, 2008 - 11am Central time zone
Reviewer: K Kiran from South Bend,IN
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 ?
Followup October 21, 2008 - 12pm Central time zone:
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
October 22, 2008 - 9am Central time zone
Reviewer: K Kiran from SBN,IN USA
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
October 31, 2008 - 8am Central time zone
Reviewer: Chris from UK
Just to say thanks - I had had trouble with CLOBs before, but this makes everything so simple.
Converting CLOB to Varchar
March 31, 2009 - 11am Central time zone
Reviewer: Janet A from Atlanta, GA USA
Thank you so much! It was so simple yet working
How to insert Varchar2 into CLOB
April 2, 2009 - 9am Central time zone
Reviewer: Samy from India
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
Followup April 2, 2009 - 10am Central time zone:
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.
August 5, 2009 - 5pm Central time zone
Reviewer: Abhijit from Pennslyvania
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
Followup August 5, 2009 - 5pm Central time zone:
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
August 6, 2009 - 8am Central time zone
Reviewer: Abhijit from Pennslyvania
Thanks for the tip, Tom.
August 6, 2009 - 3pm Central time zone
Reviewer: Abhijit from Pennsylvania
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
Followup August 6, 2009 - 3pm Central time zone:
why are you not using my code
If comments is a varchar2, then it does not have more than 4000 characters.
August 6, 2009 - 3pm Central time zone
Reviewer: Abhijit from Pennsylvania
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
Followup August 6, 2009 - 4pm Central time zone:
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.
|