Home » SQL & PL/SQL » SQL & PL/SQL » Fetching Unix Folder Name and Timestamp in SQL/PLSQL (Oracle 11.2.0.4)
Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670122] Fri, 08 June 2018 14:20 Go to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
I have created a Directory in my Oracle DB Schema pointing to my Unix folder: /dev/product/jobs/PRODUCT_JOBS
Several files get created/updated within each product folder within the above directory on a daily basis through automated scheduled jobs.

I want to be able to fetch a list of all the folder names (product folders) within this Unix directory and their respective last modified timestamps in a TABLE (if PLSQL) or VIEW (if this can be done in a single SQL) in Oracle DB schema.

Folder structure in UNIX as seen with WinSCP
/dev/product/jobs/PRODUCT_JOBS/
Name			Changed
P01_C011101		6/17/2018 11:50:56 AM
P01_C021201		6/17/2018 10:24:23 AM
P01_C021202		6/12/2018 10:54:15 PM
P02_C012101		6/13/2018 09:50:55 PM
P03_C013101		4/15/2018 02:22:09 AM
P03_C033301		5/18/2018 06:12:12 PM
P03_C043401		6/03/2018 06:23:33 AM
Output should be in a Oracle table/view as below
(FOLDER_NAME will be last four characters of actual folder name in UNIX)
FOLDER_NAME		DATE_MODIFIED_TS
1101			6/17/2018 11:50:56 AM
1201			6/17/2018 10:24:23 AM
1202			6/12/2018 10:54:15 PM
2101			6/13/2018 09:50:55 PM
3101			4/15/2018 02:22:09 AM
3301			5/18/2018 06:12:12 PM
3401			6/03/2018 06:23:33 AM

Could you please help? Thanks.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670123 is a reply to message #670122] Fri, 08 June 2018 15:17 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
I would approach this requirement from the OS side; not from within Oracle DB.
I would decide upon a fixed OS filename (directory_list.txt) that resides within the target directory.
ls -ld > directory_list.txt
I would issue necessary SQL to map directory_list.txt as EXTERNAL TABLE.
Then Oracle could query this table when ever it is needed.
If necessary a DBMS_SCHEDULER job could be established to refresh directory_list.txt as needed.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670124 is a reply to message #670122] Fri, 08 June 2018 16:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2788
Registered: January 2010
Location: Connecticut, USA
Senior Member
External table with preprocessor.

SY.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670126 is a reply to message #670122] Sat, 09 June 2018 00:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65764
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


https://asktom.oracle.com/pls/asktom/asktom.search?tag=plsql-code-to-search-and-find-pdf-from-folder-and-send-in-email-as-attachment
https://asktom.oracle.com/pls/asktom/asktom.search?tag=external-table-preprocessing
http://www.oracle-developer.net/display.php?id=513

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670127 is a reply to message #670122] Sat, 09 June 2018 00:30 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
The hacker's way:
orclx>
orclx> conn / as sysdba
Connected.
orclx> var ns varchar2(1024)
orclx> var dirname varchar2(1024)
orclx> exec :dirname:='c:\tmp'

PL/SQL procedure successfully completed.

orclx> exec sys.dbms_backup_restore.searchfiles(:dirname,:ns)

PL/SQL procedure successfully completed.

orclx> select fname_krbmsft from x$krbmsft where rownum < 10;

FNAME_KRBMSFT
--------------------------------------------------------------------------------------------------------------
C:\tmp\12.2_new_type
C:\tmp\access_log
C:\tmp\access_log-20180401
C:\tmp\airline_ticket.pdf
C:\tmp\apex_logons.txt
C:\tmp\apex_verify_out.html
C:\tmp\aprod_client_page_views.txt
C:\tmp\apxpatch.log
C:\tmp\ASH16742AL.pdf

9 rows selected.

orclx>
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670128 is a reply to message #670123] Sat, 09 June 2018 02:28 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thanks BlackSwan, this really helped. I will try it and will get back if there are any hiccups, which I am sure I will face. Never used a preprocessor before. Also, I am not quite sure about the Unix commands, but I'll google and try to create the batch files to create the directory listings.

