Retrieving modified date from file

I need to get the "date modified" from a file using pl/sql. I've looked at UTL_FILE, but I can't seem to find anything there. Is there a way to retrieve this date?
ContestoasAsked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
Try this.
CREATE or REPLACE JAVA SOURCE NAMED "GetFileDetailsJava" AS
 
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import java.util.Date;
import java.text.*;
import java.text.DateFormat.*;
 
public class CheckFile
{
 public static String lastModified(String fileName)
 {
  return new java.sql.Date(new File(fileName).lastModified()).toString();
 }
}
 
CREATE FUNCTION get_modified_date (filename IN VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME CheckFile.lastModified(String) return String;
 
DECLARE
v_date DATE(25);
BEGIN
v_date := TO_DATE(get_modified_date('myfile.txt'),'yyyy-mm-dd');
DBMS_OUTPUT.PUT_LINE('Last Modified Date for myfile.txt : ' || v_date);
END;

Open in new window

0
 
Jinesh KamdarCommented:
The UTL_FILE package provides text file I/O from within PL/SQL. It exposes a function called GET_LINE that is used to read the contents of the file.

GET_LINE - A procedure that reads a line of text from the opened file and places the text in the output buffer parameter (the maximum size of an input record is 1,023 bytes unless you specify a larger size in the overloaded version of FOPEN)

Once you read a line of data from the file, you have to parse it and extract the data in the required format. E.g. If myfile.txt contains a date in the first line as 02-FEB-2008, then the below code will retrieve the line from the file, convert it into a date-type and then display it on the prompt.
DECLARE
v_filehandle UTL_FILE.FILE_TYPE;
v_buffer     VARCHAR2(4000);
v_date       DATE;
BEGIN
v_filehandle := UTL_FILE.FOPEN ('MYDIR', myfile.txt,'r');
UTL_FILE.GET_LINE(v_filehandle,v_buffer);
v_date := TO_DATE(v_buffer,'DD-MON-YYYY');
DBMS_OUTPUT.PUT_LINE('File-Date : ' || v_date);
END;

Open in new window

0
 
ContestoasAuthor Commented:
jinesh_kamdar,

Thank you for your input, but I'm not looking for a date inside a text file. I'm looking for the file attribute called modified date in Windows. I attached a picture to show what I mean.
windows.jpg
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sujith80Commented:
Contestoas:
Oracle doesnt provide any interfaces to directly access the file properties that you are looking for. UTL_FILE has a routine called FGETATTR, that gets you the block size and length of the file only.
Workarounds are:
- write a pro*C program to read the properties using the OS commands
- write a Java stored procedure to do the same
0
 
Jinesh KamdarCommented:
Ohhhh, silly me! I don't think u can get that info. directly using PL/SQL though there is a procedure in UTL_FILE that provides 2 file attributes - file_length (length of the file in bytes) and block_size (filesystem block size in bytes).
0
 
ContestoasAuthor Commented:
Ok, I'm trying out the Java workaround and found an example I believe should work. I receive "Create or replace java statement processed" in sql developer. I assume it's ok. How do I invoke this class from sql?


CREATE or REPLACE JAVA SOURCE NAMED "GetFileDetailsJava" AS
 
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import java.util.Date;
import java.text.*;
import java.text.DateFormat.*;
 
 
public class CheckFile {
    
 
   public static long lastModified (String fileName) {
      return new File (fileName).lastModified();
      }
 
 
}

Open in new window

0
 
Jinesh KamdarCommented:
Correct this line in the above code - NAME 'CheckFile.lastModified(String) return String';
0
 
ContestoasAuthor Commented:
Thank you for the answer. I did get an error when running your code, but it was close enough. In the end I found this example:

http://www.oracle-base.com/articles/8i/FileHandlingFromPLSQL.php
0
 
Jinesh KamdarCommented:
Glad to be of help :)
0
 
messayedCommented:
sorry but i have an error :

ORA-29531: no method lastModified in class CheckFile
ORA-06512: at "SCOTT.GET_MODIFIED_DATE", line 1
ORA-06512: at line 4

Could you help me?
0
 
messayedCommented:
sorry but i have an error :

ORA-29531: no method lastModified in class CheckFile
ORA-06512: at "SCOTT.GET_MODIFIED_DATE", line 1
ORA-06512: at line 4

Could you help me?
0
 
skonlaCommented:
I had the same problem.
try to correct this line in the above code - NAME 'CheckFile.LM(java.lang.String) return String'
(difference is java.lang.String <> String)
it should work like this. it worked for me.
0
All Courses

From novice to tech pro — start learning today.