Solved

Load files from a directory using oracle utl package

Posted on 2008-06-09
2
6,043 Views
Last Modified: 2013-12-19
Hi,

On our server files gets created everday.  One process creates a directory with todays date(YYYYMMDD) and puts some files in that directory.  I would like read all files from this directory using utl package.  I do know how to read a single file from a directory using utl package, but I would like read all files in that directory and load into a table.  All files names are with timestamp.  Basically, I want to browse the directory and read all fils from my pl sql code using utl package.

Can this be done?  If yes, can some one provide me sample code please.

Oracle 10G

Thank You
cm
0
Comment
Question by:chandukb
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 21746369
you can't browse the directory with pl/sql.  but you can with java, then wrap your java code in pl/sql.

Here's a quick example way to do it...  You can extend your browsing functionality by appending other file attributes to the string.
You can also have your java code return pl/sql object types instead of a string that needs to be parsed.

This was easiest/smallest for me at the time.

End result of the code below is you get a function "dir_list" that you can query like this....

select  * from table(dir_list('/tmp'));

CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
 
 
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED DirList as 
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
 
public class DirList{
 
    public static oracle.sql.ARRAY getList(java.lang.String p_directory)
    throws java.sql.SQLException,IOException
    {
        File path = new File( p_directory );
        String[] list = path.list();
 
        Connection conn = new OracleDriver().defaultConnection();
        ArrayDescriptor descriptor =
            ArrayDescriptor.createDescriptor( "VCARRAY", conn );
 
        return new ARRAY( descriptor, conn, list );
    }
}
 
 
 
CREATE OR REPLACE FUNCTION raw_dir_list (p_directory IN VARCHAR2)
    RETURN vcarray
AS
    LANGUAGE JAVA
    NAME 'DirList.getList( java.lang.String ) return oracle.sql.ARRAY';
 
CREATE OR REPLACE FUNCTION dir_list (p_directory IN VARCHAR2)
    RETURN file_table PIPELINED
AS
    v_raw_list     vcarray;
    v_size_start   INTEGER;
    v_name_start   INTEGER;
BEGIN
    v_raw_list := raw_dir_list (p_directory);
 
    FOR i IN 1 .. v_raw_list.COUNT
    LOOP
        v_size_start :=   INSTR (v_raw_list (i), ' ', 1, 2)
                        + 1;
        v_name_start :=   INSTR (v_raw_list (i), ' ', 1, 3)
                        + 1;
        PIPE ROW (file_type (SUBSTR (v_raw_list (i), v_name_start),
                             TO_NUMBER (SUBSTR (v_raw_list (i),
                                                v_size_start,
                                                  v_name_start
                                                - v_size_start
                                                - 2
                                               )
                                       ),
                             SUBSTR (v_raw_list (i), 1, 1),
                               TO_DATE ('1970-01-01 00:00:00',
                                        'yyyy-mm-dd hh24:mi:ss'
                                       )
                             +   TO_NUMBER (SUBSTR (v_raw_list (i),
                                                    3,
                                                      v_size_start
                                                    - 4
                                                   )
                                           )
                               / 86400000
                            ));
    END LOOP;
 
    RETURN;
END dir_list;
 
CREATE OR REPLACE TYPE file_type AS OBJECT (
    file_name       VARCHAR2 (512),
    file_size       NUMBER (15, 0),
    file_type       VARCHAR2 (1),
    last_modified   DATE
);
 
CREATE OR REPLACE TYPE file_table AS TABLE OF file_type;

Open in new window

0
 
LVL 2

Author Closing Comment

by:chandukb
ID: 31465527
It was determined to use unix scripts to load data files using sql loader.  But, this information is helpful and I will keep in mind for future.
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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dataware house query tuning 9 81
Wrap Oraccle SQL*Plus executable Command 4 84
pl/sql - query very slow 26 61
Database Design Dilemma 6 39
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

823 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