Two questions here though:
1. You mentioned that you would rather approach it from OS side than Oracle side. So, does it mean it can be done directly from Oracle without touching the Unix part (i.e. without creating the batch files)?
2. Is there a way to automate this without a scheduled job? I want to be able to see the latest folders within that directory and associated timestamps whenever I query the Oracle table/view.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670129 is a reply to message #670124] Sat, 09 June 2018 02:29 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thanks SY, I'll try it out and will provide an update.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670130 is a reply to message #670127] Sat, 09 June 2018 02:31 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thanks John, but I guess this won't work for me since I don't have SYSDBA access. Also, I will have to have it as some kind of PLSQL program or SQL query.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670131 is a reply to message #670126] Sat, 09 June 2018 02:44 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thanks Michel, the last link is very helpful for me since I will be working with preprocessor for the first time - it takes step by step with an example.

I have few questions on the example shown in the 3rd link:
1. In the example, the read-write Oracle directory and the executable Oracle directory can be the ssame right? As in my case, both can be PROD_JOBS_DIR which points to /dev/product/jobs/PRODUCT_JOBS
2. How do I modify the CREATE TABLE script to list only directories instead of files?
3. How do I modify the CREATE TABLE script to store only the DIRECTORY_NAME and LAST_MODFIED_TS (Date & Time AM/PM)? I don't need to see permissions and file/folder size.
4. Why is sticky.txt required if we are not using it at all. I didn't find a good explanation for it.


Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670133 is a reply to message #670131] Sat, 09 June 2018 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65764
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. Yes
2. You don't modify the CREATE TABLE, you modify the script you call, BlackSwan gave you the command to execute in Unix.
3. Put the columns you want in the table and modify the script to return only these columns: "man ls" + maybe sed/cut/awk, it is shell programming not Oracle
4. LOCATION is a mandatory parameter of external table definition

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670134 is a reply to message #670131] Sat, 09 June 2018 04:10 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
Quote:
1. In the example, the read-write Oracle directory and the executable Oracle directory can be the ssame right? As in my case, both can be PROD_JOBS_DIR which points to /dev/product/jobs/PRODUCT_JOBS
You can do this, but it is a security risk. For example, it makes it possible for a user to write a shell script to the directory and then run it. It will run with the OS privileges of the Oracle owner, so your user can escalate his privileges horrifically.
icon14.gif  Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670181 is a reply to message #670122] Thu, 14 June 2018 19:18 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thank you @BlackSwan @Solomon @Michel @John for all your help and guidance. The links and your directions really helped me complete this.
Here is what I did. I guess it would be helpful in case someone else stumbles upon this post in future with same issue/query.

1. CREATE DIRECTORY
in Oracle DB and grant EXECUTE permissions to XYZ_USER schema to be able to run scripts in Unix folder (from SYS). My Unix Shell Script will reside within /dev/product/jobs
CREATE DIRECTORY DIR_PRODUCT_JOBS AS '/dev/product/jobs';
/
GRANT EXECUTE ON DIRECTORY DIR_PRODUCT_JOBS TO XYZ_USER
/

2. CREATE UNIX SHELL SCRIPT
to display folder name (not files) and last modified time from the "/dev/product/jobs/PRODUCT_JOBS" folder in UNIX.
I created a Unix shell script "dir_list.sh" which when called ( "$ ./dir_list.sh" ) searches within the target folder specified in the script and displays the folder names along with its associated timestamp as a comma-separated records. It utilizes Perl scripting since GNU commands like stat, --full-time, --time-style, etc. doesn't work in my HP-UX B.11.11 OS. This shell script will later be used for the Oracle external table to execute in it's preprocessor directive.
Found some help here: https://unix.stackexchange.com/questions/449766/get-list-of-directories-and-timestamp-in-a-specific-format-in-hp-ux
#! /usr/bin/ksh
# dir_list.sh : Generate a comma separated directory list with last modified timestamp
#
# Navigate to the Target Directory
cd /dev/product/jobs/PRODUCT_JOBS || exit
#
# Execute Perl script to format the output
/usr/bin/perl -MPOSIX -MFcntl -MFile::stat -le '
  setlocale(LC_TIME, "C");
  for (<*>) {
    $s = lstat $_ or die "$_: $!\n";
    print "$_," . uc(strftime("%d-%b-%Y %I:%M:%S %p", localtime $s->mtime))
      if S_ISDIR($s->mode)
  }'
