A reader, March 07, - pm UTC. March 07, - pm UTC. Was expecting one of: " sql" I cut and paste your code - failed I cut and paste my original code - success therefore, you have a cut and paste error somewhere, find it. Thanks Tom, that worked. I have tried with both the inputs: 1. A reader, March 08, - am UTC. Tom, You are right, I am refering to a subdirectory. Is there a way to refer to a subdirectory of a remote server? Thanks for your help. March 08, - am UTC. And the subfolder has 10 files.
What is the mistake? March 08, - pm UTC. So, I've a feeling YOU have a directory by that name, but the server does not. Hi, I came here when I had the problem of reading a directory list under 8. For that, thanks Now I have to write the same thing against an XE database and this is killing me. Is there an easy way to do it? I do not know anything about external procedures, but if that is the only way I have to read up on them I fear.
For this thing do I have to write an own external procedure in C or whatever or can I just execute a "dir" command and capture the output? I found one of your old posts from on groups. I think I will write something like that. Shehab, July 18, - am UTC. A reader, October 05, - am UTC. Hi, This is what I was looking for. I have files in the server and I need to get the file names into the Application express applciation.
As of now I am trying with the directory available on my local system. Can you please address me where I am going wrong? Thanks in Advance. Execuse me for my previous posting. Hi Tom, Can i install the Oracle8i 8. S 4 it is possible r not r either i had to install on A. S 4" could be clueless as to what "A. Hoping that this has not been covered before, I came to this article because I wanted to perform a directory listing on an XE database. And then I found that XE does not support Java But here's the trick: these data files have a uniform name that includes a date stamp.
And that's how I can process the next file, whatever its name. It's not pretty, but as I said the machine is otherwise idle so why not get it to "probe" the target directory and look for files in a dumb fashion. Hope this idea is deemed useful; sincere thanks to Tom for all his dedication.
Regards Mungo Henning. I resolved the SYS. Let's use a table function File; import java. FilenameFilter; import java. IOException; import java. Connection; import java. SQLException; import java. ResultSet; import java. PreparedStatement; import java. Timestamp; import oracle.
ARRAY; import oracle. ArrayDescriptor; import oracle. StructDescriptor; import oracle. String return oracle. Hi, i've developped a set of packages 11g , and one of them does the "list directory contents" and provides the output in a pipelined table with all properties that are available from Java.
I've alos added checksum and Mime-Type features so you can easily list files, check corruption, file types straight from SQL. Regards, Adrien. Michel Cadot correctly noted that Mette's modified DirList which retrieves filename, length, type and date modified requires "read privilege on all the files in the directory and not only on the directory itself.
Without this privilege i. The filename itself is case-insensitive. Hi Tom, I tried all the step you have explained correctly. Since there is no error message I really can't figure out why the table not getting populated, I have even tried to change the GTT to "preserve" but won't help.
I have tried this both on 10g in Unix box and 11g in windows 7. Hope you could help Kind Regards Ligon. January 18, - pm UTC. Not your pc. You could insert a row or two of your own into the table to give yourself a message about what is happening. Hey Tom, Just wondering if it would be possible to have the java code return the directory entries as piped rows instead of populating a global temporary table?
If yes, how would you do it? January 19, - pm UTC. Hi, such a function has already been fully implemented in open source oracle-jutils project. Once the function called you get a pipelined table.
I did develop it for 11g JDK 1. Hope this helps. Best regards, Adrien. March 02, - am UTC. String '; Error 4 : PLS Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue. March 26, - am UTC. I literally copied your code above and pasted into sqlplus.
I executed all the above objects successly but it fails to writes names into test. April 16, - am UTC. It is the latter, it is NOT a command, it is a "script", input, to a shell. You'd need to put that into a script and run the shell script.
Or, just run the ls command - as you get the output, and you write that to test. Tom, I'm trying to incorporate what you have written above into a specific schema so its not created under the user I'm logged in as. July 23, - am UTC. Excellant material John, December 20, - am UTC.
Hi Tom, how to read the file by using path? December 20, - am UTC. Use something that works from the command line and they you'd have to make sure the account the oracle software is running as the account the oracle SERVICE on windows is running as can 'see' that UNC named object as well. John, January 02, - pm UTC. January 03, - am UTC. Hi Tom, I still encounter of this error.
I don't understand of this error. What is this problem and how to solve? Tom, I found the problem. Some older txt files, i can read the content inside but not the latest content in the txt file. I have been tried update my txt file content to this: ,traditional,english but cannot get it, it only can get the previous data inside the txt file even I've modified before.
How can solve this issues? John, January 04, - am UTC. Tom, Very happy and exciting. Finally i know what is the error and solved it. Now proceed to research on read files from another share server. How to create the directory for the share path? What if I'm tryng to use Oracle XE? Sadly, Oracle express edition even last 11gR2 XE on beta still don't support java.
May 23, - am UTC. I haven't tried it yet on XE, but Would work on windows too I had to run the database service and the listener service with a domain user when I am using UNC path's at other servers. It works perfect.
But how to use UNC path's at 11gR2 I Tried to run the database service and the local Lister on one node with a domain user. It also doesn't work if I run only the database service with a domain user. In both cases I can't start the database service on that node. Which Services I need to run with domain users? Or any other config change necessary?
What about the scan listeners? Thanks in advance. Regards Juergen. April 13, - am UTC. Sorry, I don't do "windows" at all - I know very little about administering it. I'll have to refer you to support or to otn. Hello Tom, I have a little problem with foreign characters in the file names.
I hope there some "settings" I can change. My Database is a May 18, - am UTC. I tried this. Thomas, May 22, - am UTC. I implemented the dirlist and get the same result - files come back just perfect as I see them on the OS filesystem - but when I get try to open them with java I hit the "file does not exists" wall.
I wonder if some character set conversion is done wrong somewhere. I did some re-search on google but found nothing yet. Any help is appreciated :- Thanks Thomas. What I would suggest is putting together the minimal test case that demonstrates this issue as small as humanly possible and filing a support ticket - make sure to like out all of the character sets like that of the database, the clients setting, etc.
Thomas, did you use java to create this file? Why does the db see a different list of files? Joe, May 28, - pm UTC. Tom, I used your original code and the variation to the Java code that Mette from Denmark provided back in Oct and I get an output but it's not what I'm expecting. But if I navigate to that same location I see a totally different list of directories. May 31, - am UTC. That is not clear to me here. Joe, May 31, - pm UTC. Tom, Thanks for your response. That was it. Had my dba correct that and it works now.
Thanks for the tip. A reader, August 02, - am UTC. Thank dear Tom, I have a question. I have too much files that extend the string length and returns an error ORA A Truncation or null fetch error occurred Do you have any idea what should I do?
August 02, - am UTC. Since I don't fetch any data - not sure how this could be happening in the java code. Can you create a java procedure that would take an Oracle Directory Object name and produce the list of files? If so, can this solve the problem of running on something other than the system you are running SQL from like the database server?
August 31, - pm UTC. Shebang incomplete? Sokrates, August 31, - am UTC. I corrected it above. September 13, - am UTC. Hi Tom, suppose if we want to login into a server and read its folders using this program, what modifications would it need?
I feel we need to have a connection to the server. Any java class exists to do so? Thank you! October 03, - am UTC.
You can use this to read a directory you have been granted access to on the database server alone. So, you log into a database, you run this procedure, and you see a directory you were granted access to. This is not for reading a remote directory.
Hi, I find your "fantastic" code. I have implemented it with an other parameter, it inserted in the record in the table. String, java. Ora nessun metodo getList nella classe DirListAnag ora Is it a problem about privileges???? Sokrates, February 14, - am UTC. February 14, - am UTC. What should I do? August 15, - pm UTC. July 16, - pm UTC. If not, it would be pretty scary to think that plsql could reach out to a client machine - any client machine - and read its file system.
Hi, I am able to implement above code and read directory files. Many thanks for this post. In addition to this, I would also like to read file creation date.
Can you please advice how can I achieve it? Thank you in advance. November 01, - pm UTC. Thank You in advance. The code of this solution is correct and useful. The issue lies with the fact there is code in Java. If you are a Java programmer that is well and good. But if not and something goes wrong then there code be issues and if you started relying on this code and something goes wrong it could be hard to resolve.
A better solution is to use external files and the preprocessor feature of external files. A simple shell script that gets the listing and adds commas and then presents as an external table. March 01, - am UTC. This is why you can see on AskTom we have a "Is this answer out of date? If it is, please let us know via a Review" facility.
Because, as you can see, this question was first asked 17 years ago! Hi, I used this java source for a long time, but I get error on 12c, compilation error: sorry for my english An exception has occurred in the compiler 1.
Include your program and the following diagnostic in your report. Thank you. Active 1 year, 4 months ago. Viewed 6k times. See asktom. Probably a duplicate of stackoverflow. It need to create a new table. Could be posible without creating new table? It is complicated to create a solution and I don't have time to do it for you but I hope that there are enough breadcrumbs here that you or someone else could achieve it.
The other solution is to use an external table preprocessor and there is a detailed explanation in the second link of my first comment. The nub of the problem is that interacting with the OS from inside the database is hard: not programmatically but architecturally. There are all sorts of security and administrative issues, which is why it isn't switched on by default. So while there is a bit of technical complexity the tougher issues are political getting privileges to do things. That you would like to avoid creating a new table suggests that you are working under such political constraints.
Show 4 more comments. Home » Articles » Misc » Here. This article shows how to list files in a directory on the database server using an external table.
This article is based on this great article by Adrian Billigton , but it's been adjusted to suit my needs. We create the Oracle directory objects associated with these physical directories, granting the relevant permissions to our test user.
We create the pre-processor script with the following commands.
0コメント