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!!

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!

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

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!

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!

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!

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 ;) ...

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;
/
 

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

i try to insert HTML Document into clob or blob and i got an error message.

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.

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

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

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.

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

Got your point !!

Thanks for you time anyway!

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

you can, but be prepared to get coffee.

LOL :o)

excellent

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

????