Solved

Saving_xml File

Posted on 2008-06-17
17
718 Views
Last Modified: 2013-12-19
A client will use an http to pos several xml files. it will be posted to oracle database stored procedure via mod_plsql.

by default the pl/sql gateway saves those files as a BLOB in DOCUMENTS_TABLE and saves a pointer (file name/number) to the other table TESTS_TABLE where i save the data.

1.  Shall I keep the xml file as a BLOB data type in Documents table and access it from there?
Would i be limited in any way displaying the file or searching it etc..

2.  Would it be useful to copy this BLOB file convert it into CLOB and stick it into TESTS_TABLE.

3. How do you do #2.

thanks,

0
Comment
Question by:sam15
  • 9
  • 8
17 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
1 - you'll be limited because you'll have to convert the BLOB to a CLOB at extraction time to use it.

2- Yes, or possibly convert it to an XMLTYPE column if you can be sure it will be valid XML.

3 -  dbms_lob.converttoclob  will convert a BLOB (or a subset of it if you want) to a CLOB
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oops, just remembered.  9i doesn't have the converttoclob function.

you can use utl_raw.cast_to_varchar2  to pull subsets of the blob data out and append it to the the clob in 32K varchar2 chunks
0
 

Author Comment

by:sam15
Comment Utility
1. why i am limited? yes i have to convert on extraction time using a function or procedure? is it better to store it as CLOB in database than the default blob oracle does using mod_plsql.

I am inclined to store it as CLOB too. i think it is cleaner since it is xml text file.

2. I can't be sure that the xml sent via http post  is valid XML. would xmltype reject any file that is not valid?

3. I have oracle 9.2.0.2. but it does have converttoclob in dbms_lob package.
Do you have an example of how to use it?

I think you were referingto this but it is too slow. i ran it for 15 records on small xml files and it took a few minutes.
http://www.experts-exchange.com/Database/Oracle/Q_21932226.html

thanks
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
1 - limited in performance.  not really in functionality because you can always turn it into a clob and then xmltype if you want, but if it's a blob, you have to convert first, so it'll always be a performance penalty.

2 - yes  xmltype can only hold xml documents.  so if it has errors then xmltype won't work.  it'll have to be a clob.

3- really?  I couldn't find it in the 9i documentation, nor in one of the 9i databases I checked.  Not sure off hand what version.   Since I don't know what your version of it looks like,  can you post the specification of the procedure and I'll come up with an example.

In my 11g db I've got at home,  it looks like this...

PROCEDURE convertToClob(dest_lob IN OUT NOCOPY  CLOB CHARACTER SET ANY_CS,
                          src_blob       IN             BLOB,
                          amount         IN             INTEGER,
                          dest_offset    IN OUT         INTEGER,
                          src_offset     IN OUT         INTEGER,
                          blob_csid      IN             NUMBER,
                          lang_context   IN OUT         INTEGER,
                          warning        OUT            INTEGER);


Is that what your 9i version looks like too?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Here's a simple example that ran on 11g,  I double checked my 9i documentation and couldn't find any mention of the converttoclob procedure in the
Supplied PL/SQL Packages and Types Reference Release 2 (9.2)

So, I'm not entirely sure what to show you for a 9i version since it appears that 9i should not have such a procedure

CREATE TABLE mylobs

(

    b                             blob,

    c                             clob

);
 
 

INSERT INTO mylobs(b) VALUES (utl_raw.cast_to_raw('<?xml version="1.0"?><a><b id=1>my b data</b><b id=2>my other b data</b></a>'

        ));
 
 

DECLARE

    v_clob                        clob;

    v_blob                        blob;

    v_dest_offset                 integer := 1;

    v_src_offset                  integer := 1;

    v_lang_context                integer := 0;

    v_warning                     integer;

BEGIN

    dbms_lob.createtemporary(v_clob, FALSE);
 

    SELECT b

    INTO v_blob

    FROM mylobs

   WHERE rownum = 1;
 

    dbms_lob.converttoclob

    (v_clob, v_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,

     dbms_lob.default_csid, v_lang_context, v_warning

    );

    dbms_output.put_line

        ('-----------------------------------------------------');

    dbms_output.put_line(v_clob);

    dbms_output.put_line

        ('-----------------------------------------------------');

    dbms_lob.freetemporary(v_clob);

