Saving an XML file into table with PL/SQL

My table Structure is like this:

SQL> /* Creating Your table */
SQL> CREATE TABLE IBSCOLYTD
  2  (
  3  ACTNOI VARCHAR2 (8),
  4  MEMONOI NUMBER (7,0),
  5  MEMODTEI DATE,
  6  AMOUNTI NUMBER (8,0),
  7  BRCDSI NUMBER (4,0),
  8  TYPEI NUMBER (4,0),
  9  TRANSMONI NUMBER (6,0)
 10  );

My XML file is:

<?xml version="1.0"?>
<ROWSET>
<IBSCOLYTD>
<ACTNOI>28004125</ACTNOI>
<MEMONOI>251942</MEMONOI>
<MEMODTEI>05-SEP-92</MEMODTEI>
<AMOUNTI>400</AMOUNTI>
<BRCDSI>513</BRCDSI>
<TYPEI>1</TYPEI>
<TRANSMONI>0</TRANSMONI>
</IBSCOLYTD>
<IBSCOLYTD>
<ACTNOI>28004125</ACTNOI>
<MEMONOI>251943</MEMONOI>
<MEMODTEI>04-OCT-92</MEMODTEI>
<AMOUNTI>400</AMOUNTI>
<BRCDSI>513</BRCDSI>
<TYPEI>1</TYPEI>
<TRANSMONI>0</TRANSMONI>
</IBSCOLYTD>
</ROWSET>

My goal is to load the values from the XML file into the respective 

columns of the table.
Now the actual code

SQL> CREATE OR REPLACE PROCEDURE insert_xml_emps(p_directory in varchar2,
  2                                              p_filename  in varchar2,
  3                                              vtableName  in varchar2) as
  4    v_filelocator    BFILE;
  5    v_cloblocator    CLOB;
  6    l_ctx            DBMS_XMLSTORE.CTXTYPE;
  7    l_rows           NUMBER;
  8    v_amount_to_load NUMBER;
  9    dest_offset      NUMBER := 1;
 10    src_offset       NUMBER := 1;
 11    lang_context     NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
 12    warning          NUMBER;
 13  BEGIN
 14    dbms_lob.createtemporary(v_cloblocator, true);
 15    v_filelocator := bfilename(p_directory, p_filename);
 16    dbms_lob.open(v_filelocator, dbms_lob.file_readonly);
 17    v_amount_to_load := DBMS_LOB.getlength(v_filelocator);
 18    ---  ***This line is changed*** ---
 19    DBMS_LOB.LOADCLOBFROMFILE(v_cloblocator,
 20                              v_filelocator,
 21                              v_amount_to_load,
 22                              dest_offset,
 23                              src_offset,
 24                              0,
 25                              lang_context,
 26                              warning);
 27  
 28    l_ctx := DBMS_XMLSTORE.newContext(vTableName);
 29    DBMS_XMLSTORE.setRowTag(l_ctx, 'ROWSET');
 30    DBMS_XMLSTORE.setRowTag(l_ctx, 'IBSCOLYTD');
 31    -- clear the update settings 
 32    DBMS_XMLStore.clearUpdateColumnList(l_ctx);
 33    -- set the columns to be updated as a list of values 
 34    DBMS_XMLStore.setUpdateColumn(l_ctx, 'ACTNOI');
 35    DBMS_XMLStore.setUpdateColumn(l_ctx, 'MEMONOI');
 36    DBMS_XMLStore.setUpdatecolumn(l_ctx, 'MEMODTEI');
 37    DBMS_XMLStore.setUpdatecolumn(l_ctx, 'AMOUNTI');
 38    DBMS_XMLStore.setUpdatecolumn(l_ctx, 'BRCDSI');
 39    DBMS_XMLStore.setUpdatecolumn(l_ctx, 'TYPEI');
 40    DBMS_XMLStore.setUpdatecolumn(l_ctx, 'TRANSMONI');
 41    -- Now insert the doc.
 42    l_rows := DBMS_XMLSTORE.insertxml(l_ctx, v_cloblocator);
 43    DBMS_XMLSTORE.closeContext(l_ctx);
 44    dbms_output.put_line(l_rows || ' rows inserted...');
 45    dbms_lob.close(v_filelocator);
 46    DBMS_LOB.FREETEMPORARY(v_cloblocator);
 47  END;
 48  /

Procedure created.

SQL> BEGIN
  2  insert_xml_emps('TEST_DIR','load.xml','IBSCOLYTD');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ibscolytd;

ACTNOI      MEMONOI MEMODTEI     AMOUNTI     BRCDSI      TYPEI  TRANSMONI
-------- ---------- --------- ---------- ---------- ---------- ----------
28004125     251942 05-SEP-92        400        513          1          0
28004125     251943 04-OCT-92        400        513          1          0

SQL> 

The same code is published on OTN by me.

Example of downloading a pdf from web and saving into disk using PL/SQL.

Today, I have posted a code in OTN regarding Downloading a pdf file
from web and saving it into the disk using PL/SQL. I am re-posting
it into my BLOG for future reference.

SQL> CREATE OR REPLACE DIRECTORY TEST_DIR as 'C:\';
 
Directory created.
 
SQL> /* Example of downloading a pdf from web and saving into disk */
SQL> DECLARE
  2    lv_url    VARCHAR2(100) := 'http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf';
  3    lc_return BLOB;
  4    lhttp_url httpuritype;
  5    ---Varriables declared for writing the LOB to pdf file --
  6    l_file     UTL_FILE.FILE_TYPE;
  7    l_buffer   RAW(32767);
  8    l_amount   BINARY_INTEGER := 32767;
  9    l_pos      INTEGER := 1;
 10    l_blob     BLOB;
 11    l_blob_len INTEGER;
 12  BEGIN
 13    --create uri
 14    lhttp_url := httpuritype.createuri(lv_url);
 15    --get the PDF document
 16    lc_return := lhttp_url.getblob();
 17    -- Open the destination file.
 18    l_file := UTL_FILE.FOPEN('TEST_DIR', 'mypdf.pdf', 'wb');
 19    --Get the total length of the BLOB
 20    l_blob_len := DBMS_LOB.getlength(lc_return);
 21    -- Read chunks of the BLOB and write them to the file
 22    -- until complete.
 23    WHILE l_pos < l_blob_len LOOP
 24      DBMS_LOB.READ(lc_return, l_amount, l_pos, l_buffer);
 25      UTL_FILE.put_raw(l_file, l_buffer, FALSE);
 26      l_pos := l_pos + l_amount;
 27    END LOOP;
 28    -- Close the file.
 29    UTL_FILE.FCLOSE(l_file);
 30  EXCEPTION
 31    WHEN OTHERS THEN
 32      -- Close the file if something goes wrong.
 33      IF UTL_FILE.IS_OPEN(l_file) THEN
 34        UTL_FILE.FCLOSE(l_file);
 35      END IF;
 36    
 37      RAISE;
 38  END;
 39  / 
 
PL/SQL procedure successfully completed.
 
SQL>

And the file saved successfully.

C:\>dir *.pdf
 Volume in drive C has no label.
 Volume Serial Number is 6806-ABBD
 
 Directory of C:\
 
12/22/2010  02:56 PM           170,264 mypdf.pdf
               1 File(s)        170,264 bytes
               0 Dir(s)   4,829,433,856 bytes free
 
C:\>