Solved

Write lob data to file

Posted on 2013-11-26
4
1,332 Views
Last Modified: 2013-11-26
Hi,

I am trying to capture an xml feed and save it to a file for parsing into a DB table at a later time.

I am able to get the feed and display it as dbms_output but, trying to load a temp lob and then send the appended lob data to a file, I run into trouble.

SET SERVEROUTPUT ON
SET DEFINE OFF

DECLARE
   v_http_req     UTL_HTTP.req;
   v_http_resp    UTL_HTTP.resp;
   v_buffer       CLOB;
   v_text         VARCHAR2 (32767);
   v_url          VARCHAR2 (300)
      := 'http://comcat.cr.usgs.gov/fdsnws/event/1/query?starttime=2013-11-24T06:00:00&endtime=2013-11-24T18:00:00';
   start_period   DATE
      := TRUNC (TO_DATE ('2013-11-24T06:00:00', 'YYYY-MM-DD"T"hh24:mi:ss'));
   end_period     DATE
      := TRUNC (TO_DATE ('2013-11-24T18:00:00', 'YYYY-MM-DD"T"hh24:mi:ss'));
   v_filename     VARCHAR2 (200)
      := 'eq_event_' || start_period || '_' || end_period || '.xml';
   v_outFile      UTL_FILE.file_type;
   v_dir          VARCHAR2 (20) := 'XML_FILES';
   v_exists       BOOLEAN;
   v_fileLength   NUMBER;
   v_blockSize    BINARY_INTEGER;
BEGIN
   --   DBMS_OUTPUT.enable (1000000);
   DBMS_LOB.CREATETEMPORARY (v_buffer, FALSE);

   v_http_req :=
      UTL_HTTP.begin_request (url            => v_url
                             ,method         => 'POST'
                             ,http_version   => 'HTTP/1.1');
   UTL_HTTP.set_header (v_http_req, 'Content-Type', 'application/xml');
   v_http_resp := UTL_HTTP.get_response (v_http_req);
   --  read data feed into lob
   BEGIN
      LOOP
         UTL_HTTP.read_text (v_http_resp, v_text, 32766);
         DBMS_LOB.WRITEAPPEND (v_buffer, LENGTH (v_text), v_text);
      --         UTL_HTTP.read_line (v_http_resp, v_buffer, TRUE);
      --         DBMS_OUTPUT.put_line (v_buffer);
      END LOOP;
   EXCEPTION
      WHEN UTL_HTTP.end_of_body
      THEN
         UTL_HTTP.end_response (v_http_resp);
   END;
   --  get file attributes to check pre-existence of file with same name
   UTL_FILE.FGETATTR (v_dir
                     ,v_filename
                     ,v_exists
                     ,v_fileLength
                     ,v_blockSize);

   IF v_exists = TRUE
   THEN
   -- if the file exists then append to it
      v_outFile := UTL_FILE.fopen (v_dir, v_filename, 'A');
      UTL_FILE.put_line (v_outFile, v_buffer, TRUE);
      UTL_FILE.fflush (v_outFile);
      UTL_FILE.FCLOSE (v_outFile);
   ELSE --  error starts here (if the file does not yet exist)
   -- otherwise create a new file and write out the lob contents
      v_outFile := UTL_FILE.fopen (v_dir, v_filename, 'W');
      utl_file.put(v_outFile, v_buffer);
--      UTL_FILE.put_line (v_outFile, v_buffer, TRUE);
--      UTL_FILE.fflush (v_outFile);
      --  close the file
      UTL_FILE.FCLOSE (v_outFile);
   END IF;

   DBMS_LOB.FREETEMPORARY (v_buffer);
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_CALL_STACK);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
--   WHEN UTL_HTTP.end_of_body
--   THEN
--      UTL_HTTP.end_response (v_http_resp);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_CALL_STACK);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
      UTL_FILE.FCLOSE (v_outFile);
--      UTL_HTTP.end_response (v_http_resp);
END;
/

Open in new window


The trapped VALUE_ERROR indicates that it is a problem when writing.  The directory is available to the db user and the file itself (in a windows OS environment) is full control and shared for everyone.  I am not sure how to find where the mistake in my code is and could use a little help.

Incidentally this is the error stack:

ORA-06502: PL/SQL: numeric or value error
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FF1E2DE708        71  anonymous block
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 59
 PL/SQL procedure successfully completed.

Open in new window

Thanks
0
Comment
Question by:g_currier
  • 2
  • 2
4 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 39677914
The problem is that put_line can only handle 32K.  You cannot write out a CLOB at once.  You need to CHUNK it.

There are many examples out there on writing a CLOB to a file.

Are you using a TEMP LOB just in case you don't get the entire site?

Also:
1:  Opening in APPEND mode will create a new file if it doesn't exist so there really isn't a need to check for it first

2: I believe you will have to use to_char and a format mask in the file name to do an explicit date-to-string conversion.
0
 

Author Comment

