Solved

Write lob data to file

Posted on 2013-11-26
4
1,420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 77

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 77

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - Query link database loop 8 66
populate value based on what is selected in lov 2 61
Oracle Mulit-site configuration 28 68
Oracle Join issue. 3 42
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

738 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