Monthly Archives: August 2013

Listing files from a Linux directory from within Oracle SQL or PL/SQL in 11g

In this article I will describe a method which enables you to list the contents of a Linux directory from within Oracle using the 11gR2 Preprocessor feature of Oracle External Tables.

The example that follows was built using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10

After logging into a database session as a SYSDBA user, run the following commands:

CREATE DIRECTORY PREPROCESSOR_DIRECTORY AS '/home/oracle/preprocessor_scripts'
/
GRANT EXECUTE ON DIRECTORY PREPROCESSOR_DIRECTORY TO HR
/

The first command creates a new directory object, called PREPROCESSOR_DIRECTORY which points to a location on the file system.

The next command grants the execute privilege on the directory to the HR user. The execute privilege allows the HR to run scripts in this folder.

After switching to the file system and to the location specified by the directory object, create the following bash script and save it as list_files.sh (The file owner in my example is oracle)

#!/bin/bash
cd /home/oracle
/bin/ls -l

As you can see the script changes directory to the home directory and then lists the files.

The next step is to start a new database session as the HR user and create the following External Table:

CREATE TABLE home_directory
(
   fpermissions   VARCHAR2(10),
   flink          VARCHAR2(2),
   fowner         VARCHAR2(6),
   fgroup         VARCHAR2(6),
   fsize          VARCHAR2(100),
   fdate          VARCHAR2(100),
   fname          VARCHAR2(255)
)   
   ORGANIZATION EXTERNAL
   (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY PREPROCESSOR_DIRECTORY
      ACCESS PARAMETERS
      (                
       records delimited by newline
       preprocessor PREPROCESSOR_DIRECTORY:'list_files.sh'              
       fields terminated by whitespace               
       (
          fpermissions   position(1:10),
          flink          position(12:14),
          fowner         position(15:21),
          fgroup         position(22:28),
          fsize          position(28:37),
          fdate          position(37:49),
          fname          position(50:305)
       )
      )
   LOCATION ('list_files.sh')   
   )
REJECT LIMIT UNLIMITED    
/

The majority of the code shown above is the standard for creating an Oracle External Table. I will highlight the key points:

Line 18 shows the preprocessor command that calls the bash script, list_files.sh created in the preceding step. In order for this command to run, the user requires the execute privilege on the PREPROCESSOR_DIRECTORY object (which was granted here).

Lines 21 – 27 slices up the output from the bash script into the columns of the External Table.

Line 30 The location clause; Although not used in this example, this clause is mandatory, so I have pointed to the same bash script.

With the External Table in place, you can query to check what the output look like.

SELECT *
  FROM home_directory
/

Running this SQL on my environment produces the following output:

x

Whilst this proves you can now query the Linux directory from SQL, the output is relatively “raw”. The first line returned shows the total number of blocks used by files included in the directory listing and there are directories and files mixed in together.

You could go back and amend the External Table definition to exclude information that is not required, I prefer to clean up the output by creating a view of the data that I want to see. I like this method because when the requirements change and I need to see different information from the home directory I can simply create another view that returns that information.

Here is a view that just returns the files from the home directory:

CREATE OR REPLACE VIEW files_view
AS
   SELECT hd.fpermissions file_permissions,             
          hd.fowner       file_owner,  
          hd.fgroup       file_group,  
          hd.fsize        file_size, 
          hd.fdate        file_date,  
          hd.fname        file_name  
     FROM home_directory hd
    WHERE hd.fpermissions NOT LIKE 'total%'
      AND hd.fpermissions NOT LIKE 'd%'
/

Line 10 Excludes the total number of blocks row

Line 11 Excludes directories

With the view created I can now get query it to see just the files in the home directory:

SELECT fv.* 
  FROM files_view fv
/
 

Capture

Summary

In this article I have shown a method available that allows you to obtain a listing of a Linux directory.

Acknowledgements

This post was inspired by Adrian Billington’s comprehensive post on Listing Files with the External table preprocessorĀ 

Sorting an Oracle Nested Table

This article demonstrates a method used to sort an Oracle nested table collection from within PL/SQL .

The example that follows was built using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10
The user developing and running the code is logged in as the HR user.

The first step is to create a couple of schema level objects.

CREATE TYPE fruit_object AS OBJECT
(
   ID         NUMBER(10),
   FRUIT_NAME VARCHAR2(30)
);
/

CREATE TYPE fruit_table AS TABLE OF fruit_object;
/

The first type to be created is an object which contains the necessary attributes, which in this example is an identifier and a fruit name. Next a nested table is created based upon this object.

The objects need to be created at the database level and not within the PL/SQL code so that the SQL engine can “see” them. If anyone has a method which allows these objects to reside within PL/SQL please let me know via the comments.

With these structures in place, the code to populate and sort the collection is shown below. In order to keep the amount of code to a minimum, I have used a PL/SQL anonymous block.

DECLARE

 lnt_not_sorted  fruit_table := fruit_table();
 lnt_sorted      fruit_table := fruit_table();

BEGIN

 lnt_not_sorted.extend(3);

 lnt_not_sorted(1) := fruit_object(2, 'Banana');

 lnt_not_sorted(2) := fruit_object(99, 'Pineapple');

 lnt_not_sorted(3) := fruit_object(1, 'Apple');

 dbms_output.put_line('Unsorted...');

 FOR i IN 1 .. lnt_not_sorted.COUNT()
 LOOP

  dbms_output.put_line(lnt_not_sorted(i).id || ' ' || lnt_not_sorted(i).fruit_name );

 END LOOP;

 SELECT CAST(MULTISET(SELECT *
                        FROM TABLE(lnt_not_sorted)
                       ORDER BY 1 ) AS fruit_table )
 INTO lnt_sorted
 FROM dual;

 dbms_output.put_line('Sorted...');

 FOR i IN 1 .. lnt_sorted.COUNT()
 LOOP

  dbms_output.put_line(lnt_sorted(i).id || ' ' || lnt_sorted(i).fruit_name );

 END LOOP;

END;

In the declaration section (lines 3 & 4), I declare two variables of the nested table type that was created earlier. As the names reveal, one will be used for the initial unsorted records and another will hold the sorted records. Nested table collections need to be initialised before use and this has done here in the declaration section.

Moving to the executable part of the block, lines 8 – 14 makes space within the collection and then populates it with three rows. Each row of the collection, containing an id and fruit name is populated using the default constructor for the object. The id value will determine where each row should appear once the collection has been sorted.

Lines 18 – 23 output the collection showing its current unsorted order.

Lines 25 – 29, show the most relevant part of the code because this is where the collection is sorted. The SQL used may appear strange the first time you encounter it.

Starting from the inner select statement, The TABLE() operator converts the nested table collection into a row source that allows it to be used in a select statement, note the order by is on the first (id) column. Next the MULTISET operator is used, which tells Oracle to put the multiple rows returned by a select into a single collection object. Finally using the CAST function, Oracle is explicitly told what datatype to convert this to.

With the sorted collection now populated, the contents are displayed to prove they have been sorted.

When run the output of this anonymous block is:

Capture

Summary

In this article I have demonstrated one method that allows the sorting of a Nested Table collection.

Acknowledgements:

oracle-developer.net For the explanation of the Table Operator

The CAST\Multiset explanation is based upon Tony Andrews superb answer to the following question.