Home » SQL & PL/SQL » SQL & PL/SQL » Accessing directory entries in PL/SQL with clustering (Clustered Oracle 11g running on Unix)
Accessing directory entries in PL/SQL with clustering [message #399324] Tue, 21 April 2009 10:58 Go to next message
deanhw80
Messages: 2
Registered: March 2009
Location: UK
Junior Member
Hello all,

Apologies if I use any terms incorrectly, I'm not that familiar with Oracle databases and having to learn on the job! I'm trying to extract subsets of data from a table contained in a clustered Oracle database running on Unix. I need to extract the data to a text file without using third party tools and I'd like the process to be automated.

My proposed solution was to run a PL/SQL script which executed a query and used the UTL_FILE_PUT command from the UTL_FILE package to write the results to a text file. The text file is located in a folder defined in an Oracle directory entry.

The script is being executed via SQL plus running on an administration server. However as the files are written by an Oracle package they are being written by the database server.

My questions are:
In a clustered database could the machine writing the output files be any node in the cluster? I thought of having a shared network location as the directory entry but the IT department is not happy about setting up the mounted drive in Unix on each node.

Is there any way I could redirect the output of the UTL_FILE_PUT back to the machine SQL plus is running on?

Any advice gratefully received!

Dean
Re: Accessing directory entries in PL/SQL with clustering [message #399325 is a reply to message #399324] Tue, 21 April 2009 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way I could redirect the output of the UTL_FILE_PUT back to the machine SQL plus is running on?
NO!
UTL_FILE is PL/SQL run on & by database owner and can only write to "local" file system.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: Accessing directory entries in PL/SQL with clustering [message #399448 is a reply to message #399324] Wed, 22 April 2009 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is possible to use UTL_FILE to write to a network drive.

Under Windows, you need to have the Listener process running under a network domain account to get this to work.
Once you've got that set up, it's just a case of using Unc notation for the path to the file.

It's possible on Linux/Unix, but I don't know the details.
Re: Accessing directory entries in PL/SQL with clustering [message #400675 is a reply to message #399324] Wed, 29 April 2009 04:37 Go to previous messageGo to next message
deanhw80
Messages: 2
Registered: March 2009
Location: UK
Junior Member
Thanks for the replies. After some more investigation I've come to the conclusion this isn't possible using PL/SQL so I've split my program into two parts, a PL/SQL script which writes the data to a table and a C+ program that runs on a remote application server to extract the data from the table into a comma delimited file.

For future reference if anyone else is thinking of doing a similar thing I was using the following PL/SQL scripts:

    SQL_STRING := 
        'CREATE OR REPLACE DIRECTORY RECON_OUTPUT AS '''||OUTPUT_FOLDER||'''';
    EXECUTE IMMEDIATE SQL_STRING;
    EXECUTE IMMEDIATE 'GRANT READ,WRITE ON DIRECTORY RECON_OUTPUT TO PUBLIC';


Where OUTPUT_FOLDER is a variable containing a path i.e. "/tmp" or a fully qualified network location.

I then define the SQL statement I need to extract the data I'm interested in and call:

NUM_RECS_TO_FILE := dump_csv(SQL_STRING,chr(9),'RECON_OUTPUT',REC_FILE_NAME, COL_HEADING);


The variables passed in are the SQL string to be executed, the delimiting character, the directory entry name, reconciliation file name (test.txt) and a delimited string containing the column headings. The function returns the number of records selected.

The function dump_CSV (copied from the internet and slightly modified) is defined by:

function  dump_csv( p_query        in varchar2,
        p_separator in varchar2 default ',',
        p_dir       in varchar2 ,
        p_filename  in varchar2,
        p_colhdg    in varchar2 )
        return number
    as
        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(2000);
        l_status        integer;
        l_colCnt        number default 0;
        l_separator     varchar2(10) default '';
        l_cnt           number default 0;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    utl_file.put( l_output,p_colhdg);
    utl_file.new_line( l_output);

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );

    for i in 1 .. 255 loop
    begin
        dbms_sql.define_column( l_theCursor, i,
        l_columnValue, 2000 );
        l_colCnt := i;
    exception
       when others then
        if ( sqlcode = -1007 ) then exit;
        else
            raise;
        end if;
    end;
    end loop;

    dbms_sql.define_column( l_theCursor, 1,l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop
    exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,l_columnValue );
            utl_file.put( l_output,l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end;


This function opens a file on the local file system to which the query results are written.

While this approach worked very well on a single instance database, on a cluster you would need a mapped drive on each node machine.

As the cluster had already been configured it would require a configuration change to each machine in the cluster to set up the mapped drive and the IT risk department ruled it out.
Re: Accessing directory entries in PL/SQL with clustering [message #400804 is a reply to message #399324] Wed, 29 April 2009 12:46 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
We use an NFS mounted drive on each of our unix boxes to share information between them.

We therefore also use UTL_FILE to write to this same location with no extra coding.

Just a directory object pointing to the NFS share location is neeed in Oracle.



Previous Topic: cannot drop a user that is currently connected
Next Topic: Global temporary tables Session issue
Goto Forum:
  


Current Time: Sun Dec 04 08:56:01 CST 2016

Total time taken to generate the page: 0.07363 seconds