Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bhavesh.

Asked: March 20, 2001 - 10:22 pm UTC

Last updated: July 01, 2013 - 5:00 pm UTC

Version: 4.0.8

Viewed 100K+ times! This question is

You Asked

Hi Tom,
This is in continuation of my question on how can I insert streams of characters coming from HTML form into a oracle database table CLOB column.
Actually I am passing some free flow text (series of characters say something like what I am writing in this question) from html form. When this column is passed into the procedure which inserts the record what should be the datatype and how can I insert this incoming text into a CLOB column.
Regards

Bhavesh

and Tom said...

If you are calling a stored procedure, VARCHAR2.

tkyte@TKYTE816> create table t ( x int, y clob );
Table created.

tkyte@TKYTE816>
tkyte@TKYTE816> create or replace procedure p( p_x in int, p_new_text in varchar2 )
2 as
3 begin
4 insert into t values ( p_x, p_new_text );
5 end;
6 /

Procedure created.

tkyte@TKYTE816>
tkyte@TKYTE816> exec p(1, rpad('*',32000,'*') );

PL/SQL procedure successfully completed.

tkyte@TKYTE816> exec p(2, 'hello world' );

PL/SQL procedure successfully completed.

tkyte@TKYTE816>
tkyte@TKYTE816> select x, dbms_lob.getlength(y) from t;

X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
1 32000
2 11

2 rows selected.


followup to the comments below about the 4000 characters...

The difference is I used a BIND VARIABLE in the above example. PLSQL limits my bind variable to 32k.

In SQL, the limit is 4,000 characters. Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters.

See
</code> http://asktom.oracle.com/Misc/LargeStrings.html <code>
for another example of this.

Followup to comment below...

In Oracle8.0, the behavior of PLSQL is different then in 8.1 (which is what I used in my example.

In Oracle8.0, you would code:

1 create or replace procedure p( p_x in int, p_new_text in varchar2 )
2 as
3 l_clob clob;
4 begin
5 insert into t values ( p_x, empty_clob() ) returning y into l_clob;
6 dbms_lob.write( l_clob, 1, length(p_new_text), p_new_text );
7* end;
scott@ORA806.WORLD> /

Procedure created.

scott@ORA806.WORLD> exec p( 1, rpad('*',32000,'*') );

PL/SQL procedure successfully completed.

scott@ORA806.WORLD> select dbms_lob.getlength(y) from t;

DBMS_LOB.GETLENGTH(Y)
---------------------
32000


instead.




Rating

  (67 ratings)

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

Comments

Inserting a CLOB data

Nirmal kumar, March 22, 2001 - 2:58 am UTC

Hi. tom,


I tried your answer in to my oracle 8.1.6 database on NT. The insert statement allows me to specicy any size for the cLOB column, but in select statement, it shows only 4000 as size for the CLOB columns. The following is the one i tested.

sql> create table ddreg(file# number, ftext CLOB);


table created;

sql> Insert into ddreg(1, rpad('n',50000,'-'));
1 row inserted;

sql> Select dbms_lob.getlength(ftext) from ddreg;

DBMS_LOB.GETLENGTH(FTEXT)
------------------------
4000

Why it's like this.



Inserting a CLOB data

Bhavesh, March 22, 2001 - 7:05 am UTC

I have tried this solution but to my surprise, when I see the dbms_lob.getlength(col2) using select statement the length shown is 4000 only whereas the data inserted was many times bigger than this. I am using oracle 8.1.6 with NT as OS. Can you please explain as to why this is happening.

Inserting a CLOB data

Bhavesh, March 22, 2001 - 8:44 pm UTC

Post Follow up:
   When I tried to run exec p I got following error.

SQL> exec p(1, rpad('*',32000,'*') );
BEGIN p(1, rpad('*',32000,'*') ); END;

*
Error at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column.
ORA-06512: at "TEST.P", line 3
ORA-06512: at line 1

 

It is great, but

Lucy, November 06, 2001 - 12:02 pm UTC

it didn't work in our 8.1.6.3.1 database when the cursor_space_for_time=true, we got ora 7445 in the server and ora 3113 in the client (sqlplus), after I turn off cursor_space_for_time as false, it works, could you tell me why, and if I set it as false, is any performance impack.

Thanks

Clobs larger than 32k

Femi, February 01, 2002 - 5:22 pm UTC

Tom,

Could you please show an example of how to insert CLOB data larger than 32K, using PL/SQL or is this not possible? From you previous eaxmples I have iserted upto 32K worth of data.

create or replace procedure lob_ins(p_id IN number, p_text IN varchar2 )
as
l_clob clob;
BEGIN
insert into demo values (p_id, empty_clob() ) returning y into l_clob;
dbms_lob.write( l_clob,length(p_text), 1,p_text);
END;
/




Tom Kyte
February 01, 2002 - 7:44 pm UTC

You have to chunk it up and use dbms_lob.writeappend.

PLSQL will not (does not) support strings >32k. P_text as an in parameter cannot exceed 32k. You will have to "chunk up" the input data.

something like (i did NOT compile this, the gist is there tho...)

create package lob_pkg
as
procedure lob_ins( p_id in number, p_text in varchar2 );
procedure add_more( p_text in varchar2 );
end;
/

create package body lob_pkg
as

g_clob clob;


procedure lob_ins(p_id IN number, p_text IN varchar2 )
as
begin
insert into demo values (p_id, empty_clob() ) returning y into g_clob;
dbms_lob.write( g_clob,length(p_text), 1,p_text);
end;

procedure add_more( p_text in varchar2 )
as
begin
dbms_lob.writeappend( g_clob, length(p_text), p_text );
end;

end;
/


and then call

exec ins_lob( 1, 'hello' );
exec add_more( ' world' );

passing upto 32k of data at a time.

Where will i store the image files?

Chidambara Barathi, March 27, 2002 - 1:50 am UTC

Hi Tom,

Where will i save the image files in client or in server?

sothat i can read the file and update in the table...

Thanks.



Tom Kyte
March 27, 2002 - 8:06 am UTC

Umm, you need to tell us where you want to store the images.

Me, I would put them in the database for all to access. On the client means one person can get it. You would retrieve if from the DB for them when they need it.

inserting clob

Venkat, July 10, 2003 - 6:14 am UTC

Tom,

When I tried with the given procedure, I could insert upto 32700. When I entered 32800, it has thrown the error
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksmovrflow], [kokleva unicode buf],
[], [], [], [], [],
[]

Can you please explain this phenomena? Oracle version is 8.1.7.4

Regards,
Venkat

Tom Kyte
July 10, 2003 - 9:59 am UTC

32k at a time, 32k is the limit for a plsql variable - with utf8, that could be significantly LESS then 32k CHARACTERS (as each utf8 character could take 1, 2, 3 or more bytes to represent)

inserting clob

Venkat, July 10, 2003 - 6:15 am UTC

Tom,

When I tried with the given procedure, I could insert upto 32700. When I entered 32800, it has thrown the error
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksmovrflow], [kokleva unicode buf],
[], [], [], [], [],
[]

Can you please explain this phenomena? Oracle version is 8.1.7.4. db character set is UTF8

Regards,
Venkat

I could only insert up to 4k clob

June, January 05, 2004 - 3:41 pm UTC

I tried to use above procedure, but got error when I tried to insert over 4k:

 SQL> exec p(1, rpad('*',4000,'*'));

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec p(1, rpad('*',5000,'*'));
BEGIN p(1, rpad('*',5000,'*')); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [koklgloc: arrlen], [], [], [], [],
[], [], []
ORA-06512: at "P", line 4

and when I tried to use dbms_lob.write/writeappend, got following error:
RROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 722
ORA-06512: at " LOB_INS", line 8  -- where is following statement: dbms_lob.write(v_clob, length(p_text), 1, p_text);

ORA-06512: at line 1

version 8.1.7.4

what should I do? using bind variables? 

Tom Kyte
January 06, 2004 - 7:45 am UTC

Lets see this cut and paste from your system -- this too is 8174 (and my example already does use binds for the lobs)


