Home » RDBMS Server » Server Administration » Reading Hard disk file details through plsql (oracle 12c Windows 2012)
Reading Hard disk file details through plsql [message #671909] Sun, 23 September 2018 23:39 Go to next message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi All,

We want to get the information of the file's created/modified date from a directory through plsql code.

Kindly guide me to do it.

Regards
M.Krish
Re: Reading Hard disk file details through plsql [message #671910 is a reply to message #671909] Mon, 24 September 2018 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I could help but I am blocked waiting for your feedback and answer to my question in your previous topic.

[Updated on: Mon, 24 September 2018 00:40]

Report message to a moderator

Re: Reading Hard disk file details through plsql [message #671918 is a reply to message #671910] Mon, 24 September 2018 04:58 Go to previous messageGo to next message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi Michel,

I have replied to our last post.

Please, guide me for this issue also.

Regards
M.krish
Re: Reading Hard disk file details through plsql [message #671922 is a reply to message #671909] Mon, 24 September 2018 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 26538
Registered: January 2009
Location: SoCal
Senior Member
muthukrish104 wrote on Sun, 23 September 2018 21:39
Hi All,

We want to get the information of the file's created/modified date from a directory through plsql code.

Kindly guide me to do it.

Regards
M.Krish

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70915
Re: Reading Hard disk file details through plsql [message #671929 is a reply to message #671922] Mon, 24 September 2018 23:23 Go to previous messageGo to next message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi,

Thanks for the support.

But the sub program in utl_file is giving the result of the file size, but not the created/modified date.

Kindly help me.

Here is the code I had used:

DECLARE
  l_exists     boolean;
  l_size       integer;
  l_block_size integer;
BEGIN
  utl_file.fgetattr( 'EXPBKUP', 
                     'import.log', 
                     l_exists, 
                     l_size, 
                     l_block_size );
   if( l_exists )
   then
     dbms_output.put_line( 'The file exists and has a size of ' || l_size );
   else
     dbms_output.put_line( 'The file does not exist or is not visible to Oracle' );
   end if;
END;

And the output is:


The file exists and has a size of 598


Regards
M.krish
Re: Reading Hard disk file details through plsql [message #671930 is a reply to message #671929] Tue, 25 September 2018 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback and code.

Re: Reading Hard disk file details through plsql [message #671931 is a reply to message #671930] Tue, 25 September 2018 00:46 Go to previous messageGo to next message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi Michel,

But I haven't got what I want.

I want to get the information of the file's creation date.

Kindly guide me.

Regards
M.krish
Re: Reading Hard disk file details through plsql [message #671934 is a reply to message #671931] Tue, 25 September 2018 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a complete package to get file attributes:
CREATE or REPLACE AND COMPILE JAVA SOURCE NAMED "GetFile" AS
 
import java.lang.*;
import java.util.*;
import java.io.*;
import java.sql.Timestamp;
 
public class GetFile {
  private static int KO = 0; 
  private static int OK = 1;

  public static int exists (String p_file) {
    File l_file = new File (p_file);
    if (l_file.exists()) return OK; else return KO;
  }

  public static String name (String p_file) {
    File l_file = new File (p_file);
    return l_file.getName();
  }

  public static String path (String p_file) {
    File l_file = new File (p_file);
    return l_file.getParent();
  }

  public static String absolutePath (String p_file) {
    File l_file = new File (p_file);
    String res = l_file.getAbsolutePath();
    res = res.substring(0,res.length()-l_file.getName().length()-1);
    return res;
  }

  public static int isFile (String p_file) {
    File l_file = new File (p_file);
    if (l_file.isFile()) return OK; else return KO;
  }

  public static int isDirectory (String p_file) {
    File l_file = new File (p_file);
    if (l_file.isDirectory()) return OK; else return KO;
  }

  public static int isHidden (String p_file) {
    File l_file = new File (p_file);
    if (l_file.isHidden()) return OK; else return KO;
  }

  public static int canRead (String p_file) {
    File l_file = new File (p_file);
    if (l_file.canRead()) return OK; else return KO;
  }

  public static int canWrite (String p_file) {
    File l_file = new File (p_file);
    if (l_file.canWrite()) return OK; else return KO;
  }

  public static Timestamp lastModified (String p_file) {
    File l_file = new File (p_file);
    return new Timestamp(l_file.lastModified());
  }

  public static long length (String p_file) {
    File l_file = new File (p_file);
    return l_file.length();
  }
  
}
/
SHOW ERRORS
 
CREATE OR REPLACE PACKAGE get_file AS

  FUNCTION exists (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.exists (java.lang.String) return java.lang.int';

  FUNCTION name (p_file IN VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA 
  NAME 'GetFile.name (java.lang.String) return java.lang.String';

  FUNCTION path (p_file IN VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA 
  NAME 'GetFile.path (java.lang.String) return java.lang.String';

  FUNCTION absolute_path (p_file IN VARCHAR2) RETURN VARCHAR2
  AS LANGUAGE JAVA 
  NAME 'GetFile.absolutePath (java.lang.String) return java.lang.String';

  FUNCTION is_file (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.isFile (java.lang.String) return java.lang.int';

  FUNCTION is_directory (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.isDirectory (java.lang.String) return java.lang.int';

  FUNCTION is_hidden (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.isHidden (java.lang.String) return java.lang.int';

  FUNCTION can_read (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.canRead (java.lang.String) return java.lang.int';

  FUNCTION can_write (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.canWrite (java.lang.String) return java.lang.int';

  FUNCTION last_modified (p_file IN VARCHAR2) RETURN DATE
  AS LANGUAGE JAVA 
  NAME 'GetFile.lastModified (java.lang.String) return java.sql.Timestamp';

  FUNCTION length (p_file IN VARCHAR2) RETURN NUMBER
  AS LANGUAGE JAVA 
  NAME 'GetFile.length (java.lang.String) return java.lang.long';

END;
/
SHOW ERRORS
And an example of execution:
TEST> DECLARE
  2    l_file VARCHAR2(50);
  3  BEGIN
  4    l_file := '.\NETWORK\ADMIN\tnsnames.ora';
  5    dbms_output.put_line(rpad(' ',20)||' '||l_file);
  6    dbms_output.put_line(rpad(' ',20)||' '||rpad('-',length(l_file),'-'));
  7    dbms_output.new_line;
  8    dbms_output.put_line(rpad('Exists?',20,'.')||' '||get_file.exists(l_file));
  9    dbms_output.put_line(rpad('Name',20,'.')||' '||get_file.name(l_file));
 10    dbms_output.put_line(rpad('Path',20,'.')||' '||get_file.path(l_file));
 11    dbms_output.put_line(rpad('Absolute path',20,'.')||' '||get_file.absolute_path(l_file));
 12    dbms_output.put_line(rpad('Is file?',20,'.')||' '||get_file.is_file(l_file));
 13    dbms_output.put_line(rpad('Is directory?',20,'.')||' '||get_file.is_directory(l_file));
 14    dbms_output.put_line(rpad('Is hidden?',20,'.')||' '||get_file.is_hidden(l_file));
 15    dbms_output.put_line(rpad('Can read?',20,'.')||' '||get_file.can_read(l_file));
 16    dbms_output.put_line(rpad('Can write?',20,'.')||' '||get_file.can_write(l_file));
 17    dbms_output.put_line(rpad('Exists',20,'.')||' '||get_file.exists(l_file));
 18    dbms_output.put_line(rpad('Last modified',20,'.')||' '||
 19                         TO_CHAR(get_file.last_modified(l_file),'DD/MM/YYYY HH24:MI:SS'));
 20    dbms_output.put_line(rpad('Length',20,'.')||' '||get_file.length(l_file));
 21  END;
 22  /
                     .\NETWORK\ADMIN\tnsnames.ora
                     ----------------------------

Exists?............. 1
Name................ tnsnames.ora
Path................ .\NETWORK\ADMIN
Absolute path....... E:\ORACLE\ORA11204\.\NETWORK\ADMIN
Is file?............ 1
Is directory?....... 0
Is hidden?.......... 0
Can read?........... 1
Can write?.......... 1
Exists.............. 1
Last modified....... 15/01/2018 11:35:34
Length.............. 4540

PL/SQL procedure successfully completed.
Note that the user must have been granted the following permissions:
GRANT JAVAUSERPRIV TO <user>;
BEGIN
  DBMS_JAVA.grant_permission('<user>', 'java.io.FilePermission', '<<ALL FILES>>', 'read');
  DBMS_JAVA.grant_permission('<user>', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/
And, of course, Java must have been installed inside your database.

Re: Reading Hard disk file details through plsql [message #671937 is a reply to message #671934] Tue, 25 September 2018 04:46 Go to previous messageGo to next message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi,

Thanks a lot for the support.

Regards
M.krish
Re: Reading Hard disk file details through plsql [message #671939 is a reply to message #671937] Tue, 25 September 2018 05:45 Go to previous messageGo to next message
John Watson
Messages: 7933
Registered: January 2010
Location: Global Village
Senior Member
Another approach is an external table populated from an OS command invoked as a preprocessor. The last example in this article,
https://blogs.oracle.com/oraclemagazine/preprocess-external-tables
by Arup Nanda shows how get the output of DIR in a SELECT statement.
Re: Reading Hard disk file details through plsql [message #672029 is a reply to message #671939] Fri, 28 September 2018 07:49 Go to previous message
muthukrish104
Messages: 68
Registered: November 2017
Member
Hi John

Thanks for the support.

Regards
M.Krish
Previous Topic: Oracle DML/Redo Data
Next Topic: How do I reclaim unused space?
Goto Forum:
  


Current Time: Sat Jun 15 20:22:19 CDT 2019