CODEWITHSUNDEEP
xmldatabaseoracle-databaseplsqlblob
Debapriya Biswas
Debapriya Biswas

Reputation: 1339

Insert large xml file as as blob in oracle table

I have a 450 MB xml file and I want to insert it into Blob column in Oracle table. I tried inserting the file content as string but it says "string literal too long".Can anyone please suggest an elegant way to insert into table. Provided: I dont have directory access in database server, I have the xml file in my local system

Upvotes: 0

Views: 7029

Answers (3)

collapsar
collapsar

Reputation: 17238

You may convert the xml file into an sql script executing a suitably crafted anonymous plsql block. Loading this script into the db will populate the blob.

The basic idea is to split the xml file into chunks of 2000 characters. The first chunk may be inserted into the target table's blob column directly. Each other will be added by an update statement taking advantage of the dbms_lob.fragment_insert package procedure. !!! WARNING: This is not recommended practice !. Better get a dba to load it for you!

Example:

  • Assumptions:

    • The target table has 2 columns, the pk and the blob.
    • the pk is 42.
    • 2000 is a sample chunk size deemed suitable. Technically, dbms_lob.fragment_insert handles up to 32767, however, other tools involved (eg. sqlplus) might have tighter bounds on line length.

  • Code: 

    declare
    l_b BLOB;
begin
    insert
      into
  t_target ( c_pk, c_blob )
    values ( 42, utl_raw.cast_to_raw('<This literal contains the first 2000 (chunksize) characters of the xml file>') )
 returning c_blob
     into l_b
        ;

 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 2000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 4000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset 6000>'));
 ...
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains the last chunk>'));

 commit;
end;
/
show err

Preparatory work

  1. You need to make sure that no single quote occurs inside your xml file. Otherwise the generated plsql code will contain syntax errors.

    If single quotes aren't used as attribute value delimiters, simply replace them with the numerical entity &x#28;.

  2. Create the bulk of the anonymous plsql

Methods for inserting data into a file at regular intervals are presented in this SO question, the most flexible approach being outlined in this answer. Instead of newlines, insert the following string inserted:

"'));\n     dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('"

The remainder of the anonymous plsql can be copied/written by hand.

Caveat

As is, the script size will be of the same magnitude as the original xml and the plsql block will contain 200k+ lines. Very likely you will run into some limitations of the tools involved. However, the script can be split into an arbitrary number of chunks as follows:

declare
    l_b BLOB;
begin
    select c_blob
      into l_b
      from t_target
     where c_pk = 42
        ;

 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset <k>*2000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+1)*2000>'));
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+2)*2000>'));
 ...
 dbms_lob.fragment_insert ( l_b, 2000, 1+dbms_lob.getlength(l_b), utl_raw.cast_to_raw('<This literal contains 2000 characters starting at [0-based] offset (<k>+<n_k>)*2000>'));
end;
/
show err

And once again: !!! WARNING: This is not recommended practice !. Better get a dba to load it for you!

Upvotes: 1

J. Chomel
J. Chomel

Reputation: 8395

Let's say you search a little and stumble upon a search result: you find a page with PL/SQL code.

CREATE OR REPLACE DIRECTORY test_dir AS '<path_on_db_server>'; 

DECLARE
  l_bfile  BFILE;
  l_blob   BLOB;
BEGIN
  -- this depends on your table definition, col1 being the BLOB column
  INSERT INTO tab1 (col1, col2) VALUES (empty_blob(), 'test1')
  RETURN col1 INTO l_blob;

  l_bfile := BFILENAME('test_dir', 'my.xml');
  DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);
  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  COMMIT;
END;

Then you try it (after you put your test.xml file on the server in <path_on_db_server> and ensured that oracle user has access to the file).

Upvotes: 2

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

How about writing small program on any languages, which is able to read a file and make insert query into your table.

Upvotes: 1

Related Questions