ops$tkyte@ORA817DEV> create table t ( x int, y clob );
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace procedure p( p_x in int, p_new_text in varchar2 )
  2  as
  3  begin
  4          insert into t values ( p_x, p_new_text );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec p(0, rpad('*',5000,'*') );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> exec p(1, rpad('*',32000,'*') );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> exec p(2, 'hello world' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> select x, dbms_lob.getlength(y) from t;
 
         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         0                  5000
         1                 32000
         2                    11
 
ops$tkyte@ORA817DEV> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
 

Writing/Reading web page to/from CLOB

Robert, February 02, 2004 - 1:20 pm UTC

8.1.7
To make my web app seem blinding fast, I'm thinking to load/reading a whole webpage (non-changing historical report) 40K + to a CLOB column indexed with the report id...how can I achieve this ?

Im thinking in stead of htp.p-ing, I dump to my own app buffer first, then write to the LOB, then HTP it out...please provide code snippet on how to write to LOB in looping thru my plsql table buffer and how to read it back.

Thanks

Tom Kyte
February 03, 2004 - 7:31 am UTC

well, does this site seem fast?

I did not resort to creating lobs of each page - each page is constructed as it is requested.

I'd focus on writing good code personally.

But, if you want to do this, why not just publish to flat files?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:347617533333 <code>

then, let the web server just return a page.

or, just change that code to use dbms_lob.writeappend instead of utl_file. upon retrieval, you would just select clob into l_clob from t where id = :x and then use dbms_lob.substr to htp.p 32k chunks of it.

no flat files laying around...

robert, February 03, 2004 - 9:22 am UTC

>> why not just publish to flat files?

hey, I want EVERYTHING in the database, ok ? :)

thanks

A reader, April 05, 2004 - 3:22 pm UTC


Very helpful response...

A reader, April 05, 2004 - 3:28 pm UTC

Is there any way to find out using a plsql package if the string returned by

rpad('*',64000,'*'));

function exceeds 32K?

- Karthik

Tom Kyte
April 05, 2004 - 5:28 pm UTC

 
ops$tkyte@ORA9IR2> exec dbms_output.put_line( length(rpad('*',64000,'*')) );
BEGIN dbms_output.put_line( length(rpad('*',64000,'*')) ); END;
 
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small
ORA-06512: at line 1
 

Very useful to know about bind variable. One question though...

Karthik, April 06, 2004 - 10:32 pm UTC

create table t ( x number, y clob );

create or replace procedure write_clob( p_x in number, p_new_text in varchar2 )
as
begin
insert into t values ( p_x, p_new_text );
end;

exec write_clob(1, rpad('*',4001,'*') );

----works fine!

Now I have this procedure in a 11i Application Server and have another procedure that renders a html page as follows