by:g_currier
ID: 39678094
the filename itself is no problem..the file gets created with 0 bytes and then is locked (it won't close unless I quit the session).  

I won't (then) worry about existence for appending to the file (in essence I won't do that anyway).

I am using a temp lob because I might not get the entire xml feed (some days have more earthquakes than others and thus, the feed size is variable - mostly greater than 32K).  This will leave me with non-wellformed xml that is mostly useless.

Chunking the data...that's a trick.
I thought that's what I was doing here:

         UTL_HTTP.read_text (v_http_resp, v_text, 32766);
         DBMS_LOB.WRITEAPPEND (v_buffer, LENGTH (v_text), v_text);

Open in new window


I have even adjusted the size of the chunk

This (if I have it right - from online examples morgan's library, and other oracle technical discussion forums), reads the response into the v_text variable, 32766 bytes at a time and then writeappends to the clob, v_buffer and loops through to the end_of_body.

I know that the connection works because I can get the whole feed in output when I use read_line instead.

I changed the code to this:

...
   v_text         VARCHAR2 (31743);
...
   v_filename     VARCHAR2 (200)
      :=    'eq_event_' || TO_CHAR (start_period, 'dd-Mon-YYYY') || '_' || TO_CHAR (end_period, 'dd-Mon-YYYY') || '.xml';
...
LOOP
         UTL_HTTP.read_text (v_http_resp, v_text, LENGTH (v_text) - 1);
         DBMS_LOB.WRITEAPPEND (v_buffer, LENGTH (v_text) - 1, v_text);
      --         UTL_HTTP.read_line (v_http_resp, v_buffer, TRUE);
      --         DBMS_OUTPUT.put_line (v_buffer);
      END LOOP;
...

Open in new window

and I'm still getting issues (the same ones).

I thought that I had followed this spec pretty closely:

Syntax

UTL_HTTP.READ_TEXT(
   r     IN OUT NOCOPY resp,
   data  OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
   len   IN PLS_INTEGER DEFAULT NULL);
Parameters

Table 250-44 READ_TEXT Procedure Parameters

Parameter	Description
r - The HTTP response
data- The HTTP response body in text form
len - The maximum number of characters of data to read. If len is NULL, this procedure will read as much input as possible to fill the buffer allocated in data. The actual amount of data returned may be less than that specified if little data is available before the end of the HTTP response body is reached or the transfer_timeout amount of time has elapsed. The default is NULL.

Open in new window


So do this mean that I need to chunk the v_http_resp? Or something else?

For the moment I am attempting to use utl_http.request_pieces to see what I can do with that.

Thanks
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39678140
>>I thought that's what I was doing here:

You are appending to the lob in chunks.

The issue is when you are writing it to the file:
UTL_FILE.put_line (v_outFile, v_buffer, TRUE);


v_buffer can be > 32K in size.  UTL_FILE.PUT_LINE cannot handle more than 32K.

You need to chunk the clob when you write it to the file.

I'm also not sure you want PUT_LINE.  What if the chuck causes a mis-placed line break?  Try UFL_FILE.PUT instead.

The online docs have the syntax.
0
 

Author Closing Comment

by:g_currier
ID: 39678961
I found a different answer (and a bit more elegant) thanks to your prompting:

   /* http://www.gokhanatil.com/2013/06/how-to-read-web-pages-using-utl_http-request_pieces.html */
   FUNCTION get_feed (p_url IN VARCHAR2)
      RETURN CLOB
   IS
      v_xmlPcs    UTL_HTTP.html_pieces;
      v_xmlClob   CLOB;
   BEGIN
      v_xmlPcs := UTL_HTTP.request_pieces (p_url);

      FOR i IN 1 .. v_xmlPcs.COUNT
      LOOP
         v_xmlClob := v_xmlClob || v_xmlPcs (i);
      END LOOP;

      RETURN v_xmlClob;
   END get_feed;

   /* http://www.morganslibrary.org/hci/hci004.html */
   PROCEDURE send_to_file (p_dir IN VARCHAR2, p_filename IN VARCHAR2,p_url in varchar2)
   IS
      v_clob          CLOB;
      v_exists        BOOLEAN;
      v_file_length   NUMBER;
      v_block_size    BINARY_INTEGER;
   BEGIN
      SELECT get_feed (p_url) INTO v_clob FROM DUAL;

      UTL_FILE.fgetattr (p_dir
                        ,p_filename
                        ,v_exists
                        ,v_file_length
                        ,v_block_size);

      IF v_exists = TRUE
      THEN
         UTL_FILE.fremove (p_dir, p_filename);
      END IF;

      DBMS_XSLPROCESSOR.clob2file (v_clob, p_dir, p_filename);
   END send_to_file;

Open in new window


these work excellently...

I modified them where I needed it (to facilitate repeated testing).

So far, a one month's worth of data consumes about 15 MB.  Considering that I have about 75 years of data to sift through, this could take a while.

thanks for your advice.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 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

21 Experts available now in Live!

Get 1:1 Help Now