Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Loading a file into a clob

Given a url pointing to a file hosted on the webserver, is it possible to read the contents of that url into a clob? And if so, how?

like image 718
JDS Avatar asked May 11 '10 19:05

JDS


People also ask

How do I insert a file into a CLOB column in Oracle?

create or replace directory documents AS 'C:\'; Next we create a table to hold the CLOB. create table tab1 ( id number, clob_data clob ); We import the file into a CLOB datatype and insert it into the table.

Can we store image in CLOB?

BLOBs are used to store binary information, such as images, while CLOBs are used to store character information.

What can be stored in CLOB?

You can use the CLOB data type to store a block of text. It is designed to store ASCII text data, including formatted text such as HTML or PostScript. Although you can store any data in a CLOB object, IBM® Informix® tools expect a CLOB object to be printable, so restrict this data type to printable ASCII text.

What is the difference between BLOB and CLOB in Oracle?

The full form of Blob is a Binary Large Object. The full form of Clob is Character Large Object. This is used to store large binary data. This is used to store large textual data.


1 Answers

Here is a procedure which takes a URL and loads its contents into a table.

The web file is retrieved using UTL_HTTP.GET_PIECES(). This returns an array of strings. If you are working behind a firewall you will need to declare your proxy. Find out more about UTL_HTTP.

The CLOB wrangling uses various bits of DBMS_LOB functionality. The procedure declares a temporary lob variable, appends the pieces of the UTL_HTTP array to it and then finally inserts it into a table. Find out more about DBMS_LOB.

SQL> create or replace procedure pop_file_from_url
  2      (p_url in varchar2)
  3  is
  4      tc clob;
  5      lv_web_page utl_http.html_pieces;
  6  begin
  7
  8      dbms_lob.createtemporary(tc, true);
  9      dbms_lob.open(tc, dbms_lob.lob_readwrite);
 10
 11      lv_web_page := utl_http.request_pieces (p_url);
 12
 13      for i in 1..lv_web_page.count()
 14      loop
 15          dbms_lob.writeappend(tc, length(lv_web_page(i)) ,lv_web_page(i));
 16      end loop;
 17
 18      insert into t23 values (1, tc);
 19
 20      dbms_lob.close(tc);
 21      dbms_lob.freetemporary(tc);
 22
 23  end pop_file_from_url;
 24  /

Procedure created.

SQL> 

If, like me, you are on 11g you will need to add the URL to an Access Control List, otherwise the request will be blocked. Find out more about ACLs.

SQL> exec pop_file_from_url('stackoverflow.com')
BEGIN pop_file_from_url('stackoverflow.com'); END;

*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "APC.POP_FILE_FROM_URL", line 11
ORA-06512: at line 1


SQL> 

After adding the URL for StackOverflow to my ACL I can now insert the file into my table:

SQL> exec pop_file_from_url('stackoverflow.com')

PL/SQL procedure successfully completed.

SQL> select id, dbms_lob.getlength(txt)
  2  from t23
  3  /

        ID DBMS_LOB.GETLENGTH(TXT)
---------- -----------------------
         1                  208226

SQL>
like image 86
APC Avatar answered Sep 28 '22 10:09

APC