create or replace procedure render_html( p_x in number)
as
begin
l_text := rpad('*',4001,'*');
htp.p('<html><head></head><body>');
htp.p('<form action ="write_clob" method = "post">');
htp.p('<input type = "hidden" name = "p_x" value = "'||p_x||'"');
htp.p('<input type = "hidden" name = "p_new_text" value = "'||l_text||'"');
htp.p('</form>');
htp.p('<input type = "submit">);
htp.p('</body></html>');
end;

Now if I type type the following url

</code> http://webserver:8000/pls/DEV/package_name.render_html <code>

Now if I submit this form the procedure in the action attribute should insert 4001 characters into a table, but if I get a page not found error, is it because the oracle web server cannot handle post variables larger than 4k or somethingelse? Its hard to debug because I just get a 404-page not found error.

Please help! Thanks a lot for taking your time.

Tom Kyte
April 07, 2004 - 9:05 am UTC

ask your admin to configure the dad so as to get you the error message insead of the 404 not found, that'll help.

we can do lots more than 4000 (look at this page, it is all mod_plsql based and it is bigger than 4000)

ora-22275

A reader, June 21, 2004 - 11:43 am UTC

hi tom
can you please tell me why I am getting the following
error?

Thanx!
----
scott@ORA92> drop table t1;

Table dropped.

scott@ORA92> create table t1
2 ( x number,
3 y clob
4 );

Table created.

scott@ORA92>
scott@ORA92> insert into t1 values( 1, 'testing clob');

1 row created.

scott@ORA92> insert into t1 values( 2, empty_clob());

1 row created.

scott@ORA92>
scott@ORA92> commit;

Commit complete.

scott@ORA92>
scott@ORA92> select * from t1;

1 testing clob
2

scott@ORA92> -- appending some text to a clob
scott@ORA92> --
scott@ORA92> declare
2 l_clob t1.y%type;
3 l_clob_copy t1.y%type := empty_clob();
4 l_str_to_append varchar2(20) := '< appended text>';
5 begin
6 select y into l_clob
7 from t1
8 where x = 2
9 and rownum <= 1
10 for update;
11
12 dbms_lob.writeappend( l_clob,
13 length(l_str_to_append), l_str_to_append );
14
15 update t1 set y = l_clob
16 where x = 2
17 and rownum <= 1;
18 commit;
19
20 dbms_lob.copy( dest_lob=>l_clob_copy,
21 src_lob => l_clob, amount => dbms_lob.getlength( l_clob) );
22
23 end;
24 /
declare
*
ERROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 410
ORA-06512: at line 20
-------

Tom Kyte
June 21, 2004 - 2:00 pm UTC

empty_clob() is only meaningful in an insert/update statement. you would use dbms_lob.createtemporary to do this in memory clob.

Thank you!!

A reader, June 21, 2004 - 2:19 pm UTC

Love your site and learn so much every day!!


inserting CLOB data into table is giving error

pooja, June 22, 2004 - 8:08 am UTC

hi tom,
 i'm trying to insert the clob data into my table res_det
which has the following structure

 post varchar2(10)
 loc  varchar2(20)
 resume clob

but when i trying to insert the data into the field by using the following procedure it is giving the following error:

create or replace procedure ins_clob is
l_clob    CLOB;
l_bfile   BFILE:=BFILENAME('my_dir','test.doc');
begin
 insert into res_det values('hjhj','hjhj',EMPTY_CLOB())
 returning resume into l_clob;
 dbms_lob.fileopen(l_bfile);
 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
 dbms_lob.fileclose(l_bfile);
end;
 SQL> exec ins_clob
BEGIN load_file; END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "PROCESS.INS_CLOB", line 7
ORA-06512: at line 1

but i have already created a directory as
create directory my_dir as 'd:\sree'
and the test.doc already exists in it.
i have given all the grants by my dba.
plz give me answer 

Tom Kyte
June 22, 2004 - 9:21 am UTC

couple of things here.

first, test.doc is probably a word document. that is binary. use a BLOB, not a CLOB for binary.

second, you issued:


create directory my_dir as 'd:\sree';

that created a directory MY_DIR. you passed the string 'my_dir' to bfilename -- you wanted to pass 'MY_DIR' or you wanted to issue:

create directory "my_dir" as ....

in order to keep it lower case. Also, D:\SREE better be a directory on the database server machine, it better be a LOCAL disk (not a share) and Oracle needs to have READ access to that directory.

What do we use???

A reader, June 22, 2004 - 10:47 am UTC

Hi Tom,
Just a simple question.
What do we use in what circumstances?
For e.g. in case of .txt, .doc, .pdf, .bmp, .gif, .wav
etc.
My question is what of these should be used LOB, CLOB, BLOB etc. in case of text files, image files, pictures, sound files, documents etc.
The documents are not that clear on this topic.
Hope I am clear enough.
Thanks as always..


Tom Kyte
June 22, 2004 - 1:46 pm UTC

clob = text, things you would edit in vi or notepad
blob = everything else

in the above list, the only thing that would possibly be stored in a clob would be a .txt file.

doc = word (assumption) = binary
pdf = binary
bmp = binary
gif = binary
wav = binary


Thanks Tom

A reader, June 22, 2004 - 2:51 pm UTC

As usual very helpful.
Thanks as always :D


it is giving me some different error tom

pooja, June 23, 2004 - 8:50 am UTC

hi tom,
thanx for ur reply and  i changed my field datatype as BLOB and created the directory again with the lower case as advised by u.i have given the read permission on this directory also.and this test.doc exists in my local pc not in the server.
can we use the local files or we have to use the server files??
and i modified the procedure as follows:
create or replace procedure load_file is
l_blob    bLOB;
l_bfile   BFILE:=BFILENAME('my_dir','test.doc');
begin
 insert into res_det values('hjhj','hjhj',EMPTY_BLOB())
 returning resume into l_Blob;
 dbms_lob.fileopen(l_bfile);
 dbms_lob.loadfromfile(l_Blob,l_bfile,dbms_lob.getlength(l_bfile));
 dbms_lob.fileclose(l_bfile);
end;
 now it is giving me a new error msg
SQL> exec load_file
BEGIN load_file; END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
LFI-00108: Open expect the file [test][doc] to exist.
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at "PROCESS.LOAD_FILE", line 7
ORA-06512: at line 1

plz let me know wat mistake i'm doing everytime tom..i need it a bit urgent. 

Tom Kyte
June 23, 2004 - 9:37 am UTC


LFI-00108: Open expect the file [test][doc] to exist.


seems clear? file was not there on the server or file was not readable by the server.

file MUST be on server (else the server would be able to read your file system, that would be pretty scary no?)

wrong point of vew regarding Web server architecture

Faceless, June 24, 2004 - 4:32 am UTC

Giys, i'm afraid that you never heard about caching, proper HTTP headers and such wonderful thing as reversing proxy
i.e. page is being created on-the-fly as Tom's engine actually currently does, and all images, texts etc. are stored into db.
i.e. use chain db (which generates content with http heders PROPERLY set + reversing proxy server
Such link will give you outstanding performance.

Tom Kyte
June 24, 2004 - 9:36 am UTC

well, just the text is stored in relational tables. and the text is very dynamic.

I've 'heard' about caching, http headers and so on. (actually intimately familar with the technologies)

what i've not heard of of 'chain db'.

these pages and images are totally cachable by the browser, I need do nothing more nor nothing less.

so -- what was your point? and what did it have to do with inserting CLOB data?

explain me

pooja, June 25, 2004 - 8:03 am UTC

hi tom,
still i'm not getting it tom.......i'll expalin u from the starting wat i did..
1.my server is hpunix 11x machine and 8.1.5 version of oracle and i have created a directory there /optis/direct.
2.i have rwx permissions of all files created in it.
3.i have given grants of create any directory and read on directory and also the dbms_lob.
4.i wrote the load_file procedure to insert into the table.

as u have already mentioned,i changed the my_dir directory to lower case and my field datatype to blob also.

i struck here and i'm sure that i'm not able to continue more without ur help.

Tom Kyte
June 25, 2004 - 2:29 pm UTC

show us the step by steps.

show me

$ sqlplus user/pass              (no tns connect string, you are on the server)

SQL> create directory ....
SQL> !ls -l /that/directory/test.doc
SQL> !ls -ld /that/directory
SQL> !ls -ld /that
SQL> create procedure....
SQL> exec that procedure



cut and paste that stuff.  show me you are on the server, the directory exists on the server, that the entire directory path is read/exec by all (else oracle cannot navigate into it), that the file exists, what the procedure looks like, what the directory object looks like...


Just like this:

[tkyte@tkyte-pc tkyte]$ sqlplus scott/tiger
 
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Jun 25 14:08:18 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
scott@ORA9IR2> create or replace directory "my_dir" as '/tmp'
  2  /
 
Directory created.
 
scott@ORA9IR2> !ls -l /tmp/test.doc
-rw-rw-r--    1 tkyte    tkyte           4 Jun 25 14:08 /tmp/test.doc
 
scott@ORA9IR2> !ls -ld /tmp
drwxrwxrwt   27 root     root         4096 Jun 25 14:08 /tmp
 
scott@ORA9IR2> create or replace procedure load_file
  2  as
  3          l_bfile   BFILE:=BFILENAME('my_dir','test.doc');
  4  begin
  5          dbms_lob.fileopen(l_bfile);
  6          dbms_lob.fileclose(l_bfile);
  7  end;
  8  /
 
Procedure created.
 
scott@ORA9IR2>
scott@ORA9IR2> exec load_file
 
PL/SQL procedure successfully completed.
 



 

CLOB as input parameter - 9.2.0.5

A reader, June 25, 2004 - 3:17 pm UTC

Tom,

What do you think of this approach of passing a CLOB to a stored procedure from Java.
</code> http://otn.oracle.com/sample_code/tech/java/codesnippet/jdbc/lob/lobtosp.html <code>
In your original response in this thread, you mentioned passing a VARCHAR2 to insert into a CLOB. The method described on the OTN site passes the entire CLOB at once.

I tested it out in a dev environment (removing the commit from the procedure of course), and it worked well with records from 1 KB to 1 MB (1 MB is the max I tested). In production, there shouldn't be many records > 32K, and using this technique there is no need to worry about the 32k limit.
Do you see a downside to this approach?

Thanks.

Tom Kyte
June 25, 2004 - 4:30 pm UTC

if you are using java, that is fine.

What about BLOB

Yousef, June 25, 2004 - 7:39 pm UTC

How can I copy BLOB data from one table to another

Tom Kyte
June 26, 2004 - 6:18 pm UTC

insert into t1 select from t2;

they just go.

blob insertion

pooja, June 26, 2004 - 1:04 am UTC

hi tom,
  this time i moved a bit thanx alot tom.
here are my steps:

 oracle8@dserver /oracle8i/app/oracle/product/8.1.5> sqlplus process/process

SQL*Plus: Release 8.1.5.0.0 - Production on Sat Jun 26 10:02:34 2004

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> create or replace directory "my_dir" as '/optis/tmp';
Directory created.

SQL> !ls -l /optis/tmp/test.doc
-rw-rw-rw-   1 oracle8    dba             41 Jun 26 09:31 /optis/tmp/test.doc

SQL> !ls -ld /optis/tmp
drwxrwxrwx   2 oracle8    dba             96 Jun 26 09:31 /optis/tmp

SQL> create or replace procedure load_file
  2  as
  3  l_bfile BFILE:=BFILENAME('my_dir','test.doc');
  4  begin
  5  dbms_lob.fileopen(l_bfile);
  6  dbms_lob.fileclose(l_bfile);
  7  end;
  8  /

Procedure created.

SQL> exec load_file;

PL/SQL procedure successfully completed.

i too got that file executed tom.
ok, from the form6i, i want that table's data to b displayed.as u know that i used the blob to the resume field,it is giving me image type on the form not the long type.if i changed that resume field to clob, then ok it is coming.
and one more thing tom, the previous test.doc is in the other directory name das "/optis/direct" on the server and the file test.doc is created by me using utl_file. when i used this it is not coming. y?? plz explain. now that test.doc is created by me manually on the server .
another one tom, my students resumes are all in the client machine.how can i send them all to the server.is the only way by utl_file or any other way.

plz let me know tom,

and thanx once again tom for this great help.
 

Tom Kyte
June 26, 2004 - 6:48 pm UTC

not following you -- I see you have the bfile's working now (your keyboard is failing -- dropping not only vowels that are needed (eg: you became 'u' somehow), but consonants like W and H too)


if test.doc is just a text file, use a clob.
if test.doc is a work file, you'll need to give it to word to display.


.doc is normally associated with word by convention.
.txt is normally associated with "just plain text", in order to avoid confusion, you might want to rethink your file extension in the future.

Few minot doubts -please clear them

A reader, September 05, 2004 - 8:41 pm UTC

My db version is 9.2.01

I did the following

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                         NOT NULL NUMBER(38)
 Y                                                  CLOB

SQL> truncate table t;

Table truncated.

SQL>  insert into t values ( 3, rpad('*',32000,'a'));

1 row created.

SQL> select x, dbms_lob.getlength(y) from t;

         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         3                  4000

SQL> select * from t;


         X Y
---------- --------------------------------------------------------------------------------
         3 *aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

SQL> 


1.I was expecting to see 32000 as the length of y, but it is showing 4000. Why

2.When I select y from t, it is only displaying 4000 characters. Why

3.Please help by showing how to retrieve the entire string in column Y.

Thanks

 

Tom Kyte
September 06, 2004 - 9:51 am UTC

in sql, the maximum length of a string is 4000 characters. You must use dbms_lob.write/writeAppend to perform larger operations.

the entire string stored is 4000 characters in this case.

declare
l_clob clob;
begin
insert into t values ( 3, empty_clob() ) returning y into l_clob;
dbms_lob.write( l_clob, 32000, 1, rpad('*',32000,'*') );
end;
/





A reader, September 06, 2004 - 11:13 am UTC

SQL> create table t ( x number, y clob);

Table created.

SQL> declare
  2     l_clob clob;
  3  begin
  4     insert into t values ( 3, empty_clob() ) returning y into l_clob;
  5     dbms_lob.write( l_clob, 32000, 1, rpad('*',32000,'*') );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select x, dbms_lob.getlength(y) from t;

         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         3                 32000

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


It worked exactly like the way you advetized.

But look below, in version 8.1.7 you did none of this write or write append but still it returned a length of 32000. Please expalin..




ops$tkyte@ORA817DEV> create table t ( x int, y clob );
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace procedure p( p_x in int, p_new_text in 
varchar2 )
  2  as
  3  begin
  4          insert into t values ( p_x, p_new_text );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec p(0, rpad('*',5000,'*') );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> exec p(1, rpad('*',32000,'*') );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> exec p(2, 'hello world' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> select x, dbms_lob.getlength(y) from t;
 
         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         0                  5000
         1                 32000
         2                    11
 
ops$tkyte@ORA817DEV> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production



Finally, when I tried to retrive y from table t, it did not even return 4000 length. Please explain.

SQL> select y from t;

Y
--------------------------------------------------------------------------------
********************************************************************************

SQL> 


How should I read a clob? 

Tom Kyte
September 06, 2004 - 11:53 am UTC

that was using a bind variable -- the best way to deal with lobs is to use dbms_lob.write/writeappend

In sqlplus to see "more" of the clob, issue

SQL> set long 500000

see also:

http://asktom.oracle.com/Misc/LargeStrings.html

A reader, September 06, 2004 - 1:38 pm UTC

Tom

I have to display 10,000 characters length string from a clob, in my oracle form, and may be even in a report.

How should read the clob for that purpose.

Your solution works in sqlplus( for a long though, how about a clob in sqlplus ?), how should I implement it for front ends.

How will the function reading the clob look like ? Please help.

Tom Kyte
September 06, 2004 - 3:26 pm UTC

in forms, you just build a default block, it'll do it for you. forms doesn't have a "set long" -- you just use the attributes of the item on the form to set the max lengths and so on.

A reader, September 06, 2004 - 4:23 pm UTC

'that was using a bind variable '

Do you mean the following

1.Using a direct insert statement the max we can insert into a clob is 4000

insert into t values ( 1, rpad('*',32000,'*') );


2.If we insert into a clob using a bind variable( in sqlplus or through a procedure) then the max we can insert is 32000.

tkyte@TKYTE816> exec p(1, rpad('*',32000,'*') );


3.If we want to insert into a clob more than 32000( i.e. more than the max length of a varchar2 pl/sql variable) then we have to use dbms_lob or dbms_lob.writeappend.

1 create or replace procedure p( p_x in int, p_new_text in varchar2 )
2 as
3 l_clob clob;
4 begin
5 insert into t values ( p_x, empty_clob() ) returning y into l_clob;
6 dbms_lob.write( l_clob, 1, length(p_new_text), p_new_text );
7* end;


Is my understanding right, as I have explained above.

Tom Kyte
September 06, 2004 - 5:03 pm UTC

1) correct, yes.
(imo it should have thrown an error on you, but did not)

2) 32k would be more appropriate

3) depends on your language, but yes in general. in java, i might use a "stream".