END;

Open in new window

0
 

Author Comment

by:sam15
Comment Utility
sdstuber:

this is an EXCELLENT example. thanks a lot.

Here is what I copied from TOAD under SYS.DBMS_LOB package

PROCEDURE convertToClob( dest_lob       IN OUT NOCOPY  CLOB CHARACTER SET ANY_CS,
                          src_blob       IN             BLOB,
                          amount         IN             INTEGER,
                          dest_offset    IN OUT         INTEGER,
                          src_offset     IN OUT         INTEGER,
                          blob_csid      IN             NUMBER,
                          lang_context   IN OUT         INTEGER,
                          warning        OUT            INTEGER);

also i tried your procedure and added an actual to the table and it seems to have worked

  1  DECLARE
  2      v_clob                        clob;
  3      v_blob                        blob;
  4      v_dest_offset                 integer := 1;
  5      v_src_offset                  integer := 1;
  6      v_lang_context                integer := 0;
  7      v_warning                     integer;
  8  BEGIN
  9      dbms_lob.createtemporary(v_clob, FALSE);
 10      SELECT b
 11      INTO v_blob
 12      FROM mylobs
 13     WHERE rownum = 1;
 14      dbms_lob.converttoclob
 15      (v_clob, v_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
 16       dbms_lob.default_csid, v_lang_context, v_warning
 17      );
 18      update mylobs set c = v_clob where rownum =1 ;
 19      commit;
 20      dbms_output.put_line
 21          ('-----------------------------------------------------');
 22      dbms_output.put_line(v_clob);
 23      dbms_output.put_line
 24          ('-----------------------------------------------------');
 25      dbms_lob.freetemporary(v_clob);
 26* END;
 27  /
-----------------------------------------------------
<?xml version="1.0"?><a><b id=1>my b data</b><b id=2>my other b data</b></a>
-----------------------------------------------------


SQL> select c from mylobs;

C
--------------------------------------------------------------------------------
<?xml version="1.0"?><a><b id=1>my b data</b><b id=2>my other b data</b></a>

1 row selected.


1.  Would this procedure have any size limitation of the BLOB. Let us say the BLOB is 2 gig would it still work?

2.  I am not sure if you know how oracle uplaods files using mod_plsql to a documents table. it saves them by default as a BLOB. Would you take those and convert them as a CLOB and save them in another user table or you would leave them as BLOB in that table and convert them when you display.

My feeling since xml is text is to convert them when they are submitted via http and save them in the user table as CLOB. it seems cleaner.

3.  I guess if i decided to save them sa CLOB I have to do this:

-  insert into my_table ---this will save the files as blobs into documents table
-- select each file from the document_Table by using the pointer save in my_table for each file
--update my_table with those CLOBS

dose this sound correct or i can do all of that in the insert statement in step 1
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
1 - yes it "should" work,  however while you clearly do have the procedure and it seems to work,  it is undocumented as far as I can tell  so I can't guarantee it'll be reliable on 9i.  But, if it works on a small one, I would expect it to work on a big one too.  Nothing really changes.

2 - yes, I would put it in a CLOB too

3 - Maybe put a trigger on the table, so as soon as the blob is inserted it's immediately copied to a clob.
0
 

Author Comment

by:sam15
Comment Utility
sdstuber:

Here is what i am trying to do. Maybe you ahve a better way. I got an error on converting the 4 K xml file.
The small one works fine.

is there a limit on the BLOBtoCLOB conversion and how would you write the code more efficiently.


SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 X_NAME                                             VARCHAR2(128)
 X_FILE                                             CLOB
 Y_NAME                                             VARCHAR2(128)
 Y_FILE                                             CLOB
 Z_NAME                                             VARCHAR2(128)
 Z_FILE                                             CLOB

SQL> desc documents_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(128)
 MIME_TYPE                                          VARCHAR2(128)
 DOC_SIZE                                           NUMBER
 DAD_CHARSET                                        VARCHAR2(128)
 LAST_UPDATED                                       DATE
 CONTENT_TYPE                                       VARCHAR2(128)
 CONTENT                                            LONG RAW
 BLOB_CONTENT                                       BLOB



