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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
SujithData ArchitectCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.