See the application developers guide for LOBS for details (down to the language)

CLOBs as arguments...

Kashif, September 20, 2004 - 3:02 pm UTC

Hi Tom,

Even though I may declare a PL/SQL procedure to take a CLOB as an argument, it looks like I am still limited to a max of 32K characters for the value I pass in as the argument:

kkashif@DEV> get p
1 create or replace procedure p11( p_x in int, p_new_text in clob )
2 as
3 l_clob clob;
4 begin
5 insert into t values ( p_x, empty_clob() ) returning y into l_clob;
6 dbms_lob.write( l_clob, 1, length(p_new_text), p_new_text );
7* end;
kkashif@DEV> /

Procedure created.

kkashif@DEV> exec p11 (1, rpad ('*', 33000, '*'))
BEGIN p11 (1, rpad ('*', 33000, '*')); END;

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

Or am I doing something wrong? If this is the case then I'm not sure how I would be able to pass in a previously existing CLOB that is greater than 32K using a PL/SQL procedure, short of going into a loop that passes in chunks of 32K into this procedure. Or would I have to resort to Java? Thanks.

Kashif

Tom Kyte
September 20, 2004 - 4:21 pm UTC

unless you actually pass in a clob.

yes, the string - 32k -- it'll be implicitly converted to a clob, but the string, 32k

a clob -- 4gig (or terabytes in 10g)


You are passing a string, pass a clob.

CLOBS and HTMLDB

Russell, October 18, 2004 - 12:09 am UTC

Hi Tom,
A colleague of mine is currently converting a MS Access database, into an Oracle (10G) one, using HTMLDB as the front end. Currently he only has access to the Oracle DB through the HTMLDB interface. As such, he has exported all the data out of the Access DB as CSV files, and is using the Data import wizard in HTMLDB to get the data into Oracle. In the Access database he has some data stored in a memo field (which holds approx 65000 characters). He is only new at Oracle and has come to the understanding that a varchar2 will only hold 4000 characters. I am about to suggest he use a CLOB datatype.

Is there anything special that needs to be done, to insert this field into a clob from a csv file?
Will the import data wizard handle this data into a clob?
Do you have any other tips?

Thanks

Russell

Tom Kyte
October 18, 2004 - 11:28 am UTC

Russell,

Tyler here. I hate to say this, but the Spreadsheet Import wizard was not written to handle the volume of data you are trying to insert. While I don't know of any issues of inserting the data into a clob vs varchar2, I would stringly suggest working with the dba's to get access to this instance and to use the Oracle Migration Workbench for Access. You can find the doc and the downloads at:

</code> http://www.oracle.com/technology/documentation/migration.html <code>

Thanks,
Tyler


Printing CLOB content to browser

Reddy P, November 19, 2004 - 2:35 pm UTC

Hi Tom,
I store a lot of html(more than 32000 chars) into CLOB content in my application and print on the browser using "htp.p"(with loop).
This is good until any html tag is not falling exactly at 32000th location. It breaks all my html code.
Is there any way to print CLOB content directly onto browser without using "htp.p"??
If not, how to eliminate the problem of printing CLOB with html tags inside it onto browser without breaking HTML tags at the limit specified.

Thanks



Tom Kyte
November 19, 2004 - 7:15 pm UTC

htp.prn() instead.

Reddy P, December 02, 2004 - 4:49 pm UTC


directory does exist and so does file

A reader, June 28, 2005 - 9:58 am UTC

-rw-r--r--    1 oracle   oracle         28 Jun 28 09:33 projtemp.doc
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jun 28 09:43:34 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> DECLARE
  l_bfile BFILE:=BFILENAME('MntSamba','projtemp.doc');
begin
 dbms_lob.fileopen(l_bfile);
 dbms_lob.fileclose(l_bfile);
end;
/  
DECLARE
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 4

I already created directory it does exist and so does file why isn't this working? 

Tom Kyte
June 28, 2005 - 10:18 am UTC

does this directory exist ON THE SERVER, ON THE MACHINE WITH THE DATABASE.

I see "MntSamba", looks like "network disk", unfortunately, windows is not as easy as unix and you have to bend over backwards to make network disks visible to services.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>
for example

exist on server

A reader, June 28, 2005 - 10:29 am UTC

Yes, i wrote the clob succesfully to this directory. with this file name. The only thing that changed was originally this file was created as a .txt which i changed to .doc could that be whats throwing it off?

Tom Kyte
June 28, 2005 - 11:31 am UTC

if the directory is on the server
and the file is there
and the MntSamba is a directory that was created with:

create directory "MntSamba" as '/.....'

(NOTE: the double quotes)

then this will work -- so, show us the entire thing from start to finish. all of the creates

entire thing

A reader, June 28, 2005 - 1:43 pm UTC

CREATE OR REPLACE procedure sp_write_clob( p_file in varchar2, p_clob in clob)
 as
    l_output utl_file.file_type;
    l_amt    number default 32000;
    l_offset number default 1;
    l_length number default 
                   nvl(dbms_lob.getlength(p_clob),0);
begin
if l_length =0 then
 l_length := 0;
else
     l_output := utl_file.fopen('/mnt/samba_file_stage', p_file, 'w', 32760);
     while ( l_offset < l_length )
     loop
          utl_file.put(l_output,
                  dbms_lob.substr(p_clob,l_amt,l_offset) );
          utl_file.fflush(l_output);
          l_offset := l_offset + l_amt;
     end loop;
     utl_file.new_line(l_output);
     utl_file.fclose(l_output);
end if;
end;
/

CREATE OR REPLACE procedure sp_call_write_clob(p_no in varchar2,
                                               p_level in char,
                    p_table in varchar2)
AS
     projid   number;
     p_clob1   clob;
     p_file1   varchar2(30);
     l_cnt     number :=0;
     len       number;

  
  BEGIN
       for x in ( select proj_memo mylob, proj_id psid
                    from PROJ
                   where proj_no=p_no and user_level=p_level)
       loop
           projid := x.psid;
           p_clob1 := x.mylob;
           p_file1 := 'projid_'|| projid||'.doc';
           len :=0;
           len := dbms_lob.getlength(p_clob1);
           if len is null then
              dbms_output.put_line('EMPTY CLOB');
           else
              l_cnt := l_cnt +1;
              sp_write_clob(p_file1, p_clob1 );
              dbms_output.put_line(l_cnt);
           end if;
       end loop;
       dbms_output.put_line(l_cnt || ' clob files were created from PROJ');