SQL> execute print_table('select * from test1');
ID                            : 3
X_NAME                        : F24098/x.xml.xml
X_FILE                        :
Y_NAME                        : F9283/y.xml.xml
Y_FILE                        :
Z_NAME                        : F14714/z.xml.xml
Z_FILE                        :
-----------------


I want to get the CLOB of each file into X_FILE, Y_FILE, Z_FILE

PROCEDURE SAVE_TEST1 (
     p_x_name              IN   VARCHAR2   DEFAULT   NULL,
     p_x_file              IN   VARCHAR2   DEFAULT   NULL,
     p_y_name              IN   VARCHAR2   DEFAULT   NULL,
     p_y_file              IN   VARCHAR2   DEFAULT   NULL,
     p_z_name              IN   VARCHAR2   DEFAULT   NULL,
     p_z_file              IN   VARCHAR2   DEFAULT   NULL)
AS

     v_clob                        clob;
     v_blob                        blob;
     v_dest_offset                 integer := 1;
     v_src_offset                  integer := 1;
     v_lang_context                integer := 0;
     v_warning                     integer;
     
     v_x_blob              blob;
     v_y_blob              blob;
     v_z_blob              blob;
     v_x_clob              clob;
     v_y_clob              clob;
     v_z_clob              clob;
     l_id                  integer;

BEGIN
   dbms_lob.createtemporary(v_x_clob,FALSE);
    dbms_lob.createtemporary(v_y_clob,FALSE);
     dbms_lob.createtemporary(v_z_clob,FALSE);

--insert the files - default is BLOB into documents table
   INSERT INTO TEST1(id,x_name,x_file,y_name,y_file,z_name,z_file)
       VALUES (test1_seq.nextval,p_x_name,p_x_file,p_y_name,p_y_file,p_z_name,p_z_file)
       returning id into l_id;
 
--get the blob from documents table
   SELECT BLOB_CONTENT into v_x_blob FROM documents_table WHERE name = trim(p_x_name);

   dbms_lob.converttoclob
       (v_x_clob, v_x_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );

   UPDATE TEST1
        SET x_file = v_x_clob WHERE id = l_id;

   SELECT BLOB_CONTENT into v_y_blob FROM documents_table WHERE name = p_y_name;

   dbms_lob.converttoclob
       (v_y_clob, v_y_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );

     UPDATE TEST1
        SET y_file = v_y_clob WHERE id = l_id;

   SELECT BLOB_CONTENT into v_z_blob FROM documents_table WHERE name = p_z_name;

   dbms_lob.converttoclob
       (v_z_clob, v_z_blob, dbms_lob.lobmaxsize, v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );
 
 UPDATE TEST1
        SET z_file = v_z_clob WHERE id = l_id;

   COMMIT;
  htp.p('saved');

END;



Thu, 19 Jun 2008 21:57:21 GMT

ORA-22925: operation would exceed maximum size allowed for a LOB value
ORA-06512: at "SYS.DBMS_LOB", line 653
ORA-06512: at "SAVE_TEST1", line 46
ORA-06512: at line 64

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sam15
Comment Utility
I forgot to mention when you uplaod files via mod_plsql oracle automaticlly saves the file in documents_table and saes the NAME into the other table as a pointer.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also, I really don't know what to say about using the 9i converttoclob procedure because, again, it's not supposed to be there
so there's really no reason at all it should work.

but...try using dbms_lob.getlength(clob_variable_here)  on the clobs instead of using dbms_lob.lobmaxsize






0
 

Author Comment

by:sam15
Comment Utility
The procedure works fine under 9i. I think something in the code.

you mean doing this

dbms_lob.converttoclob
       (v_x_clob, v_x_blob, dbms_lob.getlength(v_x_clob), v_dest_offset, v_src_offset,
        dbms_lob.default_csid, v_lang_context, v_warning );

ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 653
ORA-06512: at ".SAVE_TEST1", line 37
ORA-06512: at line 64

2. How would you do the updates. would you set up a loop or a better way of doing it.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
actually do it it on the blob, not the clob

