Helena Markova, March 22, 2001 - 3:48 am UTC
How about CLOB as parameter?
Joshua Perry, April 11, 2002 - 6:27 pm UTC
So, how does this apply to CLOBs as parameters to a Packaged Procedure?
As an example, if I have a procedure that will parse & process an XML document, how do you perform the "CALL(SOME_PACKAGE.SOME_PROC('<XML>...</XML>'))" from the client if the XML document is larger than 4K?
I am using ADO to execute Procedure call from NT service against a 9i install on SUN UNIX. Is there a way to do this with the ADO or am I forced to use Embedded SQL (Pro*C/C++)? If the later, how would I perform the embedded execution?
Thanks!!
April 11, 2002 - 7:34 pm UTC
The XML document can be 32k (the varchar2 limit in PLSQL is 32k, not 4000 bytes)...
If you have >32k -- i would be tempted to use a global temporary table, stream the data into that and call a procedure to work against that clob/blob.
you can do all of this from VB. No C needed.
How about CLOB as parameter?
Joshua Perry, April 12, 2002 - 2:09 pm UTC
Thanks for the response...
I appreciate what you say about VARCHAR2 being 32K, but when I make any CALL('LITERAL') from C++/ADO where the LITERAL is larger than 4K I get the error that string literal is too large. When you say that VARCHAR2 is 32K does that only apply when inside a PLSQL block/Procedure?
You used the term "stream," does this imply that there is another way to load this XML to CLOB in the Global Temp. Table, or must I do the -- Insert Empty_CLOB & WRITEAPPEN() method?
Thanks Again!
April 12, 2002 - 3:34 pm UTC
You need to use BIND VARIABLES -- for things from 1 byte to 32kbytes.
You NEED to use BIND VARIABLES regardless.
BIND VARIABLES do not have the limit of a character string literal.
Last Question...
Joshua Perry, April 12, 2002 - 6:17 pm UTC
Forgive me for drawing this out, but does that then equate to input parameters with ADO?
By the way...Your site is one of the best sources for Oracle I have encounter as of yet!
Thanks
April 12, 2002 - 8:46 pm UTC
I know nothing about ADO or VB programming other then I am 100% sure it supports bind variables.
If you look at
</code>
http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>
someone gave me a VB and/or ASP example that uses bind variables for sample code.
CLOB vs BLOB to store text string
Frank, September 15, 2004 - 3:35 pm UTC
We have a column need to store following IDs up to 3000 separated by coma.
36480288239283, 36480288239282, 36480288239285, ...
In the design discussion, one person in our DBA team prefer BLOB to CLOB. He thinks the BLOB is more efficient because it can avoid one layer of conversion performed by Oracle. I did not follow what you refers to, can not find any reference you mentioned.
In your rich knowledge of Oracle, which data type you prefer in this situation.
Thanks a lot!
September 15, 2004 - 4:11 pm UTC
that would be "wrong". CLOB is the only type you should consider for a string.
you would spend all of your time converting "raw binary stuff" into something you could actually use.
but -- actually, it just looks "so wrong wrong wrong" to me to store what is apparently a list of numbers encoded in a big string with comma's too
seems like you want an index organized table personally.
CLOB vs BLOB to store text string
Frank, September 15, 2004 - 3:53 pm UTC
Sorry about the typo. Following is what I really mean.
We have a column need to store following IDs up to 3000 separated by coma.
36480288239283, 36480288239282, 36480288239285, ...
In the design discussion, one person in our DBA team prefers BLOB to CLOB. He thinks the BLOB is more efficient because it can avoid one layer of conversion performed by Oracle. I did not follow what he refers to, cannot find any
reference to support his opinion.
In your rich knowledge of Oracle, which data type you think more appropriate in this situation.
Thanks a lot!
Thanks!
Frank, September 15, 2004 - 8:53 pm UTC
I agree with you completely! CLOB is my choice too. However, I did not have a reference to convince him at that time. Thanks a lot!
September 15, 2004 - 9:12 pm UTC
no, you are disagreeing with me.
clob is wrong
a detail table with a foreign key to the parent, a number column and *maybe* a sequence to keep order
create table ids ( fk references parent_table, seq number, VAL number,
primary key(fk,seq) ) organization index;
Thanks again!
Frank, September 15, 2004 - 9:45 pm UTC
Thanks again Tom for your detail explanation! I really appreciate it. The CLOB column should be split out as a separate table. However, for some reason, the CLOB has been used in this way as a semi-standard (not a good one) in this company. Convincing them is going to be tough ... :(
I will try ;) ...
Urgently ::: STORE/Load & DISPLAY/retrieve file from node/client
Tanweer, September 18, 2004 - 7:45 am UTC
I have Oracle Database in window 2000 SERVER and the other node is Window 2000 Professional and wants to STORE & retrieve/DISPLAY file(e.g. : .doc, .txt, .PDF etc
..) into the database from nodes .
That means STORE/LOAD file from NODE (i.e PC1) to SERVER and DISPLAY file FROM SERVER to NODE (i.e PC2)
I have created the Package in Server from the Client Side.....
SQL*Plus: Release 8.0.6.0.0
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 Production
SQL> desc my_docs;
Name Null? Type
------------------------------- -------- ----
DOC_ID ------------------------NUMBER
BFILE_LOC --------------------BINARY FILE LOB
DOC_TITLE --------------------VARCHAR2(255)
DOC_BLOB ---------------------BLOB
SQL> set serveroutput on
SQL> exec mydocs.doc_dir_setup
PL/SQL procedure successfully completed.
SQL> exec mydocs.list('\aaa\testfile.txt');
PL/SQL procedure successfully completed.
When I execute the Following Query from node then I got the error
Note : The file aaa.doc exists into the node directory
SQL> exec mydocs.load('\aaa\testfile.txt',1);
BEGIN mydocs.load('\aaa\testfile.txt',1); END;
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The filename, directory name, or volume label syntax is incorrect.
ORA-06512: at "SYS.DBMS_LOB", line 672
ORA-06512: at "ARCH.MYDOCS", line 39
ORA-06512: at line 1
The Package Code is given below which I use
CREATE TABLE my_docs
(doc_id NUMBER,
bfile_loc BFILE,
doc_title VARCHAR2(255),
doc_blob BLOB DEFAULT EMPTY_BLOB() );
CREATE OR REPLACE PACKAGE
mydocs
AS
PROCEDURE doc_dir_setup;
PROCEDURE list (in_doc IN VARCHAR2);
PROCEDURE load (in_doc IN VARCHAR2,
in_id IN NUMBER);
PROCEDURE search (in_search IN VARCHAR2,
in_id IN NUMBER);
END mydocs;
/
CREATE OR REPLACE PACKAGE BODY
mydocs
AS
vexists BOOLEAN;
vfile_length NUMBER;
vblocksize NUMBER;
PROCEDURE doc_dir_setup IS
BEGIN
EXECUTE IMMEDIATE
'CREATE DIRECTORY DOC_DIR AS'||
'''"C:\Temp"''';
END doc_dir_setup;
PROCEDURE list (in_doc IN VARCHAR2) IS
BEGIN
UTL_FILE.FGETATTR('DOC_DIR',
in_doc,
vexists,
vfile_length,
vblocksize);
IF vexists THEN
dbms_output.put_line(in_doc||' '||vfile_length);
END IF;
END list;
PROCEDURE LOAD (IN_DOC IN VARCHAR2, IN_ID IN NUMBER)
IS
TEMP_BLOB BLOB := EMPTY_BLOB();
BFILE_LOC BFILE;
BEGIN
DBMS_LOB.CREATETEMPORARY(TEMP_BLOB,TRUE,DBMS_LOB.SESSION);
BFILE_LOC := BFILENAME( 'DOC_DIR', IN_DOC);
DBMS_LOB.FILEOPEN( BFILE_LOC );
DBMS_LOB.LOADFROMFILE( TEMP_BLOB, BFILE_LOC, DBMS_LOB.GETLENGTH(BFILE_LOC));
DBMS_LOB.FILECLOSE( BFILE_LOC );
INSERT INTO MY_DOCS (DOC_ID, BFILE_LOC, DOC_TITLE, DOC_BLOB)
VALUES (IN_ID, BFILE_LOC, IN_DOC, TEMP_BLOB);
COMMIT;
END LOAD;
PROCEDURE search (in_search VARCHAR2,
in_id NUMBER) IS
lob_doc BLOB;
Pattern VARCHAR2(30);
Position INTEGER := 0;
Offset INTEGER := 1;
Occurrence INTEGER := 1;
BEGIN
Pattern := utl_raw.cast_to_raw(in_search);
SELECT doc_blob INTO lob_doc
FROM my_docs WHERE doc_id = in_id;
DBMS_LOB.OPEN (lob_doc, DBMS_LOB.LOB_READONLY);
Position := DBMS_LOB.INSTR(lob_doc, Pattern, Offset, Occurrence);
IF Position = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pattern not found');
ELSE
DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
END IF;
DBMS_LOB.CLOSE (lob_doc);
END search;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
END mydocs;
/
September 18, 2004 - 10:38 am UTC
I keep deleting this but you keep putting it back.
I already answered this on the first page you put it. please use "your questions" from the home page and find it.
(the file must be on the DATABASE SERVER TO USE THESE FUNCTIONS, you use TEMP on the server but reference aaa in the call, your own list function shows *the file is not there*, i don't get it)
STORE/Load & DISPLAY/retrieve file from node/client
Tanweer, September 21, 2004 - 6:50 am UTC
Hi,
Sorry to take your valuable suggestion
It means where I build the Directory( i.e Server) there I placed the files.
Is is not possible to LOAD file (i.e : .doc,.txt,.xls etc) from the Client(PC1) to SERVER also RETRIEVE them from SERVER to the Client(PC1) ?
Thanks
insert HTML Document into clob or blob
Mokhtar, November 15, 2005 - 10:37 am UTC
i try to insert HTML Document into clob or blob and i got an error message.
November 15, 2005 - 11:44 am UTC
sorry?
I mean, what else could anyone say. This is sort of like going to a mechanic - without your car - and saying:
It won't start.
.....
Selecting BLOB and Inserting BLOB
Maverick, February 28, 2006 - 1:59 pm UTC
Tom, Can you Give me a simple example on how to insert a blob into a table [whose column is of BLOB data type] and again selecting from the table?
I need to pass BLOB Value as on OUT Parameter to Client [JAVA or .NET]. Can this be done like any other type [eg:ref cursor] or needs a special treatment?
Thanks for your help in advance.
March 01, 2006 - 7:54 am UTC
reader
A reader, August 25, 2006 - 9:15 am UTC
If the column needs to store less than 4000 characters in 9.2.0.7, what is the advantage of one over the other varchar2(4000) vs. clob
August 27, 2006 - 8:25 pm UTC
if the column needs less than 4000 characters, it would not be wise to use anything other than a varcahr2.
the clob would just lend additional overhead, being a slightly more "complex" type.
Should we store as BLOB?
Lakshmi, October 11, 2006 - 8:42 am UTC
Hi Tom
Our requirement is to store the following data:
PatterId
Degree
ValueForthatDegree
We have Degrees 1 to 180 and 181 such patterns. (180 X 181 values). The client will access this complete set of data during startup and stores in their local memory
Which is the best way store such data? Is BLOB is the right choice?
Thanks
Lakshmi
October 11, 2006 - 9:08 am UTC
sorry, does not compute. not really sure what you are storing or retrieving here given the description. not immediately clear anyway.
Earlier response is still good
John Flack, October 11, 2006 - 10:00 am UTC
Your previous response for the use of BLOB or CLOB to store what is obviously tabular data is still good. Look folks, if the data you want to store has discrete values, it belongs in a separate table, NOT concatinated together in a single big field, be it VARCHAR, BLOB or CLOB. So you want to retrieve the data all at once and work with it locally? Then do it - retrieve it into an array - this works in PL/SQL, in C, in COBOL, in pretty much any language that can do an Oracle SELECT.
Should we store as BLOB ...
A reader, October 12, 2006 - 12:04 am UTC
The data to be stored in all numbers (PatterId Degree ValueForthatDegree)
The data is generated by some tool and is to be stored in database. The data is required by the client all in one shot. The data is kind of read-only data and it will not updated anytime once it is loaded in DB.
My confusion isthat for such a requirement which is the right way to design the table.
It would be nice if you can tell about when we should we go for BLOB. Is it ONLY if the data is unstructured?
Thanks
Lakshmi
October 12, 2006 - 8:12 am UTC
one cannot "design a table" until one "understands the data". All I know is you have a "bunch of numbers", nothing more, nothing less.
use blobs to store binary large objects - like a word document.
A reader, October 13, 2006 - 4:28 am UTC
Got your point !!
Thanks for you time anyway!
View CLOB data
A reader, February 07, 2008 - 2:44 pm UTC
Tom,
I have nearly similar question which started this thread, but the database version is 10gr2. Can I use CLOB in WHERE clause to search for pieces of data? Does LIKE operator work for CLOB?
Thanks
February 07, 2008 - 3:50 pm UTC
you can, but be prepared to get coffee.
It won't be fast.
You might consider INDEXING IT and using contains (create a text index, then you can use the full power of text searching on it)
Coffee
SeánMacGC, February 07, 2008 - 6:08 pm UTC
you can, but be prepared to get coffee.
LOL :o)
clob
A reader, February 07, 2008 - 11:13 pm UTC
excellent
LOB
A reader, March 30, 2010 - 6:58 pm UTC
blobs
A reader, April 17, 2010 - 1:19 pm UTC
BLOB CLOB or multiple row
Shinto Thomas, April 26, 2010 - 7:13 am UTC
Hello Tom,
Here we have a scenario where we need to store a string which sometimes goes beyond 4000 char. Currently we are using varchar2(4000) for it and since it can store only up to string of length 4000 we are planning to change it to clob, blob or multiple rows based on the performance.
Exact scenario is to store the gap in the sequences of the files received by us in the database. Like say we received a file with sequence "1" and then we receive a file with sequence "5" then we need to store in the database a gap as string "2,3,4".
Also as and when we receive the files with sequence "2" , "3" or "4" we will be deleting it from database.
With smaller gaps we dont have any issues mangaing this in a table with datatype varchar2(4000). However when after sequence "1" we receive file with sequence "500" we are not able to store the gap string in the table with data type varchar2(4000).
Please advice if we should use blob, clob or even multiple rows based on some key?
FYI this scenario is roaming related and sequences or gap are maintained by the network operators.
Thanks,
Shinto
April 26, 2010 - 9:05 am UTC
you should not be storing the 'gap' as a string, you obviously (to me) either meant to
a) NOT store a gap, the lack of data is as telling as the presence of data in this case. I have no idea why you would stort 2,3,4 - just store 1, just store 5 and it is rather obvious that 2,3,4 are not there.
b) if you have a valid reason for storing the gap, store a ROW PER MISSING object - store 2,3,4 - that way when 3 comes along - it is trivial to update 3.
what should you use if want to split more that 4k bytes in word by ,
siya, July 30, 2018 - 1:10 pm UTC
????
July 30, 2018 - 4:30 pm UTC
I'm not sure what you're asking here...