END;
/

SQL> exec sp_call_write_clob('1', 'A', 'PROJ');

PL/SQL procedure successfully completed.

SQL> create directory mount as '/mnt/samba_file_stage/'
  2  ;

Directory created.



SQL> DECLARE
  2  l_bfile BFILE:=BFILENAME('mount','projid_102029.doc');
  3  begin
  4  dbms_lob.fileopen(l_bfile);
  5  dbms_lob.fileclose(l_bfile);
  6  end;
  7  /
DECLARE
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 475
ORA-06512: at line 4







 

Tom Kyte
June 28, 2005 - 2:22 pm UTC

see the double quotes I pointed out -- they were relevant and are the cause


either use 'MOUNT' in the bfile or create directory "mount" as ....



i feel dumb!

A reader, June 28, 2005 - 2:28 pm UTC

THANK YOU, thats been driving me crazy for awhile!

Should all Unicode values work?

Tom, August 03, 2005 - 2:37 pm UTC

Insert of \udb60 is only successful using some versions of ojdbc14.jar
Oracle TAR results in bug 4524029 but development team feedback implies the problem is no such valid character.
Problem results inserting into a VARCHAR2 field in a UTF-8 10g db
Frontend is J2EE/iBatis/Spring app using the Thin driver
Thanks in advance and thanks again for the Great site and erudite information.

Tom Kyte
August 03, 2005 - 5:58 pm UTC

well, is it a valid character in your character set? (i'm not a utf expert by any means!)

Appreciate the confirmation

Tom, August 03, 2005 - 7:20 pm UTC

Many thanks for confirming it is a bad idea to use numeric unicode values except those mapped to valid characters and unless the db character set is guaranteed to remain fixed.

Using unicode numeric values in this manner appears to be common practice in the XML community, so this info will help the application architects understand that it suddenly stopped working when we updated ojdbc14.jar because it never should have worked in the first place.

Not the answer we hoped for, but thanks!!

copy long and clob from history table to production -- 9206

Baqir Hussain, October 10, 2005 - 6:28 pm UTC

We would like to copy few hundred lines of data from a history table "message" to a production table "message" containing both data types (LONG and CLOB) in the table. Copy does not support CLOB.
I would very much appreciate if you please show me with an example that how to achieve it?

Thanks

Tom Kyte
October 10, 2005 - 7:37 pm UTC

only if the table you are copying to can have two CLOBS instead of a clob and long...

if so, you can use insert as select with TO_LOB() on the long.


Else, you'll be writing code if the longs are >32k.

Well, even if they are <32k, you'll be writing code but you can use plsql

copy data from long and clob -- 9206

Baqir Hussain, October 10, 2005 - 9:43 pm UTC

Thanks for the prompt response.
Please show me a simple example how to write a pl/sql code to copy data from LONG and CLOB.
Thanks

Tom Kyte
October 11, 2005 - 6:31 am UTC

for x in ( select * from t )
loop
insert into t2 (...) values ( x.c1, x.clob_col, x.long_col );


CLOB question

atul, November 09, 2005 - 7:32 am UTC

Hi,

We have table with structure (x ,y, z clob).
Genarally avg CLOB length is < 4k but for some tables its > 4k.


We have moved CLOB's to different tablespace with Blocksize 16k.

Now in our queries we update non clob columns but we are using index so CLOB is not getting scanned.

So will it make any difference if we keep CLOB in-line or Out-of-line?

As we are not scanning CLOB at all.

2)For inserts,what will be faster INLINE or OUTOFLINE.

3)We are moving CLOB out in different 16K block tablespace
Do we keep chunk 16k


Thanks,
Atul


Tom Kyte
November 11, 2005 - 9:52 am UTC

1) probably not - if you are index accessing the row, the clob will just "be there" or the lob locator will be there, there will be something there.

2) inline likely - as that is definitely cached and there is less work to do (no lob index to maintain)

3) only you can tell - your choices would be 16 or 32k. which is a better multiple given that a chunk can only be used by one clob - if you have clobs that are say 15k alot - you'll have 16k of "wastage". but if your clobs are either <4000 or greater than 1mb - 32k might well be more efficient.

Inserting a CLOB data

Girish Jahagirdar, November 13, 2005 - 11:44 pm UTC

When I did following steps above mentioned method of CLOB insertion fails and gives error

SQL>create table t(col CLOB);

SQL> create or replace procedure p_in(pv in varchar2) is
  2  begin
  3  insert into t values(pv);
  4  end;
  5  /

Procedure created.

