asked on

Write lob data to file

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

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

g_currier

ASKER

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

g_currier

ASKER

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.