since you can't use a procedure inside of sql, yes, you'll have to loop through the blobs doing the updates
0
 

Author Comment

by:sam15
Comment Utility
Do you think this will work?

How can you do a loop with one update statement when you are updating several columns with different file?do you have a small example.

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
      v_clob    CLOB;
      v_varchar VARCHAR2(32767);
      v_start       PLS_INTEGER := 1;
      v_buffer  PLS_INTEGER := 32767;
BEGIN
      DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
      
      FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
      LOOP
            
         v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
 
           DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
 
            v_start := v_start + v_buffer;
      END LOOP;
      
   RETURN v_clob;
 
END blob_to_clob;
/


0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
"How can you do a loop with one update statement "

I'm not sure what you're asking there.

Maybe I'm misunderstanding your original question of "How would you do the updates. would you set up a loop or a better way of doing it."

What I was trying to say with my answer to that question is you can't issue a single update statement to change clobs in a bunch of rows
because the converttoclob procedure can't be used in sql.  So you'll have to write a loop that converts each blob to a clob and then update
each row with the new clob.
0
 

Author Comment

by:sam15
Comment Utility
yes you wrote the question better. I am not trying to update many rows. It is one row with several CLOB columns.

however, the BLOBS are in many rows in the other table "documents_table".
so let us say you have this

test1
-------
id  number(1)
name varchar2(100)
x  clob
y  clob
z  clob

document_table
------------------
name      varchar2(100)
blob_file   blob

test1 has ONE ROW. DOCUMENT_TABLE has THREE rows.
I want to to update the row in test1 with the three files in document_table.

What is the most efficient way of writing it.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
something like this...

for b in (select rownum rn, blob_file
                    from document_table
                    where name = v_name
                      and rownum <= 3
               ) loop
      if b.rn = 1 then dbms_lob.converttoclob( put the x clob conversion here)
      elsif b.rn = 2 then dbms_lob.converttoclob(put the y clob conversion here)
      elsif b.rn = 3 then dbms_lob.convertoclob(put the z clob conversion here);
      end if;
end loop;

update test1 set  name = v_name, x = v_x_clob, y = v_y_clob, z = v_z_clob
where id = v_id;




0
 

Author Comment

by:sam15
Comment Utility
sdstuber

thanks, do you see issues with this. it works for one file without the loop. but does not with loop.

PROCEDURE SAVE_TEST3 (
     p_x_name              IN   VARCHAR2   DEFAULT   NULL,
     p_y_name              IN   VARCHAR2   DEFAULT   NULL,
     p_z_name              IN   VARCHAR2   DEFAULT   NULL)
AS

    type vc_array is table of varchar2(30);
        l_files vc_array := vc_array( p_x_name, p_y_name, p_z_name );

       l_blob       blob;
       x_clob      clob;
       y_clob      clob;
       z_clob      clob;
       l_warning    number;
       l_id         number;
       l_src_offset number := 1;
       l_dest_offset number := 1;
       l_cs_id       number := dbms_lob.default_csid;
       c_cnt integer;
       
      TYPE clob_files IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
      l_clob clob_files;


BEGIN
  dbms_lob.createtemporary(x_clob,FALSE);
  dbms_lob.createtemporary(y_clob,FALSE);
  dbms_lob.createtemporary(z_clob,FALSE);


  insert into test1(id,x_name,y_name,z_name)
     values (test1_seq.nextval,p_x_name,p_y_name,p_z_name)
     returning id into l_id;

       for i in 1 .. l_files.count
       loop
         if (l_files(i) is not null) then
     
         select blob_content into l_blob
          from documents_table
          where name = l_files(i);


        l_src_offset := 1;
        l_dest_offset := 1;
        dbms_lob.convertToClob(l_clob(i), l_blob, dbms_lob.getLength(l_blob), l_src_offset,
       l_dest_offset, 1, l_cs_id, l_warning );
     
        end if;
   
      end loop;

        update test1
           set  x_file = l_clob(1),
                y_file = l_clob(2),
                z_file = l_clob(3)
            where id = l_id;

   COMMIT;
  htp.p('saved');

END;  
 

ORA-01403: no data found
ORA-06512: at "SAVE_TEST3", line 52
ORA-06512: at line 64
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now