begin
p_in('PRECAUTIONS:  Before using this medication, tell your doctor or pharmacist if you are allergic to it; or to other corticosteroids (e.g., methylprednisolone); or to benzyl alcohol; or if you have any other allergies. This medication should not be used if you have certain medical conditions. Before using this medicine, consult your doctor or pharmacist if you have: untreated active fungal infections. If your have a certain bleeding disorder (idiopathic thrombocytopenic purpura), consult your doctor before injecting this medication into a muscle. Before using this medication, tell your doctor or pharmacist your medical history, especially of: bleeding problems, history of blood clots, brittle bones (osteoporosis), high blood pressure, certain heart problems (e.g., congestive heart failure), diabetes, certain eye diseases (e.g., cataracts, herpes infection, glaucoma), kidney disease, current infections (e.g., tuberculosis, threadworm), severe liver disease (cirrhosis), certain mental/mood conditions (e.g., psychosis, depression), certain muscle/nerve problems (e.g., myasthenia gravis), previously infected joint, seizures, stomach/intestinal problems (e.g., diverticulitis, ulcer, ulcerative colitis), underactive thyroid gland (hypothyroidism), untreated mineral problems (e.g., low potassium or calcium). This medication may make you dizzy; use caution while engaging in activities requiring alertness, such as driving or using machinery. This medication may mask signs of infection or put you at greater risk of developing very serious infections. Report any injuries or signs of infection (e.g., persistent sore throat/cough/fever, pain during urination, muscle aches) that occur while using this medication or within 12 months after stopping it. If you have been on this medication for a long time, your body may not be able to make enough natural steroids while you are under stress due to infection, surgery or injury. Your dose may need to be adjusted. If you have stopped 
using this drug within the past 12 months, you may need to start using it again if your body is under severe stress. Consult your doctor for more details. Tell your doctor immediately if any of these side effects occur: unusual weakness, sudden weight loss, dizziness. Before having surgery, tell your doctor or dentist that you are using this medication or have used it within the last 12 months. Do not have immunizations, vaccinations, or skin tests while you are using this drug unless specifically directed by your doctor. Avoid contact with people who have recently received oral polio vaccine. Avoid exposure to chickenpox or measles infection while using this medication. If you are exposed to these infections, seek immediate medical attention. If you have a history of ulcers or take large doses of aspirin or other arthritis medication, limit alcoholic beverages while using this drug. Alcohol may increase the risk of stomach/intestinal bleeding. If you have diabetes, this drug may increase your blood sugar levels. Check your blood glucose levels regularly as directed by your doctor. Tell your doctor immediately if you have symptoms such as increased thirst and urination. Your anti-diabetic medication or diet may need to be adjusted. Caution is advised when using this drug in children. It may slow down a child''''s growth rate if given for long periods. Monitor your child''''s height and growth rate regularly. Injecting this medication into a muscle is not recommended for children under six years of age. Consult your doctor for more details. This medication should be used only when clearly needed during pregnancy. There have been rare reports of fetal harm when corticosteroids are used during pregnancy. Discuss the risks and benefits with your doctor. Infants born to mothers who have been using this medication for an extended time may have low levels of corticosteroid hormone. Tell your doctor immediately if you notice symptoms such as persistent nausea/vomiting, 
severe diarrhea, or weakness in your newborn. This medication passes into breast milk. While there have been no reports of harm to nursing infants, consult your doctor before breast-feeding.');
end;

SQL> @c:\t.sql;
SP2-0027: Input is too long (> 2499 characters) - line ignored
SP2-0027: Input is too long (> 2499 characters) - line ignored
 10  /
end;
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe 

Tom Kyte
November 14, 2005 - 8:27 am UTC

well, first and foremost - say "thank goodness it failed"


I would not want this junk in my shared pool.


You'll want (need, must, have to) use bind variables for long strings - period.


If this is in a file on the server, use dbms_lob.loadfromfile.

If this is on the client, you'll need a proper programming language (not sqlplus, sqlplus is nothing more than a very very simple command line interface, nothing to develop an application in).


You can use SQLLDR from the client as well to load files into clobs.

Gathering up 32k strings and writing to a file

VA, December 09, 2005 - 8:41 pm UTC

declare
type vc2_t is table of varchar2(32767) index by binary_integer;
vc2_arr vc2_t;
begin
for i in 1..100 loop
vc2(i) := rpad(i,32767,i);
end loop;
end;

Now how can I take all these 32767*100 characters and write them to a file on the server? Do I need to go "via" a CLOB or can I write directly to the file?

Thanks

Tom Kyte
December 10, 2005 - 5:18 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref14146 <code>

utl_file, just make note of the max linesize parameter.

CLOB length more than 45K

Maverick, December 18, 2005 - 12:18 pm UTC

Tom, I want to insert a clob value [received from client] which is more than 45K. I am trying your examples in this thread and still not able to do that. I am getting "String buffer too small" error.

Table script:

CREATE TABLE TEST_CLOB
(
A INTEGER,
B CLOB
)

This is my procedure :

CREATE OR REPLACE procedure p_clob_test(p_a in integer, p_clob in clob)
as
v_clob clob;
begin
insert into test_clob values (p_a,empty_clob()) returning b into v_clob;
dbms_lob.write(v_clob,1,48000,p_clob);
end;
/


And I am calling it here:

declare
v_clob clob:=rpad('*',38000,'*');
begin

p_clob_test(1,v_clob);
end;

This is the error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

Can you show me how to insert more than 32K?

Thanks,


Tom Kyte
December 18, 2005 - 1:05 pm UTC

what is the real client here - plsql has a limit of 32k on the strings involved. The real client (which must be something OTHER than plsql, the thing that calls plsql) would be using an API designed for them - or calling dbms_lob.writeAppend to write the lob data 32k at a time (sort of like writing to a file, well, exactly like that)

Client is .NET Application

A reader, December 19, 2005 - 9:17 am UTC

Tom, a .NET Application will be calling a procedure [it just expects to insert a CLOB value into a table]. So, ultimately, i have to deal with PL/SQL only. So it really doesn't matter what the client is, I was just wondering how could i insert more than 32K. Is there a way of doing it?

Thanks,


Tom Kyte
December 19, 2005 - 9:25 am UTC

what language is the .nyet written in.




oops..Sorry!

A reader, December 19, 2005 - 12:17 pm UTC

It was in c#.

Tom Kyte
December 19, 2005 - 12:24 pm UTC

what API do you use in C#, never used C# myself. Do you have reference to the api you use in that language.

But what difference does it make ??

A reader, December 19, 2005 - 1:03 pm UTC

Tom, I think i lost you somewhere. Client is only calling a procedure and sending CLOB value as input which is more than 32K. I have to manage inserting into table using Stored procedure/functions. So, what difference does it make, what language Client is using? I did not quite follow you there.
All i need is, to insert into table using PL/SQL [but value is sent by client]

Hope i am clear this time [Sorry if i messed it up in my question]

Thanks,

Tom Kyte
December 19, 2005 - 1:11 pm UTC

yes, so, java has a way to stream data into a clob, C has a way, plsql has a way, "C#" must have a way as well.

stored procedure will return empty clob to client, client will stream data into clob much like client would write data into a file.

It is the way clobs work.

Give me pl/sql example

A reader, December 19, 2005 - 3:57 pm UTC

Tom, Let's assume i am writing a stored procedure [forget about client] which has two parameters. one of them is CLOB type. How do i insert into table for clob data >32K?

I am in Oracle 10g, so i am hoping there is a way to insert directly, instead of creating 32K chunks of that input value using dbms_lob.writeappend [like it was mentioned earlier in this thread].

Thanks,

Tom Kyte
December 19, 2005 - 4:28 pm UTC

cannot forget about the client. Impossible to forget about the client.

The client would in general:

insert into t values ( :bv1, empty_clob() ) returning clob_col into :some_bind;
using client API, stream data into :some_bind;
commit;

IF you use plsql
THEN
you are limited to 32k;
ELSIF you_are_using_some_other_language
THEN
the size of the data being streamed after you get the lob locator is
dependent on the features of that language
END IF;


for example, in java with jdbc, I would be using these API's after getting the lob locator (think of LOB's as you would think of a file - you get a FILE pointer and write to it - you get a LOB LOCATOR and you write to it)


</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_api_overview.htm#sthref731 <code>




CLOB object

manish, March 23, 2006 - 1:40 pm UTC

hi tom,

i have a clob object as a datatype and am using dbms_clob.substr(col_name). however, i get an error:

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

i am using oracle 10g and middleware is oracle 8.1.7.

please help asap.

Tom Kyte
March 23, 2006 - 2:00 pm UTC

"middle whare is oracle 8.1.7" does not compute with me.

dbms_clob doesn't exist, assume you mean dbms_lob

substr in that package takes more parameters.

and that error just means the string you assigned the substring into wasn't BIG enough. Your variable you assigned to is too small.

Clob to Clob

Sudershan, March 27, 2006 - 3:56 pm UTC

Oracle 9.2.0.6

Hi Tom,
If I am trying to insert a clob column across a DB link..

like (table a has a clob column)

insert into a
select * from a@remote_db_link;

Are their any limitations that I need to cover for...like
does it only work if the clob is limited to certain length.

Thanks

Tom Kyte
March 27, 2006 - 8:19 pm UTC

it'll either work or not for any size ...

that is one that will work.

invalid LOB locator specified after using DBMS_LOB.CREATETEMPORARY

Ken, August 03, 2006 - 3:02 pm UTC

hi Tom, 

Can you tell me why I am still getting this error?  Here is my function:

CREATE OR REPLACE function blob_to_clob(pi_hist_key in MY.BLOBTABLE.HIST_KEY%TYPE) return clob is

v_file_blob blob ;
v_file_clob clob ;
v_file_size integer := dbms_lob.lobmaxsize;
v_dest_offset integer := 1;
v_src_offset integer := 1;
v_blob_csid number := dbms_lob.default_csid;
v_lang_context number := dbms_lob.default_lang_ctx;
v_warning integer;


begin


select ole_object_blob into v_file_blob  -- column is a blob
from MY.BLOBTABLE
where hist_key = pi_hist_key;

DBMS_LOB.CREATETEMPORARY(v_file_clob, TRUE,2);


dbms_output.put_line ('3');
dbms_lob.convertToClob( v_file_clob,
v_file_blob,
v_file_size,
v_dest_offset,
v_src_offset,
v_blob_csid,
v_lang_context,
v_warning);

if v_warning = 0 then

   return v_file_clob;
else 

  raise value_error ;

end if;
exception when value_error then
raise;
when others then raise;

end;
/


AND HERE is when I call the above function:

SQL> l
  1  DECLARE
  2    RetVal clob;
  3    PI_HIST_KEY NUMBER;
  4  BEGIN
  5  
  6    PI_HIST_KEY := 3270;
  7    RetVal := PDWPRES.BLOB_TO_CLOB ( PI_HIST_KEY );
  8  dbms_output.put_line('RETVAL:' || RETVAL);
  9* END;
SQL> /
here
3
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "PDWPRES.BLOB_TO_CLOB", line 42
ORA-06512: at line 7


 

Tom Kyte
August 03, 2006 - 4:56 pm UTC

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

Table created.

ops$tkyte%ORA9IR2> insert into t values ( to_char(ascii('H'),'fmxx' )||
  2                         to_char(ascii('e'),'fmxx' )||
  3                         to_char(ascii('l'),'fmxx' )||
  4                         to_char(ascii('l'),'fmxx' )||
  5                         to_char(ascii('o'),'fmxx' )||
  6                         to_char(ascii(' '),'fmxx' )||
  7                         to_char(ascii('W'),'fmxx' )||
  8                         to_char(ascii('o'),'fmxx' )||
  9                         to_char(ascii('r'),'fmxx' )||
 10                         to_char(ascii('l'),'fmxx' )||
 11                         to_char(ascii('d'),'fmxx' ) );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> CREATE OR REPLACE function blob_to_clob return clob
  2  is
  3     v_file_blob blob ;
  4     v_file_clob clob ;
  5     v_dest_offset integer := 1;
  6     v_src_offset integer := 1;
  7     v_lang_context number := dbms_lob.default_lang_ctx;
  8     v_warning integer;
  9  begin
 10      select x into v_file_blob from t;
 11     DBMS_LOB.CREATETEMPORARY(v_file_clob, TRUE,2);
 12     dbms_lob.convertToClob( v_file_clob,
 13                       v_file_blob,
 14                       dbms_lob.lobmaxsize,
 15                       v_dest_offset,
 16                       v_src_offset,
 17                       dbms_lob.default_csid,
 18                       v_lang_context,
 19                       v_warning);
 20     return v_file_clob;
 21  end;
 22  /

