Home » SQL & PL/SQL » SQL & PL/SQL » How to get the file name from directory (Oracle 10g)
How to get the file name from directory [message #572475] Wed, 12 December 2012 06:02 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

How to get the file name from directory without Java. Is there any way to do it in Pl/Sql.
Please help me out.

Regards,
Nathan
Re: How to get the file name from directory [message #572476 is a reply to message #572475] Wed, 12 December 2012 06:10 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
What do you mean exactly? You want to list a given directory by using PL/SQL?

Something equivalent to the linux/unix "ls" command or "dir" in windows?


Regards,
Dariyoosh
Re: How to get the file name from directory [message #572479 is a reply to message #572476] Wed, 12 December 2012 06:17 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


I am having list of files in a directory in linux server.And I want to retrieve all the files name and insert into a specific table.

Regards,
Nathan
Re: How to get the file name from directory [message #572481 is a reply to message #572479] Wed, 12 December 2012 06:20 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Write down here the exact form of your file pathnames.


Regards,
Dariyoosh
Re: How to get the file name from directory [message #572484 is a reply to message #572479] Wed, 12 December 2012 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 10g, there is no documented procedure for this.
You can search on AskTom for "dirlist".
(Or you can use at your risks the undocumented dbms_backup_restore.searchfiles procedure.)

Regards
Michel
Re: How to get the file name from directory [message #572485 is a reply to message #572481] Wed, 12 December 2012 06:26 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


The path name like '/application/prods/temp/'.This is a directory created in Linux environment. And inside temp 5 files are there.So I need to insert that names into a table.
Then we have to read each by each.

Regards,
Nathan
Re: How to get the file name from directory [message #572487 is a reply to message #572485] Wed, 12 December 2012 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, the dirlist procedure from T. Kyte directly gives you the answer as it inserts into a table the list of files, you just have to remove the display part.

Regards
Michel
Re: How to get the file name from directory [message #572488 is a reply to message #572485] Wed, 12 December 2012 06:35 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi Michael,

If it is Oracle 11g then ,it would possible or what. I tried with Asktom dirlist but java is causing some issue.

Regards,
Nathan
Re: How to get the file name from directory [message #572490 is a reply to message #572488] Wed, 12 December 2012 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it would be possible.
Fix your problem with Java and it will work.

Regards
Michel
Re: How to get the file name from directory [message #572491 is a reply to message #572488] Wed, 12 December 2012 07:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
It is possible without java. One way is external procedure. Same like java solution you will have to write code but in, let say C. But unlike java solution you will have to make small changes to listener (add extproc library). Second solution is for all versions supporting DBMS_SCHEDULER. You would submit a EXECUTABLE type job which would spool directory listing to a file (using sequence generated value in file name to make it unique), wait till job completes and read listing from that file.

SY.
Re: How to get the file name from directory [message #572529 is a reply to message #572491] Wed, 12 December 2012 23:31 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
CREATE GLOBAL TEMPORARY TABLE dir_list     ( filename VARCHAR2(255) )     
ON COMMIT DELETE ROWS;--create the java proc  

CREATE OR REPLACE        AND COMPILE JAVA SOURCE NAMED "DirList"     AS     

import java.io.*;     
import java.sql.*;        
 public class dirlist     {     public static void getlist(string directory)                      
  throws sqlexception    {        file path = new file( directory );       
 string[] list = path.list();        
  string element;            for(int i = 0; i < list.length; i++)        {            
element = list[i];           
   #sql { insert into dir_list (filename)                   values 
(:element) };        }    }        }
   

CREATE OR REPLACE PROCEDURE get_dir_list (p_directory IN VARCHAR2)
AS
   LANGUAGE JAVA
   NAME 'DirList.getList( java.lang.String )';
    
    
BEGIN
   get_dir_list ('/application/prods/temp');
END;          

select * from dir_list ;


Execute the above code... final select query will list the filelist from the folder '/application/prods/temp'.


Veera

[Updated on: Thu, 13 December 2012 02:13] by Moderator

Report message to a moderator

Re: How to get the file name from directory [message #572550 is a reply to message #572529] Thu, 13 December 2012 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are a cheater, this is T. Kyte code and you post it in an unreadable way.
In addition, OP said "I tried with Asktom dirlist but java is causing some issue."
You are welcome to post and answer but
1) read what has been posted and do not repeat,
2) when something is not from you post its origin and grant the creator.

Regards
Michel

[Edit: typo]

[Updated on: Fri, 14 December 2012 04:20]

Report message to a moderator

Re: How to get the file name from directory [message #572595 is a reply to message #572550] Thu, 13 December 2012 08:37 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member


Thanks to All for responding well, finally the issue got resolved with same java(asktom). Earlier this java program was throwing null pointer exception due to directory path because db server and app server residing in different places. Now the java .class file converted into jar file and moved to app server. And the code works properly.

Regards,
Nathan
Re: How to get the file name from directory [message #572597 is a reply to message #572595] Thu, 13 December 2012 08:41 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, it will help if someone has the same problem with Java.

Regards
Michel
Previous Topic: How to parse pieces of data from a Clob?
Next Topic: insert query issue
Goto Forum:
  


Current Time: Sat Oct 25 08:44:56 CDT 2014

Total time taken to generate the page: 0.60381 seconds