#
exit 0

3. CREATE EXTERNAL TABLE using PREPROCESSOR
While logged in as XYZ_USER in Oracle DB, I created the below External table which will run the shell script and dump the output in this table. I have set both my location file and the script file to be the same, since location file is of no use in this scenario, but is a mandatory clause for External Preprocessor table, hence can't exclude it.
CREATE TABLE T_JOBS_DIR_LIST
(
   job_folder         VARCHAR2 ( 100 )
 , last_modified_ts   VARCHAR2 ( 50 )
)
ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
         DEFAULT DIRECTORY DIR_PRODUCT_JOBS
            ACCESS PARAMETERS (
               RECORDS DELIMITED BY NEWLINE
               NOLOGFILE
               PREPROCESSOR DIR_PRODUCT_JOBS : 'dir_list.sh'
               FIELDS
                  TERMINATED BY ','
            )
         LOCATION ( 'dir_list.sh' ))
   REJECT LIMIT UNLIMITED
/

4. QUERY THE TABLE
to display all the folder names (excluding files) and their associated timestamps within the "/dev/product/jobs/PRODUCT_JOBS" directory. I don't even have to do any kind of data cleaning since I already have the data in the format I need, thanks to the Perl script in my shell script code. Any time I query the table, it always retrieves the latest data.
SELECT * FROM XYZ_USER.T_JOBS_DIR_LIST 
ORDER BY TO_DATE(last_modified_ts) DESC;

Please let me know if I have stated anything incorrectly.
Thanks again.
Smile

[Updated on: Thu, 14 June 2018 19:21]

Report message to a moderator

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670183 is a reply to message #670181] Fri, 15 June 2018 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65764
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the detailed solution.

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670186 is a reply to message #670181] Fri, 15 June 2018 06:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2788
Registered: January 2010
Location: Connecticut, USA
Senior Member
Serious security hole. Pre-processor shell script must be in separate directory. Otherwise user selecting from external table must be granted all read, write and execute on directory object. As a result nothing prevents user from writing malicious shell script to directory using UTL_FILE and creating external table with that malicious shell script as pre-processor. Select form external table spawns OS process on database server that runs under OS user oracle (as OS user Oracle software was installed, to be precise) giving client all the access OS user oracle has. By creating pre-processor in separate directory you provide client read/write on external table directory and execute on pre-processor directory. This way client can write shell script to external table directory but can't execute it.
But much better solution is completely separate directory access. Create database user DIRECTORY_ACCESS which will be only database user granted access to Oracle directory objects and will own all external tables. Then create table/pipelined functions selecting from external tables and grant execute on such functions to database users.

SY.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670232 is a reply to message #670186] Tue, 19 June 2018 15:43 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
I am encountering an issue here.

The External table T_JOBS_DIR_LIST resides under XYZ_USER DB schema, which has READ, WRITE, EXECUTE permissions on the Unix shell script residing under the directory DIR_PRODUCT_JOBS.

I created a view XYZ_USER.V_JOBS_DIR_LIST which queries the data from external table XYZ_USER.T_JOBS_DIR_LIST. I queried this view from its own XYZ_USER schema, and I am able to see the data.

I granted SELECT access to all other users to this view, e.g.: GRANT SELECT ON XYZ_USER.V_JOBS_DIR_LIST to USER_ABC