Function created.

ops$tkyte%ORA9IR2> exec dbms_output.put_line( blob_to_clob );
Hello World

PL/SQL procedure successfully completed.


works for me - I can say that this:

exception when value_error then
raise;
when others then raise;

is an UTTERLY BAD idea as it "moves the error line number" and does absolutely nothing else.  delete that part of the code and see if that doesn't help you find your actual error (as you'll get the REAL line number of the error, not the exception block!!!  You don't even know if it is the temporary lob locator here at all!!)


help me reproduce - with an entirely complete, self contained - yet as small as possible - test case.  See how small you can make it - all of the "extra stuff" should just fall away. 

Problem was with data.

ken, August 03, 2006 - 7:14 pm UTC

Thank you Tom.

I found that if the blob is populated with text, then it will return the text correclty, however, if the data in the blob column is null (via DBMS_LOB.getLength is null), then I got the error ORA-22275, invalid LOB locator specified.

The actual problem is I have a long raw column that I wish to convert to clob.  First I tried with 

ALTER TABLE myTable Modify myLongRaw CLOB;
/

but I got Ora-22858: invalid alteration of datatype

I think this is because the rule: LONG RAW -> BLOB, and LONG -> CLOB.

So, I thought I can convert the table to BLOB first and then to CLOB, hence my first question.

Now it seems I can ONLY use DBMS_LOB.ConvertToClob when the column is populated, if the data is null, I get the Ora-22775 error, is this right?

If so, I can convert the non-null rows first, then leave the NULL value rows alone.  But is there a better way to do it ?


Here are the steps I took to realize this:

SQL> create table t ( x blob )
  2  /

Table created.

SQL>  insert into t values ( to_char(ascii('H'),'fmxx' )||
  2                               to_char(ascii('e'),'fmxx' )||
  3                               to_char(ascii('l'),'fmxx' )||
  4                               to_char(ascii('l'),'fmxx' )||
  5                               to_char(ascii('o'),'fmxx' )||
  6                               to_char(ascii(' '),'fmxx' )||
  7                               to_char(ascii('W'),'fmxx' )||
  8                               to_char(ascii('o'),'fmxx' )||
  9                               to_char(ascii('r'),'fmxx' )||
 10                               to_char(ascii('l'),'fmxx' )||
 11                              to_char(ascii('d'),'fmxx' ); )
 
1 row created.

SQL> update t set x = null;

1 rows updated.

SQL> update t set x = null

SQL> drop table t purge;

Table dropped.

SQL>  create table t ( x blob );

Table created.

SQL>  insert into t values ( to_char(ascii('H'),'fmxx' )||
  2                               to_char(ascii('e'),'fmxx' )||
  3                               to_char(ascii('l'),'fmxx' )||
  4                               to_char(ascii('l'),'fmxx' )||
  5                               to_char(ascii('o'),'fmxx' )||
  6                               to_char(ascii(' '),'fmxx' )||
  7                               to_char(ascii('W'),'fmxx' )||
  8                               to_char(ascii('o'),'fmxx' )||
  9                               to_char(ascii('r'),'fmxx' )||
 10                               to_char(ascii('l'),'fmxx' )||
 11                              to_char(ascii('d'),'fmxx' ) );

1 row created.

SQL> update t set x = null;

1 row updated.

SQL> CREATE OR REPLACE function blob_to_clob2 return clob
  2     is
  3         v_file_blob blob ;
  4          v_file_clob clob ;
  5          v_dest_offset integer := 1;
  6          v_src_offset integer := 1;
  7          v_lang_context number := dbms_lob.default_lang_ctx;
  8          v_warning integer;
  9       begin
 10           select x into v_file_blob from t;
 11          DBMS_LOB.CREATETEMPORARY(v_file_clob, TRUE,2);
 12         dbms_lob.convertToClob( v_file_clob,
 13                           v_file_blob,
 14                           dbms_lob.lobmaxsize,
 15                           v_dest_offset,
 16                           v_src_offset,
 17                           dbms_lob.default_csid,
 18                           v_lang_context,
 19                           v_warning);
 20          return v_file_clob;
 21     end;
 22  /

Function created.

SQL>  exec dbms_output.put_line( blob_to_clob2);
BEGIN dbms_output.put_line( blob_to_clob2); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 683
ORA-06512: at "PDWPRES.BLOB_TO_CLOB2", line 12
ORA-06512: at line 1

 

Tom Kyte
August 04, 2006 - 7:36 am UTC

why not just make line 11 of the routine be:

if v_file_blob is null
then
v_file_clob := NULL;
else
existing code
end if;



A reader, August 07, 2006 - 10:48 am UTC

Hi Tom,

We have designed a CLOB column as out of line.
When we do updates on that column, it results in
high consistent gets, is there a work around for this?

Thanks in advance!

Tom Kyte
August 07, 2006 - 9:37 pm UTC

example?

how high is "high"
:)

clob variable

Vishal, January 30, 2008 - 3:26 am UTC

Hi Tom,
my application is on .net,c# and oracle 10g as a backend.
one of my procedure ,i am concatinating a string which is more then 32000 ,and writing in a clob varible ,it could contain more then one lacs charecter.i can not insert into any table clob() columns because of some restrictions.

so my question is ,is there will be some memory issue in future using this?? because i am concatnating all string in clob variable.
here is code ->


CREATE OR REPLACE PROCEDURE Suppliers_Report_test1(general IN VARCHAR2,v_projid in number,o_stmt out clob) IS
strParHeaders VARCHAR2(32000);
stmt varchar2(32000);
stmt1 clob;
CURSOR headers IS
SELECT short_desc,paramid FROM GENERAL_PARAMETERS
WHERE setid = 'SUPPLIERS'
ORDER BY paramorder ;
--v_projid NUMBER:=Userinfo.GETPROJECTID;
CURSOR detailkeys IS
SELECT p.detailkey
FROM PARAM_VALUES p
WHERE p.PROJECTID = 0
AND p.setid = 'SUPPLIERS'
AND p.paramid = 1
AND (general = 'Y' OR
0 <> (SELECT COUNT(*) FROM PARAM_VALUES p2 WHERE projectid = v_projid
AND setid = 'PRODUCTION'
AND paramid = 20
AND value = p.value
AND status = 'A'))
ORDER BY p.value ;
v_conta NUMBER;
v_value VARCHAR2(32000);
v_nameProj VARCHAR2(1000);
app_server VARCHAR2(100);
ext_url VARCHAR2(1000);
BEGIN
stmt1:=to_clob(' ');
stmt:=' ';
stmt:=stmt||'<td><font color="#ffffff">=SUM(0,0)</font></td>';
stmt:=stmt||'<table><tr><td>.<img src="../images/hplogo.gif"></td><td><font size=+3 color=#2200aa>e-SCPD Report</font></td><td colspan="4" bgcolor=#2200aa>&nbsp;</td></tr></table>';
stmt:=stmt||'<table><tr><td>&nbsp;</td><td>HP - Confidential ('||SYSDATE||')</td><td colspan = 2><font size=+3 color=#2200aa>Suppliers List</font></td></tr></table><br><br>';
IF general = 'N' THEN
SELECT projectname INTO v_nameProj FROM PROJECTS WHERE projectid = v_projid;
ELSE
v_nameProj := 'All projects';
END IF;
stmt:=stmt||'<table ><tr><td>Project:<span align="left"><font color="#0000ff"> '||UPPER(v_nameProj)||'</font></span></td></tr></table>';
stmt:=stmt||'<br>';
stmt:=stmt||'<br><font size=+1>Navigation Options:</font><br>';
stmt:=stmt||'<br>';
stmt:=stmt||'<a href="'||app_server||'escpd/app/gui.welcome">back to e-SCPD main screen</a><br>';
stmt:=stmt||'<br><br><br>';
-- Header parameters
stmt:=stmt||'<table><tr>';
FOR h IN headers LOOP
stmt:=stmt||'<td style> "border:solid" align = center color=green><font size = -1> ' || UPPER(h.short_desc) || '</font></td>';
dbms_lob.writeappend(stmt1,length(stmt),stmt);
stmt:=' ';
END LOOP;
stmt1:=stmt1||'</tr>';
o_stmt:=stmt1;
stmt1:=' ';
stmt:=' ';
FOR d IN detailkeys LOOP
stmt:=stmt||'<tr>';
FOR h1 IN headers LOOP
SELECT COUNT(*) INTO v_conta FROM PARAM_VALUES WHERE projectid = 0 AND setid = 'SUPPLIERS'
AND detailkey = d.detailkey
AND paramid = h1.paramid;
IF v_conta = 1 THEN
SELECT value INTO v_value
FROM PARAM_VALUES
WHERE projectid = 0
AND detailkey = d.detailkey
AND paramid = h1.paramid;
stmt:=stmt||'<td style = "border:solid"> '||UPPER(v_value)||'</td>';
dbms_lob.writeappend(stmt1,length(stmt),stmt);
stmt:=' ';
ELSE
stmt:=stmt||'<td style = "border:solid"> </td>';
dbms_lob.writeappend(stmt1,length(stmt),stmt);
stmt:=' ';
END IF;
END LOOP;
stmt:=stmt||'</tr>';
dbms_lob.writeappend(stmt1,length(stmt),stmt);
END LOOP;
o_stmt:=stmt1;
dbms_output.put_line(substr(o_stmt,5000,200));
dbms_output.put_line(length(o_stmt));
END ;



