Re: writing contents of oracle long variable to file

From: <sunrise828_at_my-deja.com>
Date: Mon, 22 Nov 1999 20:05:32 GMT
Message-ID: <81c7m9$fr5$1@nnrp1.deja.com>

> I need to write contents of oracle long variable to a flat file.

This is code from this book: Orcale 8 PL/SQL Programming By Scott Urman. If you are using Oracle 7.34 ... it does not show you how to get around the 1023 Utl_file limit. We had to write out a special record and write a separate C program to get around that problem.

CREATE OR REPLACE FUNCTION dump_doc(docid IN NUMBER,

                                    filename IN VARCHAR2)
                           RETURN VARCHAR2
  IS
    data_chunk          VARCHAR2(254);
    chunk_size          NUMBER:=254;
    chunk_size_returned NUMBER;
    location            VARCHAR2(20) := '/tmp';
    mycursor            NUMBER;
    stmt                VARCHAR2(1024);
    cur_pos             NUMBER:=0;
    rows                NUMBER;
    dummy               NUMBER;
    file_handle         UTL_FILE.FILE_TYPE;
    status              VARCHAR2(50);
  BEGIN
     file_handle:=utl_file.fopen(location,filename,'w');


         -- open the file for writing



     stmt:='SELECT DOCUMENT FROM ASCII_DOCS WHERE ID = :doctoget';

     mycursor:=dbms_sql.open_cursor;
     dbms_sql.parse(mycursor, stmt, dbms_sql.v7);
     dbms_sql.bind_variable(mycursor, ':doctoget', docid);


         -- bind the doctoget host variable with the plsql parameter


docid


         -- which is passed into the function



     dbms_sql.define_column_long(mycursor,1);
     dummy:=dbms_sql.execute(mycursor);
     rows:=dbms_sql.fetch_rows(mycursor);


         -- only doing one fetch for the primary key as assuming the


whole


         -- document is stored in one row


     loop


         -- fetch 'chunks' of the long until we have got the lot


       dbms_sql.column_value_long(mycursor,
                                  1,
                                  chunk_size,
                                  cur_pos,
                                  data_chunk,
                                  chunk_size_returned);
       utl_file.put(file_handle, data_chunk);
       cur_pos:=cur_pos + chunk_size;
       exit when chunk_size_returned = 0;
     end loop;
     dbms_sql.close_cursor(mycursor);
     utl_file.fclose(file_handle);
     return('Success');

  EXCEPTION
    WHEN OTHERS THEN

       utl_file.fclose(file_handle);
       return ('Failure');

  END dump_doc;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 22 1999 - 14:05:32 CST