Now, when USER_ABC tries to query the view XYZ_USER.V_JOBS_DIR_LIST, it doesn't return any data. It just throws an error:
ORA-06564: object DIR_PRODUCT_JOBS does not exist.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670234 is a reply to message #670232] Tue, 19 June 2018 15:52 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
nickz wrote on Tue, 19 June 2018 13:43
I am encountering an issue here.

The External table T_JOBS_DIR_LIST resides under XYZ_USER DB schema, which has READ, WRITE, EXECUTE permissions on the Unix shell script residing under the directory DIR_PRODUCT_JOBS.

I created a view XYZ_USER.V_JOBS_DIR_LIST which queries the data from external table XYZ_USER.T_JOBS_DIR_LIST. I queried this view from its own XYZ_USER schema, and I am able to see the data.

I granted SELECT access to all other users to this view, e.g.: GRANT SELECT ON XYZ_USER.V_JOBS_DIR_LIST to USER_ABC

Now, when USER_ABC tries to query the view XYZ_USER.V_JOBS_DIR_LIST, it doesn't return any data. It just throws an error:
ORA-06564: object DIR_PRODUCT_JOBS does not exist.
Oracle is behaving as expected.
XYZ_USER does not own any DIR_PRODUCT_JOBS directory object & has no privilege to access XYZ_USER directory object.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670235 is a reply to message #670234] Tue, 19 June 2018 15:59 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
So, do I need to provide READ access on DIR_PRODUCT_JOBS directory (from SYS schema since SYS owns it) to each user who needs to see the data in the view XYZ_USER.V_JOBS_DIR_LIST ?

Is there any other way so that other users can query the data in the view XYZ_USER.V_JOBS_DIR_LIST ?

[Updated on: Tue, 19 June 2018 16:00]

Report message to a moderator

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670236 is a reply to message #670235] Tue, 19 June 2018 16:08 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
nickz wrote on Tue, 19 June 2018 13:59
So, do I need to provide READ access on DIR_PRODUCT_JOBS directory (from SYS schema since SYS owns it) to each user who needs to see the data in the view XYZ_USER.V_JOBS_DIR_LIST ?

Is there any other way so that other users can query the data in the view XYZ_USER.V_JOBS_DIR_LIST ?
have XYZ_USER own PL/SQL procedure (READ_T_JOBS_DIR_LIST) that does SELECT * FROM T_JOBS_DIR_LIST;
GRANT EXECUTE ON READ_T_JOBS_DIR_LIST TO USER_ABC;
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670237 is a reply to message #670236] Tue, 19 June 2018 16:28 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Can it not be done with the View (instead of procedure)? Actually, the idea is to join this view with some other views to assemble related data which the users can then directly query. What needs to be done so that the View XYZ_USER.V_JOBS_DIR_LIST is readable and accessible by the user USER_ABC?

[Updated on: Tue, 19 June 2018 16:33]

Report message to a moderator

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670238 is a reply to message #670237] Tue, 19 June 2018 23:17 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Okay, so I found an explanation for this issue in the link below. Looks like it's a bug/missing functionality in current Oracle versions.
http://blog.trivadis.com/b/danischnider/archive/2014/06/26/external-tables-and-privileges.aspx

VIEWS OVER EXTERNAL TABLES ( Doc ID 1530608.1 )
Bug 8755195 : TOO MUCH PRIVILEGES REQUIRED FOR VIEWS OVER EXTERNAL TABLES

Ideally, granting a select on the View to other users should be enough. But, because of the way External tables currently work in Oracle, there are 3 things that needs to be provided to another user for them to be able to query data from the view:

1. Grant Select on the View (the view used to query the External Table) to the User
2. Grant Select on the External Table to the User
3. Grant READ privilege on the Directory (containing the pre-processor script) to the User

Not sure if there are any workarounds to this issue, in case I don't want to grant any access on the directory or external table to any user.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670240 is a reply to message #670238] Tue, 19 June 2018 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 26139
Registered: January 2009
Location: SoCal
Senior Member
nickz wrote on Tue, 19 June 2018 21:17
Okay, so I found an explanation for this issue in the link below. Looks like it's a bug/missing functionality in current Oracle versions.
http://blog.trivadis.com/b/danischnider/archive/2014/06/26/external-tables-and-privileges.aspx