Vishal


blob to clob

A reader, June 23, 2008 - 4:42 pm UTC


HOW CAN WE INSERT & RETRIEVE THE IMAGES FROM DATA BASE?

A reader, September 08, 2008 - 4:14 am UTC

Hi tom,
How can we insert & retieve images from data base with out compressing the image.
Tom Kyte
September 08, 2008 - 4:03 pm UTC

read the original answer - there is no compression.

if you are asking about forms (probability almost certainly 100%) - you'll want to use the forums on otn.oracle.com - I haven't touched forms since 1995 myself.

Oracle Forms

searchingoracle.com, September 08, 2008 - 7:58 pm UTC

Hi Tom,

I think that explains why Oracle forms has not changed much after 1995 :-).

Andrew, May 18, 2009 - 9:21 pm UTC

Tom,
how the situation has changed since 04?
Here is what i'm trying to achieve:
Thre are 2 sites. View on the local site is defined as select * from the remote site's table.
I'm trying to get a "instead of " trigger (defined on that view) to insert/update the blob column on the remote site's table.
And so far - no luck.
Maybe you can advise?

And yes, files are larger than 32K.
Tom Kyte
May 23, 2009 - 11:24 am UTC

not going to work. not with a magical trigger (i hate triggers).

andrew, May 23, 2009 - 10:46 pm UTC

"Magical" trigger? What do you mean?

Why do you hate triggers?
Tom Kyte
May 26, 2009 - 8:31 am UTC

https://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Almost (remember everyone - the word is "almost") every time I see a trigger implementation - to do "automagic stuff in the background, to trick the application into thinking it is doing one thing but it is really doing another" or to do "integrity checks"

they are done wrong.

Magic is never the way to go.

Our developed code should be as straightforward as possible - triggers make things "magic"

A reader, May 27, 2009 - 12:45 am UTC


Thank you Tom. Interesting point of view. Article is useful (as always) and is full of examples and explanations. Always learn from you.
Andrew

Updating clob with still larger data

Naren, April 02, 2010 - 3:52 pm UTC

Hi Tom,
Is there a way to update a clob with string content greater than 64K?
Please let me know if this is possible on a client machine - to update the clob with the contents of a local file - WITHOUT having access to mount to server filesystem.

Thank You Very much!
Tom Kyte
April 05, 2010 - 1:20 pm UTC

what would mounting the server file system do for you? That is very confusing to me - you never need to mount the client filesystem to update something in the database.


yes, a client application can definitely update a clob to be ANY SIZE it wants to. If you are using plsql as an interface, you would just create a procedure that takes a clob as input and then uses dbms_lob.Append.

if using some other language, you would review the API's for that language to determine the right function calls to make.


clob

A reader, April 05, 2010 - 3:11 pm UTC


binding more than 4k bytes to lob and long column

Pranav, February 25, 2013 - 3:14 am UTC

Hi,
I executed the following queries 
SQL> desc testtab3;
 Name                                      Null?    Type
 C01                                                NUMBER
 C02_CLOB                                           CLOB
 C03_LONG                                           LONG

SQL> create or replace procedure p( p_x in int, p_new_text in varchar2,p_long_data in varchar2)
  2  as
  3  begin
  4  insert into testtab3 values(p_x,p_new_text,p_long_data);
  5  end;
  6  /

Procedure created.

SQL> exec p(1,lpad('a',32000,'a'),lpad('b',32000,'b'));
BEGIN p(1,lpad('a',32000,'a'),lpad('b',32000,'b')); END;

*
ERROR at line 1:
ORA-22295: cannot bind more than 4000 bytes data to LOB and LONG columns in 1
statement
ORA-06512: at "SCOTT.P", line 4
ORA-06512: at line 1

How to add 32k data in both lob and long columns.

Tom Kyte
February 25, 2013 - 11:33 am UTC

You'll have to two step it.


ops$tkyte%ORA11GR2> !oerr ora 22295
22295, 00000, "cannot bind more than 4000 bytes data to LOB and LONG columns in 1 statement"
// *Cause:  An attempt was made to bind data more than 4000 bytes of data to both
//          LOB and LONG columns in the same insert or update statement. You can 
//          bind more than 4000 bytes of data to either a LONG column or one or
//          more LOB columns but not both.
// *Action: Bind more than 4000 bytes of data to either the LONG column or one or
//          more LOB columns but not both.

ops$tkyte%ORA11GR2> set echo on
ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> drop table t;

Table dropped.

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

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_data long := rpad( 'a', 32000 );
  3  begin
  4          insert into t(x) values( l_data );
  5          update t set y = l_data;
  6  end;
  7  /

PL/SQL procedure successfully completed.


Clob where clause

Abhisek, June 11, 2013 - 6:37 am UTC

Hi Tom,

How can we use a CLOB column for where clause? if it is smaller than 4000 characters I believe we can use to_char(clob_col) but what happens when if it is > 4K?
Tom Kyte
June 18, 2013 - 2:43 pm UTC

you can use things like dbms_lob.substr() = :x, dbms_lob.instr() = :y and so on.

what are you trying to do exactly?

Issue with MERGE statement when updating/Inserting CLOB field

Sridhar, June 21, 2013 - 1:53 pm UTC

Hi Tom,

We are getting the data from a webpage and insert/updating a CLOB field. When i tried using MERGE statement, got a ORA-01461 but I used usual Insert/Update statement if worked fine. May I know what could cause the issue? Isn't MERGE supposed to work the same way as (INSERT/UPDATE)

Test case:
create table test4 (col1 number, col2 clob)

create or replace procedure test3 (p_num number, p_clob varchar2)
is
begin
merge into test4 t
using (select p_num col1, p_clob col2 from dual) s
on (t.col1 = s.col1)
when matched then
update set t.col2 = s.col2
when not matched then
insert (col1, col2) values(s.col1, s.col2);
commit;
end;

--Throws ORA:01461: can bind a LONG value only for insert into a LONG Column
begin
test3(1,rpad('*',32000,'*'));
end;

create or replace procedure test3 (p_num number, p_clob varchar2)
is
begin

update test4
set col2 = p_clob
where col1 = p_num;

if sql%notfound then
insert into test4 values (p_num, p_clob);
end if;
/*
merge into test4 t
using (select p_num col1, p_clob col2 from dual) s
on (t.col1 = s.col1)
when matched then
update set t.col2 = s.col2
when not matched then
insert (col1, col2) values(s.col1, s.col2);*/
commit;
end;

begin
test3(1,rpad('*',32000,'*'));
end;

Tom Kyte
July 01, 2013 - 5:00 pm UTC

because you are trying to stuff more then 4000 bytes into a VARCHAR2 in sql and in 11gr2 and before - 4000 bytes is the maximum for varchar2 in SQL (in 12c it can be up to 32k...)


use the right type:

ops$tkyte%ORA11GR2> create or replace procedure test3 (p_num number, p_clob <b>clob </b>)
  2  is
  3  begin
  4  merge into test4 t
  5  using (select p_num col1, p_clob col2 from dual) s
  6  on (t.col1 = s.col1)
  7  when matched then
  8  update set t.col2 = s.col2
  9  when not matched then
 10  insert (col1, col2) values(s.col1, s.col2);
 11  commit;
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> begin
  2  test3(1,rpad('*',32000,'*'));
  3  end;
  4  /

PL/SQL procedure successfully completed.


Knowledge Master

Prem, June 26, 2013 - 12:36 pm UTC

It's a wonderful forum which is helping all over the world who were into I.T Sector and improve and build their professional skills individually.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here