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: 634
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: 538
Registered: March 2009
Location: 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: 634
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: 538
Registered: March 2009
Location: 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: 68625
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: 634
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: 68625
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: 634
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: 68625
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: 3269
Registered: January 2010
Location: Connecticut, USA
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: 68
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: 68625
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: 634
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 messageGo to next message
Michel Cadot
Messages: 68625
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
Re: How to get the file name from directory [message #676842 is a reply to message #572475] Mon, 22 July 2019 07:06 Go to previous messageGo to next message
sundarboopathi
Messages: 1
Registered: July 2019
Junior Member
I'm getting below error while running Java Piece.

It is stating an issue with -- #sql { insert into dir_list (FILENAME) values (:element) };

Can someone help me.

--------------------------------------------------------------------------------------

Java Source DirList created

LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 DirList:13: error: illegal character: '#'
0/0 10 errors
0/0 ^
0/0 DirList:13: error: not a statement
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: ';' expected
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: ';' expected
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: ';' expected
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: illegal start of expression
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: ';' expected
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: illegal start of expression
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:13: error: ';' expected
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
0/0 ^
0/0 DirList:16: error: reached end of file while parsing
0/0 }
0/0 ^
0/0 #sql { insert into dir_list (FILENAME) values (:element) };
Errors: check compiler log
Re: How to get the file name from directory [message #676843 is a reply to message #676842] Mon, 22 July 2019 07:52 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
You really should start your own thread instead of hijacking someone else's 7-year-old thread.
Previous Topic: ALTER INDEX To Add the Extra columns
Next Topic: Case insensitive in oracle 12c
Goto Forum:
  


Current Time: Fri Mar 29 02:32:25 CDT 2024