VIEWS OVER EXTERNAL TABLES ( Doc ID 1530608.1 )
Bug 8755195 : TOO MUCH PRIVILEGES REQUIRED FOR VIEWS OVER EXTERNAL TABLES

Ideally, granting a select on the View to other users should be enough. But, because of the way External tables currently work in Oracle, there are 3 things that needs to be provided to another user for them to be able to query data from the view:

1. Grant Select on the View (the view used to query the External Table) to the User
2. Grant Select on the External Table to the User
3. Grant READ privilege on the Directory (containing the pre-processor script) to the User

Not sure if there are any workarounds to this issue, in case I don't want to grant any access on the directory or external table to any user.
Consider 2 possible alternatives.
1) have the directory owned by PUBLIC
2) actually load the data from file into regular heap table & proceed from there.
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670242 is a reply to message #670240] Wed, 20 June 2018 00:47 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Thanks for the options BlackSwan. Actually, I liked the idea of External Table with pre-processor since it allows for an on-demand real-time data fetch which is what I exactly wanted. No fuss with scheduled jobs, etc. I can't have that functionality with a regular Heap table.

For the first option you mentioned, TWO QUESTIONS:
1. How can I change the ownership of a directory to PUBLIC ? Currently, SYS is the Owner.
2. If I change the ownership to PUBLIC, then what are the impacts? I guess anyone will be able to READ< WRITE, EXECUTE, DROP the directory, right?

Otherwise, I guess at this point, my only option is to:
> create a role
> grant SELECT privilege on view and ext table to role
> grant READ privilege on the pre-processor directory to the role (I have NOLOGFILE enabled in ext table, so I guess only READ privilege will do)
> assign this role to the users who need access to the view from then on for easy maintenance

The main schema where the external table and view resides is XYZ_USER, as in my example. Currently, XYZ_USER has all READ, WRITE, EXECUTE privileges on the directory DIR_PRODUCT_JOBS. A QUESTION here. Do you think WRITE privilege is required by XYZ_USER ? Or only READ and EXECUTE will do, considering the below is how my external table is defined (NOLOGFILE) ?

CREATE TABLE T_JOBS_DIR_LIST
(
   job_folder         VARCHAR2 ( 100 )
 , last_modified_ts   VARCHAR2 ( 50 )
)
ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
         DEFAULT DIRECTORY DIR_PRODUCT_JOBS
            ACCESS PARAMETERS (
               RECORDS DELIMITED BY NEWLINE
               NOLOGFILE
               PREPROCESSOR DIR_PRODUCT_JOBS : 'dir_list.sh'
               FIELDS
                  TERMINATED BY ','
            )
         LOCATION ( 'dir_list.sh' ))
   REJECT LIMIT UNLIMITED
/
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670243 is a reply to message #670242] Wed, 20 June 2018 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65764
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
1. How can I change the ownership of a directory to PUBLIC ? Currently, SYS is the Owner.

A directory is ALWAYS owned by SYS.

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670265 is a reply to message #670242] Wed, 20 June 2018 08:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2788
Registered: January 2010
Location: Connecticut, USA
Senior Member
As Michel said, CREATE DIRECTORY always creates directory object in SYS schema and is granting READ, WRITE, EXECUTE on directory object to user issuing CREATE DIRECTORY. Anyway, as I already mentioned secure way would be having DIRECTORY_MASTER user that is the only user with READ, WRITE, EXECUTE on directory. Create external table in DIRECTORY_MASTER schema. Create pipelined function in DIRECTORY_MASTER schema. Then grant execute on pipelined function to desired users.

SY.

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670266 is a reply to message #670265] Wed, 20 June 2018 09:44 Go to previous messageGo to next message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Hi Solomon.. Thanks for the tips. The XYZ_USER is actually the master schema holding the External table and has exclusive Read, Write, Execute access to the directory. I have put the schema name as XYZ_USER just as an example.

Could you please elaborate more on "Create pipelined function in DIRECTORY_MASTER schema.". So, as I understand, this pipelined function would read the external table and return the records as a TABLE Object type ? If the users do a SELECT XYZ_USER.FN_GET_DIR_LIST FROM DUAL (of even if I create a view calling the function), wouldn't they just get a record type returned rather than the individual records?

Actually, I want the users to be able to query it simply like a view/table, so that they can use it easily to join with other views/tables. Is that not an option here?
Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670273 is a reply to message #670266] Wed, 20 June 2018 13:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2788
Registered: January 2010
Location: Connecticut, USA
Senior Member
Below is example to read trace file directory:

Step 1. Login as user DIRECTORY_MASTER.

Step 2. Create trace file directory:

column trace_file_dir new_value trace_file_dir
select  value trace_file_dir
  from  v$diag_info
  where name like 'Diag Trace'
/
drop directory trace_file_dir
/
create or replace
  directory trace_file_dir as '&trace_file_dir'
/

Step 3. Create external table to list trace file directory with ls -ltr.

a) Create PREP directory:

drop directory prep
/
create or replace
  directory prep as '/prep'
/

b) Create shell script /prep/ls_ltr:

#!/bin/ksh
/bin/ls -ltr $1

c) Create external table:

create table trace_file_dir_list(
                                 line varchar2(4000)
                                )
  organization external(
                        type oracle_loader
                        default directory trace_file_dir
                        access parameters(
                                          records delimited by newline
                                          skip 1
                                          nobadfile nologfile
                                          preprocessor prep:ls_ltr
                                          fields rtrim(line char(1000))
                                         )
                        location('.')  
                       )
  reject limit unlimited
/

Step 4. Create pipelined function:

create or replace
  function trace_file_dir_list_fn
    return sys.odcivarchar2list
    pipelined
    is
        cursor v_cur
          is
            select  *
              from  trace_file_dir_list;
    begin
        for v_rec in v_cur loop
          pipe row(v_rec.line);
        end loop;
end;
/

Step 5. Create view:

create or replace
  view trace_file_dir_list_vw
    as
      select  *
        from  table(trace_file_dir_list_fn)
/

Step 6. Grant SELECT on the view to desired user, e.g. USER1:

grant select on trace_file_dir_list_vw to user1
/

Step 6. Login as USER1 and issue:

SQL> select  *
  2    from  directory_master.trace_file_dir_list_vw
  3    where rownum <= 3
  4  /

COLUMN_VALUE
--------------------------------------------------------------------------------------
-rw-rw-r-x 1 oracle dba   158728 Mar 26  2015 alert_bi2d4.log.12987_032615.bz2.gz
-rw-rw-r-- 1 oracle dba    50153 Apr 17  2015 alert_bi2d4.log.28327_041715.bz2.gz
-rw-rw-r-- 1 oracle dba   639527 Apr 18  2015 alert_bi2d4.log.31269_041815.bz2.gz

SQL> 

SY.

[Updated on: Wed, 20 June 2018 18:39]

Report message to a moderator

Re: Fetching Unix Folder Name and Timestamp in SQL/PLSQL [message #670304 is a reply to message #670273] Mon, 25 June 2018 14:05 Go to previous message
nickz
Messages: 38
Registered: September 2013
Location: US
Member
Solomon Yakobson wrote on Wed, 20 June 2018 23:38

Step 5. Create view:

create or replace
  view trace_file_dir_list_vw
    as
      select  *
        from  table(trace_file_dir_list_fn)
/
This is exactly what I needed. Thanks SY for your help, I have it implemented as a function now which gathers the information from the view. Smile
Previous Topic: Difference between two dates
Next Topic: How to replace the new line and line feed in a specific manner
Goto Forum:
  


Current Time: Sat Sep 22 21:18:15 CDT 2018