reading files in a directory
July 9, 2001 - 7am Central time zone
Reviewer: Marcos from Netherlands
Better option than I came up with
Thanks
Great explanation !
February 26, 2002 - 4pm Central time zone
Reviewer: Sven Bleckwedel from Santos, SP/Brazil
Hi,
Tom, tks for your valuable explanation about this feature, using Java and the bit tricky use of
"global temporary table DIR_LIST...on commit delete rows" ! Suppose that i had created these
procedures in one schema (and the init.ora parameter "utl_file_dir" was configured properly). How
can the other users use it ? I had granted "execute on get_dir_list" to another user and received
the following error:
SQL> grant execute on get_dir_list to scott;
Grant succeeded.
SQL> connect scott
Enter password: *****
Connected.
SQL> exec get_dir_list( 'C:\Temp\Extract' );
BEGIN get_dir_list( 'C:\Temp\Extract' ); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'GET_DIR_LIST' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> desc dir_list
ERROR:
ORA-04043: object dir_list does not exist
Could you explain my mistake ? I think that the access to this table is inherited by the
procedure, but...
Tks in adv,
Sven
Reviewing steps...
February 27, 2002 - 5pm Central time zone
Reviewer: Sven Bleckwedel from Santos, SP/Brazil
Hi,
Excuse me, but i had found the reason of my mistake...
SQL> connect system
Enter password:
Connected.
SQL> grant select on dir_list to scott;
Grant succeeded.
SQL> connect scott
Enter password:
Connected.
SQL> desc system.dir_list;
Name Null? Type
----------------------------------------- -------- -------------
FILENAME VARCHAR2(255)
SQL> exec system.get_dir_list( 'C:\Temp\Extract' );
PL/SQL procedure successfully completed.
SQL> desc system.dir_list;
Name Null? Type
----------------------------------------- -------- -------------
FILENAME VARCHAR2(255)
SQL> select * from system.dir_list;
FILENAME
-----------------------------------------------------------------
LOG_ERRO.CDX
LOG_ERRO.DBF
SQL>
I made this test in one test instance only and i know the security problems that can arise when
giving to another users the ability to use procedures and tables from another user...
Rgds,
Sven
Extremely helpful!
March 6, 2002 - 2pm Central time zone
Reviewer: Mike Jones from Columbia, MD USA
Helpful, easy and worked first time in 8.1.6.
Great initial exposure to Java in the database.
Great! Helped me solve one of my problems
May 7, 2002 - 9am Central time zone
Reviewer: Yogeeraj from Mauritius
Hi,
Helped me solve one of my problems where i had to query the existence of a file on the OS in one of
my applications.
Helped me also build a list of files from several directories.
(only missing part on which i have to investigate is how to include the relative path to each
filename.)
I can also understand that files must be residing on the same box as the database server.
The first java procedure in my database ;)
thanks a lot.
Best Regards
Yogeeraj
NullPointerException
May 9, 2002 - 11pm Central time zone
Reviewer: Charlie
Hi Tom,
I followed the exact procedure you listed here and I know "c:\temp" folder exists on the database
server. Why do I get this NullPointerException? Any clue?
Thanks,
SQL> exec get_dir_list('c:\temp');
BEGIN get_dir_list('c:\temp'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "TU1.GET_DIR_LIST", line 0
ORA-06512: at line 1
Followup May 10, 2002 - 6am Central time zone:
version and all?
version
May 10, 2002 - 10am Central time zone
Reviewer: Charlie
Followup:
version and all?
--------------------
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
OS: Windows2000/Pro SP2
Thanks,
Followup May 10, 2002 - 4pm Central time zone:
Sorry but the only way I can reproduce this is when the directory in fact DOES NOT exist:
a@ORA817DEV.US.ORACLE.COM> @connect /
a@ORA817DEV.US.ORACLE.COM> set termout off
ops$tkyte@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout on
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user a cascade;
User dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> grant create session, create table, create procedure to a
identified by a;
Grant succeeded.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.grant_permission( 'A',
'java.io.FilePermission','/tmp','read' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_java.grant_permission( 'A',
'java.io.FilePermission','/tmp_NOT','read' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect a/a
ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off
a@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST
a@ORA817DEV.US.ORACLE.COM> set termout on
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /
Table created.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23
24 }
25 /
Java created.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /
Procedure created.
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> exec get_dir_list( '/tmp' );
PL/SQL procedure successfully completed.
a@ORA817DEV.US.ORACLE.COM> select * from dir_list where rownum < 5;
FILENAME
----------------------------------------------------------------------------------------------------
-------------------------------
lost+found
.rpc_door
ps_data
.pcmcia
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM>
a@ORA817DEV.US.ORACLE.COM> exec get_dir_list( '/tmp_NOT' );
BEGIN get_dir_list( '/tmp_NOT' ); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "A.GET_DIR_LIST", line 0
ORA-06512: at line 1
a@ORA817DEV.US.ORACLE.COM>
It works fine
May 10, 2002 - 3pm Central time zone
Reviewer: Amitabha Bhaumik from Boston,MA USA
It works GREAT but when I created the java procedure
instead of saying
"java created"
it said
"Operation 160 succeeded."
any idea.
Followup May 10, 2002 - 4pm Central time zone:
You are using a pre-8i sqlplus version. You should upgrade the client software to take advantage
of the database new features correctly.
Solution from Heart
May 11, 2002 - 4am Central time zone
Reviewer: Parag from India
Too good man.
I think this is one of the "THE BEST" site for Oracle related Issues/Quesries/Problems.
One Suggestion Tom , Can you incorporate following changes in your site.
1. Like Other site , as and when new qustsion is posted on your site , can you give ALERT email to
whole asktom communities with the Question Link.
2. Can your site , send a ORA (Oracle) TIP OF THE DAY , to all registered asktom users. The
question will be given with multi choice answere selection.
3. Can you intoduced "RATING SYSTEM". Say those who are posting maximum number of questions on your
site which you are going to publish on your site , which really carry some weight.
4. Is it possibel to provide " ON LINE CHAT WITH Mr.TOM " on specified date and time regarding
oracle topics.
All this are just thought's .
Butanyway Thank a lot for offering such nice services to ORA COMMUNITIES.
Regards
Yea, Yea !! I got one too!
July 3, 2002 - 1pm Central time zone
Reviewer: John from MA
#5. How bout you stop over my place of business, fix my sloppy code (not as bad as it use to be,
thanks for that!)
teach my DBA the importance of access to the V$ views so we can better do our job,ooh! actually,
since your taking suggestions and have all this time on your hands, how bout rewritting the 30,000
some odd manuals that oracle has into an easier, beer on the beach kind of read.
Love the site, Tom and thanks for all the help.
(OK OK how bout just the books)
Documentation?
October 7, 2002 - 5pm Central time zone
Reviewer: Robert from Memphis, USA
Tom,
Can you tell us where to find documentation on the specific JAVA functions (objects?)...
...Specifically the 'FILE' (function, object, 'thing'?), from your example.
I am wanting to investigate a way to return the privilege and datestamp info from the UNIX box,
similar to what you would see with a 'ls -l' command.
Thanks,
Robert.
Followup October 7, 2002 - 6pm Central time zone:
http://java.sun.com/
under documentation. Takes about a year to read it all. Java isn't "small".
Need your suggestion
October 8, 2002 - 6am Central time zone
Reviewer: Daniel from India
Hi Tom,
I get comma-separated values (e.g. 3,4,5,6,) as input. I parse these values, with using some
oracle functions. After reading this Java source example, thought of using java source to parse
this data. Because I read that parsing in java program is efficient than parsing in pl/sql. So
which will be the efficient way? Using oracle function or using java source? Please, I need your
suggestion
Thanks in advance
daniel
Followup October 8, 2002 - 7am Central time zone:
I'll race your java code with my plsql code -- we'll see who wins.
Parsing a comma delimited string is TRIVIAL in plsql. Use the language you feel comfortable in.
MalFormedInputException
October 8, 2002 - 7am Central time zone
Reviewer: A reader
Hi Tom,
A Similar Program that i had written and tried to upload into the Oracle Database Using the Create
Or Replace Java Stmt. I received a MalFormedInputException.
My Database CharSet is UTF8.
Contacted support but did not get a convincing answer on this.
Regards,
Ganesh R
-------------------Code of the Java Programm---------------
Create Or Replace and compile Java Source Named FileHandling
As
import java.io.*;
class FileHandling extends Object implements FileFilter
{
public FileHandling(String dir, String newFile) throws IOException
{
openFiles(dir, newFile);
}
public void openFiles(String dir, String fileNew) throws IOException
{
File dirFile;
File sqlFile;
File newFile;
File dirListing[];
FileWriter fw;
FileReader fr;
dirFile=new File(dir);
newFile = new File(fileNew);
fw=new FileWriter(newFile.toString(),false);
int i=0;
if (dirFile.isDirectory())
{
System.out.println("Parsing Directory... " + dirFile + " \n");
dirListing = dirFile.listFiles(this);
for (i = 0;i< dirListing.length;i++)
{
if (dirListing[0].isDirectory())
{
openFiles(dirListing[i].toString(), fileNew);
}
else
{
fr=new FileReader(dirListing[i]);
System.out.print("Writing File " + dirListing[i] + " ");
if (!appendFiles(fw,fr))
{
System.out.println("Unable to Write File : " + dirListing[i] + " \t\t");
}
else
{
//System.out.println("File " + dirListing[i] + " Successfully Written");
}
}
}
System.err.println("\nRead " + i + " Files/Directory from Directory " + dirFile);
}
else
{
System.err.println("Uanble To Parse Directory... " + dirFile);
}
}
public boolean accept(File testFile)
{
if (testFile.isDirectory())
{
return true;
}
else if (testFile.isFile())
{
if (testFile.getName().endsWith(".sql"))
{
return true;
}
}
return false;
}
public boolean appendFiles(FileWriter fw, FileReader fr) throws IOException
{
if (fr.ready())
{
int i = 0;
int x=0;
do
{
i=fr.read();
x++;
if ((x == 16384))
{
x=0;
System.err.print(".");
}
if (i != -1)
fw.write(i);
}while(i != -1);
fw.write("\n".toCharArray());
fw.write("/*".toCharArray());
for(int j=0;j<118;j++)
{
fw.write('*');
}
fw.write("*/".toCharArray());
fw.write("\n".toCharArray());
fw.flush();
fr.close();
System.err.println("");
return true;
}
else
{
System.err.println("");
return false;
}
}
public static void ConcatNow(String newFile, String dirFile) throws IOException
{
new FileHandling(dirFile,newFile);
}
}
------------------------End Of Java Code-------------------
Followup October 8, 2002 - 7am Central time zone:
I didn't have any issues running your code in Oracle 9iR1 with UTF8. It ran verbaitim.
You didn't give me the error message, version, platform, tar# from support, etc etc etc... so thats
all I can say.
Sorry About That ...
October 8, 2002 - 8am Central time zone
Reviewer: A reader
Here is the error. It is an error when i try to run it.
Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> ED
Wrote file afiedt.buf
1 cREATE OR REPLACE Procedure ConcatFile(newFile Varchar2, dirFile Varchar2)
2 As
3* Language Java Name 'FileHandling.ConcatNow(java.lang.String,java.lang.String)';
Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> /
Procedure created.
Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1> eXEC CONCATfILE('a.TXT','D:\iFLEX');
BEGIN CONCATfILE('a.TXT','D:\iFLEX'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: sun.io.MalformedInputException
ORA-06512: at "GTFSDXB\GANESH.CONCATFILE", line 0
ORA-06512: at line 1
Ganesh@Db9i.Gtfsgulf.Com on 9.0.1.3.1>
Regards,
Ganesh R
Followup October 8, 2002 - 8am Central time zone:
See -- this is why I always always always want a straight cut and paste. First you say:
<quote>
A Similar Program that i had written and tried to upload into the Oracle
Database Using the Create Or Replace Java Stmt. I received a
MalFormedInputException.
</quote>
which I reasonably took to mean "Hey, I cannot get this to compile -- I get this error
"malformedinputexception"...
but lo and behold, it is not a compile time error at ALL!!! its a runtime error...
Ok, run your code like this and debug it (I get a NullPointerException myself). There is nothing
"wrong" at the Oracle end here -- there is something wrong in your code somewhere.
Use dbms_java so you can see your system.out.printlns and add copious amounts of "debug" statements
to see what is causing it to fail, eg:
ops$oracle9i@ORA9IUTF.WORLD> CREATE OR REPLACE Procedure ConcatFile(newFile Varchar2, dirFile
Varchar2)
2 As
3 Language Java Name 'FileHandling.ConcatNow(java.lang.String,java.lang.String)';
4 /
Procedure created.
ops$oracle9i@ORA9IUTF.WORLD> exec dbms_java.set_output( 1000000 )
PL/SQL procedure successfully completed.
ops$oracle9i@ORA9IUTF.WORLD> set serveroutput on size 1000000
ops$oracle9i@ORA9IUTF.WORLD> exec concatfile( 'a.txt', '/tmp/' );
Parsing Directory... /tmp
Parsing Directory... /tmp/lost+found
java.lang.NullPointerException
at FileHandling.openFiles(FILEHANDLING:31)
at FileHandling.openFiles(FILEHANDLING:35)
at FileHandling.<init>(FILEHANDLING:7)
at FileHandling.ConcatNow(FILEHANDLING:117)
BEGIN concatfile( 'a.txt', '/tmp/' ); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "OPS$ORACLE9I.CONCATFILE", line 0
ORA-06512: at line 1
So, I can see we are dying (in my case) after looping 2 times
After correcting the code to deal with the NULL pointer exception via:
dirListing = dirFile.listFiles(this);
for (i = 0;dirListing != null && i< dirListing.length;i++)
{
if (dirListing[0].isDirectory())
I get:
ops$oracle9i@ORA9IUTF.WORLD> exec concatfile( 'a.txt', '/tmp/' );
Parsing Directory... /tmp
Parsing Directory... /tmp/lost+found
Read 0 Files/Directory from Directory /tmp/lost+found
Parsing Directory... /tmp/.rpc_door
Read 0 Files/Directory from Directory /tmp/.rpc_door
Parsing Directory... /tmp/.pcmcia
Read 0 Files/Directory from Directory /tmp/.pcmcia
Parsing Directory... /tmp/.X11-unix
Read 0 Files/Directory from Directory /tmp/.X11-unix
Parsing Directory... /tmp/.X11-pipe
Read 0 Files/Directory from Directory /tmp/.X11-pipe
Uanble To Parse Directory... /tmp/a.sql
Uanble To Parse Directory... /tmp/flat10674.sql
Uanble To Parse Directory... /tmp/x.sql
Uanble To Parse Directory... /tmp/t.sql
Parsing Directory... /tmp/test
Writing File /tmp/test/runall.sql
Read 1 Files/Directory from Directory /tmp/test
Parsing Directory... /tmp/kk
Read 0 Files/Directory from Directory /tmp/kk
Uanble To Parse Directory... /tmp/gork.sql
Uanble To Parse Directory... /tmp/test.sql
Parsing Directory... /tmp/empty
Read 0 Files/Directory from Directory /tmp/empty
Parsing Directory... /tmp/clntst8.7259
Read 0 Files/Directory from Directory /tmp/clntst8.7259
Parsing Directory... /tmp/clntst8.15037
Read 0 Files/Directory from Directory /tmp/clntst8.15037
Parsing Directory... /tmp/clntst8.21730
Read 0 Files/Directory from Directory /tmp/clntst8.21730
Uanble To Parse Directory... /tmp/flat2465.sql
Uanble To Parse Directory... /tmp/flat20540.sql
Uanble To Parse Directory... /tmp/installhelp.sql
Uanble To Parse Directory... /tmp/alter_rbs8i.sql
Parsing Directory... /tmp/clntst8.8455
Read 0 Files/Directory from Directory /tmp/clntst8.8455
Parsing Directory... /tmp/clntst9.22241
Parsing Directory... /tmp/clntst9.22241/objs
Read 0 Files/Directory from Directory /tmp/clntst9.22241/objs
Read 1 Files/Directory from Directory /tmp/clntst9.22241
Parsing Directory... /tmp/.removable
Read 0 Files/Directory from Directory /tmp/.removable
Parsing Directory... /tmp/clntst8.15137
Read 0 Files/Directory from Directory /tmp/clntst8.15137
Uanble To Parse Directory... /tmp/obj.sql
Uanble To Parse Directory... /tmp/xw.sql
Uanble To Parse Directory... /tmp/10565.sql
Uanble To Parse Directory... /tmp/10593.sql
Uanble To Parse Directory... /tmp/10599.sql
Parsing Directory... /tmp/demo
Read 0 Files/Directory from Directory /tmp/demo
Parsing Directory... /tmp/tkyte
Read 0 Files/Directory from Directory /tmp/tkyte
Parsing Directory... /tmp/clntst9.23657
Parsing Directory... /tmp/clntst9.23657/objs
Read 0 Files/Directory from Directory /tmp/clntst9.23657/objs
Read 1 Files/Directory from Directory /tmp/clntst9.23657
Read 33 Files/Directory from Directory /tmp
PL/SQL procedure successfully completed.
so it ran thru OK for me.
arrange by files created order
October 8, 2002 - 11am Central time zone
Reviewer: rajesh
hi tom
while displaying is that is possible to obtain
the list in such a manner as
the file created last comes first and so on
thanks in adv
raj
Followup October 8, 2002 - 12pm Central time zone:
Don't know, I don't program java extensively (just as little as I need). That is more of a "java"
question (eg: in java, how can I get the timestamp or can you just feed me the files in sorted
order)....
Maybe devtrends.oracle.com can help
Parsing in PLSQL
October 8, 2002 - 5pm Central time zone
Reviewer: Randy
You threw in that parsing a csv in PLSQL is trivial. I'm not feeling particularly clever today so
I'd like to see your method. Even though what I have works (barely), I'm sure yours is better than
what I have.
Thanks.
Followup October 8, 2002 - 5pm Central time zone:
search this site for
str2table
Thanks
October 10, 2002 - 1am Central time zone
Reviewer: Gururaj Kulkarni from Bangalore,Karnataka, India
Tom,
You said: You are using a pre-8i sqlplus version. You
should upgrade the client software to take advantage of the
database new features correctly.
Could you tell us what are those features ?
Thanks
-Gururaj
Followup October 10, 2002 - 6am Central time zone:
well, the timing would be one.
Ability to create "new types of things" in sqlplus without sqlplus getting in the way (eg: consider
$ sqlplus scott/tiger@ora817dev
SQL*Plus: Release 8.0.6.0.0 - Production on Thu Oct 10 06:50:01 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
scott@ORA817DEV.US.ORACLE.COM> set echo on
scott@ORA817DEV.US.ORACLE.COM> @rand
scott@ORA817DEV.US.ORACLE.COM> create or replace and compile java source named "RandomNumGenerator"
2 as
3 public class RandomNumGenerator
4 {
5 public static double random( java.lang.Short max )
6 {
7 double rand = Math.random();
create or replace and compile java source named "RandomNumGenerator"
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "<EOF>" at line 5, column 40.
Was expecting one of:
"instanceof" ...
hundreds of errors chopped
SQLPlus doesn't understand the new commands and chokes on them.
and so on.
Old SQL*Plus version is better for basic stuff!
October 10, 2002 - 10am Central time zone
Reviewer: Robert from Memphis, USA
Tom, and all,
One thing that the 7.3.4 version of GUI sql*plus has over 8i... is that the old version has a
'Cancel' button so you can stop your query without killing your sql*plus session and sql*plus
screen... thus losing all your buffered output.
Why even use GUI sql*plus? ... because its easier to copy/paste (windoze notwithstanding)... and it
has the nifty variable selection feature that lets you copy individual columns for a set of rows
instead of the entire vertical chunk.
Thanks,
Robert
Followup October 11, 2002 - 7pm Central time zone:
I find the DOS window trivial to cut and paste from -- I just selected text and hit enter (copy).
I just hit "alt space" ep and it pasted.
The DOS window copies with "squares" as well ;)
CTL-C always works in the dos window to cancel a query.
May we following the rabbit trail a little further?
October 22, 2002 - 4pm Central time zone
Reviewer: Robert from Memphis, USA
Tom,
I respect your opinion so I am considering looking into using the DOS sql*plus program.
I have been able to size the buffer width appropriatly (1000 chars wide so I can issue 'select *'
on wide tables), so far so good...
...but it seems a pain to copy and paste.
With the GUI sql*plus you can just do a cntl-c, cntl-v... very simple... but with DOS, you have to
go thru the whole rigaramarole of 'marking', copying, then pasting.....
..... Could you please give an example for some of us who may be a little slower to catch on... an
actual example of how you 'mark', copy and paste in a DOS sql*plus session...including all
keystrokes, mouse-clicks, etc.
Like I said... GUI sql*plus is a snap... swipe with the mouse, cntl-c, cntl-v.....DONE! .......
Your reply would be greatly appreciated!
Thanks,
Robert.
Followup October 22, 2002 - 7pm Central time zone:
Well, I don't actually have windows installed anywhere anymore so this is from memory.
You can adjust the properties to "smart select" or something like that -- at least on windows NT.
It was the first thing I always did to dos windows (it could be made "sticky" so all dos windows
would have it)
Then when you clicked and dragged with a mouse - it would select text.
So in a windows NT dos window it was:
o select text
o hit enter -->> selected text in copy buffer
o alt-space
o e
o p
and it would paste it.
RE: Old SQL*Plus version is better for basic stuff!
October 23, 2002 - 4am Central time zone
Reviewer: Ed from Cheshire, UK
Robert,
You can still cancel queries in newer versions of Windows SQL*Plus by using the menu (File ->
Cancel).
Another great thing about Windows SQL*Plus is the facility to copy and paste with a single mouse
operation: Select what you want using the left mouse button and dragging - then, whilst the left
button is still depressed, click the right button and what you had selected is copied straight to
the SQL*Plus command line (this is internal to SQL*Plus - it doesn't copy to the Windows clipboard
buffer).
I use this feature dozens of times a day when I want to quickly re-sun a SQL statement that I ran
half an hour ago.
Followup October 23, 2002 - 7am Central time zone:
I just hit the f7 button or arrow up in character mode to get a history of commands myself (well, I
used to anyway when I ran windows)
RE: Old SQL*Plus version is better for basic stuff!
October 23, 2002 - 9am Central time zone
Reviewer: Ed from Cheshire, U.K.
I don't think that's ever worked in Windows SQL*Plus - that's one of the criticisms one of my
non-Oracle colleagues has of it. If it works in the DOS version, I'd be tempted to change.
If not Windows then what ?!
October 23, 2002 - 3pm Central time zone
Reviewer: Robert from Memphis, USA
Tom,
You said you don't have Windows installed anywhere.
Can you please tell us what OS you do use.
Also, If you don't use Windows... what do you do for spreadsheets, word processors, etc.
Thanks,
Robert.
copy & paste from dos window
October 24, 2002 - 1am Central time zone
Reviewer: John from San Francisco, CA
Robert,
I usually do the following on XP/2000 (been a while since I've used NT, so the exact wording may
have changed) for dos windows.
Start --> Run --> cmd
You should now have a dos window. Right click on the title bar, and select Properties. You want
to enable QuickEdit mode (I also adjust the layout properties such as window size--so it's usable).
Click OK, and save properties for future windows with same title.
Now you should be able to select text with the left mouse button, click the right mouse button to
copy the selected text, another click of the right mb will paste the text into this dos window or
any other (or ctrl-v into other apps).
AWESOME!!
November 6, 2002 - 10pm Central time zone
Reviewer: Russell Hertzberg from Roanoke, VA
This was a great code snippet! I've been looking for code like this for a while...nice, simple and
to the point!! Now I can take this and expand it to meet my needs. Thank You so much!
December 19, 2002 - 2pm Central time zone
Reviewer: A reader
Excellent example!!
can we read directory from machine other then oracle server?
We have requirment to read App server directories from PL/SQL.
Thanks
Followup December 19, 2002 - 3pm Central time zone:
the directory needs to be mounted on the server in order for the server to read it.
you need to make it accessible to Oracle if you want a stored procedure to read, yes.
Further Java reading
December 20, 2002 - 9am Central time zone
Reviewer: G Johnson from UK
<advert>
For people asking about further Java reading, I learnt Java from Sun Press' Just Java 2 by Peter
Van der Linden. As a DBA I found this book extremely useful for getting enough Java under my belt
without feeling like I was about to have to change careers to learn a little something useful!
Paperback - 1113 pages (1 December, 2001)
Prentice Hall PTR; ISBN: 0130320722
</advert>
Great approach.
February 7, 2003 - 1am Central time zone
Reviewer: Chandra S.Reddy from India
Tom,
You are really great.
This example is highly useful and highly reliable.
Thanks
reading files in a directory -- how to get a list of available files.
April 23, 2003 - 3am Central time zone
Reviewer: Artur from LITHUANIA
Very Very helpful even for those who do not know java and not very familiar in ORACLE :]
(works great on 9i (9.2.0.2.0))
THANX
Superb!!!
May 13, 2003 - 6am Central time zone
Reviewer: man2002ua from Ukraine
Tom, you are BEST!
need some help
July 8, 2003 - 7am Central time zone
Reviewer: binu from (TN)India
i am tring to unzip the zipped file from c:\test_java\ to c:\test
this program executes prefectly in java but when i execute thru PL/SQL
i face some problem (i.e.) it shows "PL/SQL procedure successfully completed."
but no files are unzipped into c:\test.
can u explain where did i go wrong??
create or replace and compile java source
named "unzip_xml"
as
import java.io.*;
import java.util.*;
import java.util.zip.*;
import java.text.*;
import java.lang.*;
class unzip_xml
{
public static void unzip(String zip_name,String Extract_to)
throws Exception
{
try
{
String str;
String xls_file;
int n=0;
ZipEntry ze;
File f_xml=new File(Extract_to);
String xmlfls[]=f_xml.list();
for(int k=0;k<xmlfls.length;k++)
{
File f=new File(Extract_to+xmlfls[k]);
if (!(f.isDirectory()))
f.delete();
}
FileInputStream fis=new FileInputStream(zip_name);
ZipInputStream zis=new ZipInputStream(fis);
while (true)
{
ze=zis.getNextEntry();
if (ze==null)
break;
str=ze.getName();
int x = fis.available();
byte [] rgb = new byte [x];
FileOutputStream fout = new
FileOutputStream(Extract_to+get_zip_xml_file(str));
while((n=zis.read(rgb))>-1)
{
fout.write(rgb,0,n);
}
fout.close();
zis.closeEntry();
}
zis.close();
fis.close();
}
catch (Exception e)
{}
}//end of unzip_xml()
public static String get_zip_xml_file(String str)
{
int n=str.indexOf('.');
String file_name="";
for (int j=(n-1);j>=0;j--)
{
if (str.charAt(j)!='/')
file_name=str.charAt(j)+file_name;
else if (str.charAt(j)=='/')
{
file_name=str.substring(j+1,str.length());
break;
}
}
return file_name;
}//end of get_zip_xml_file()
}
/
create or replace procedure proc_unzip_xml(p_zip_name varchar2,p_extract_to varchar2)
as language java name 'unzip_xml.unzip(java.lang.String,java.lang.String)';
/
exec proc_unzip_xml('c:\\\\test_java\\\\xml-55-0001-08072003 9-36-36.zip','c:\\\\test\\\\');
regards
Binu
Followup July 8, 2003 - 7am Central time zone:
catch (Exception e)
{}
thats a beauty eh? "on error, any error, just kindly ignore it -- pretend it doesn't exist"
You might consider deleting those lines of code -- letting the error, which might be due to \\\\ or
lack of privs -- to propagate up.
PLSQL is not Java -- \ is not a "special character", \\ is not \, \\ is \\ in plsql/sql.
(and remember, we catch exceptions IF and ONLY IF we are
a) expecting them
b) can do something about them
else we let them propagate up and out of our code. better to fail with a message then APPEAR to
succeed)
need more help
July 8, 2003 - 7am Central time zone
Reviewer: binu from (TN)India
i tried your first sample program
i get an error as
SP2-0734: unknown command beginning "sql { INSE..." - rest of line ignored.
and compile java source named "DirList"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered "( :" at line 16, column 8.
Was expecting one of:
";" ...
"++" ...
"--" ...
"." ...
"[" ...
"(" <INTEGER_LITERAL> ...
"(" <FLOATING_POINT_LITERAL> ...
"(" <CHARACTER_LITERAL> ...
"(" <STRING_LITERAL> ...
"(" "true" ...
"(" "false" ...
"(" "null" ...
"(" "this" ...
"(" "super" ...
"(" "(" ...
"(" "new" ...
"(" "void" ...
"(" "boolean" ...
"(" "char" ...
"(" "byte" ...
"(" "short" ...
"(" "int" ...
"(" "long" ...
"(" "float" ...
"(" "double" ...
"(" <IDENTIFIER> ...
"(" "+" ...
"(" "-" ...
"(" "++" ...
"(" "--" ...
"(" "~" ...
"(" "!" ...
"(" "#sql" ...
"(" ")" ...
":" ...
<IDENTIFIER> ...
"=" ...
"*=" ...
"/=" ...
"%=" ...
"+=" ...
"-=" ...
"<<=" ...
">>=" ...
">>>=" ...
"&=" ...
"^=" ...
"|=" ...
Thanking u
Followup July 8, 2003 - 8am Central time zone:
guess you need to look at your cut and paste and figure out where in your cut and paste you messed
up? at least show US your cut and paste -- just like I show you (you don't need to put the entire
error message -- but the code is important, you cut and pasted the code wrong)
follow up
July 8, 2003 - 7am Central time zone
Reviewer: binu from (TN)India
my oracle version is
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Linux: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
Array vs temp table.
July 8, 2003 - 9am Central time zone
Reviewer: Darren from London, UK
Tom,
you inserted the values into a temporary table (no bulk binds either ;)) .. is there any
(dis)advantage of returning this result set in an array rather than inserts into sql tables?
Followup July 8, 2003 - 11am Central time zone:
figured there would not be hundreds or thousands of directory entries.
I liked the temp table here cause I wanted to write as little java as humanly possible, as fast as
I could -- hence insert was easier then messing about with IN OUT collection types.
Matter of preference.
Interacting with the OS -- what to learn?
July 8, 2003 - 11am Central time zone
Reviewer: Brian from Phoenix
From your book, I take it that Java is the first choice in interacting with the OS. However, the
Oracle documentation suggests more than one route for programming in Java.
One requires the client to install software; one does not.
I am not sure what to learn first for using Java in Oracle. I'm okay with SQL now; I'm starting to
get a grasp of PL/SQL (I still need to get a better handle on packages -- reminds me a lot of C++).
I would like to learn Java in August. But I'm not sure where to start.
I am using JServer 9.2.
The only two reasons why I can see using Java at all is for interacting with the OS and maybe GUI
stuff.
Thanks
Followup July 8, 2003 - 1pm Central time zone:
do you mean java inside the database?
I don't follow you -- you talk about Oracle interacting with the OS which seems to imply java
stored procedures....
then you talk about client software? I don't see the connection.
Clarification
July 8, 2003 - 1pm Central time zone
Reviewer: Brian from Phoenix
Yep, I'm having a problem following me to.
Here's the documentation I am referring to:
http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96656/invokeap.htm#1007092
It's says to learn to write stored procedures first then to worry about SQLJ and JDBC.
But when I go out to www.bookpool.com and start looking at Java books. The above starts to blend
together and the confusion starts.
I read your chapter on java stored procedures, but I don't understand the java. So do I go out and
pick up a book on Java 2 and download the stuff from Sun? Or is there a book that discusses Java 2
with Oracle using store procedures?
I'm just confused as how to learn this stuff.
Do I go out and pick up a book on Java 2? Read through it and learn it, and then read the Oracle
documentation. Or do I go out and find a Oracle specific book on Java?
I would like to be able to understand your java stored procedures for interacting with the OS and
maybe write some GUI stuff later on.
Thanks.
Followup July 8, 2003 - 2pm Central time zone:
Java is just a programming language, like C or C++.
You can write stored procedures in java.
You can write client side applications (guis or command line programs) in java.
You can write code that runs in an application server in java.
Java is just a language.
You will not be writing j2ee stuff inside the database, you would be doing little stored procedures
(only when PLSQL cannot do it, as the most efficient language in the database for manipulating data
will be that -- in fact, it would be the most efficient inside OR out actually).
If you want to do little snippets of java, java stored procedures are great. All you need is
sqlplus and an editor.
If you want to write full blown java apps -- then you need the client side tools.
clarification
July 10, 2003 - 7am Central time zone
Reviewer: reader from (Delhi)India
thank u tom as usual u r most helpful for me. but one question about this
this can read the files only in the oracle server but if at all i want
to read the files from my client machine how to go about by??
Sorry if i am wrong
rajeev
Followup July 10, 2003 - 10am Central time zone:
(hey, your keyboard is suffering some serious failures. It is dropping exceeding important
characters, namely "vowels". without vowels -- I feel like I'm reading a cell phone.)
In order to read files on the client, one writes a client side application or one uses the
operating system to make the client side files visible to the server.
Otherwise, we'd have what some people might term "a virus" as it could be used to steal any file we
wanted..... Oracle cannot just reach out and grab files from a client, the client has to give them
to us.
Help With Next Step
September 24, 2003 - 9pm Central time zone
Reviewer: Rob H. from Boston, MA
What technique would you then use to loop through the file names returned from dir_list... before
passing the filename to UTL_FILE.FOPEN for processing?
Followup September 25, 2003 - 5am Central time zone:
for x in ( select * from dir_list )
loop
... process x.filename ....
Great information
December 1, 2003 - 3pm Central time zone
Reviewer: Mike from St. Louis
Tom:
The original poster was after a way to feed an O/S directory listing into his/her PL/SQL code. Is
an external language call still required given Oracle 9.2 and Oracle directory objects?
Maybe its wishful thinking but since we can CREATE DIRECTORY with full path to the disk location,
maybe we can interrogate that directory object (from PL/SQL) to have it yield a file listing (or a
listing limited by a file pattern) without having to code external routines. Is that possible?
Thanks,
-Mike
Followup December 2, 2003 - 8am Central time zone:
still needed to read the contents of a directory, yes.
Date and Time?
December 3, 2003 - 2pm Central time zone
Reviewer: Mike from St. Louis
Thanks again Tom. Follow up question from a non-Java guy... the Java code and global temp table
solution above work great. Does anyone know how to extend the code to also extract the file
modification date and size for each file in the directory? -Mike
Does this routine work for Unix
December 10, 2003 - 1pm Central time zone
Reviewer: Suhail from NY, USA
Tom,
Does this example of creating global table and then a java stored procedure and pl/sql wrapper
works for UNIX environment too?
Thanks
Suhail
Followup December 10, 2003 - 4pm Central time zone:
well, the example was written on unix (linux) the only OS i have installed.
so, yes.
Oracle is Oracle is Oracle is Oracle.
You want the ultimate in portability? do it in the database.
Oracle is Oracle and Miracle too
December 11, 2003 - 8am Central time zone
Reviewer: Suhail from Albany, USA
Thanks , Oracle is Oracle and Oracle is Miracle too. I love Oracle and its features. I tested this
routine on NT and it works OK. On Unix platform I asked my DBA to grant my schema with DBMS_JAVA
using
EXEC DBMS_JAVA.GRANT_PERMISSION('MYSCHEMA',
'java.io.FilePermission','/export/stage/data','read,write');
and he said he granted but when I dis exec get_dir_list( '/export/stage/data' ); I got all sorts of
Java errors.
I did not ask for GRANT JAVAUSERPRIV to myschema.
Any idea why it snot working on UNIX box.
Thanks Tom
Followup December 11, 2003 - 8am Central time zone:
i cannot see your terminal from way over here, so no -- i've no idea since I've no clue what the
error messages might be trying to tell us!
Sorry I did not paste the error
December 11, 2003 - 9am Central time zone
Reviewer: Suhail from Albany, NY
Here are my error listings,
SQL> exec get_dir_list( '/export/home/staging');
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )
at
java.security.AccessControlContext.checkPermission(AccessControlContext.java:207
)
at java.security.AccessController.checkPermission(AccessController.java:403)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:549)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java
:267)
at java.lang.SecurityManager.checkRead(SecurityManager.java:864)
at
oracle.aurora.rdbms.SecurityManagerImpl.checkRead(SecurityManagerImpl.java:156)
at java.io.File.list(File.java:771)
at DirList.getList(DirList:9)
BEGIN get_dir_list( '/export/home/staging'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )
ORA-06512: at "DBT.GET_DIR_LIST", line 0
ORA-06512: at line 1
I can read files using UTL_FILE option so I have access to this directory.
Thanks
Followup December 11, 2003 - 10am Central time zone:
the error message:
SQL> exec get_dir_list( '/export/home/staging');
java.security.AccessControlException: the Permission (java.io.FilePermission
/export/home/staging read) has not been granted to DBT. The PL/SQL to
grant this is dbms_java.grant_permission( 'DBT',
'SYS:java.io.FilePermission', '/export/home/staging', 'read' )
it pretty clear is it not??????
it gives you the exact command that need to be executed!
You may have asked the dba to execute:
EXEC DBMS_JAVA.GRANT_PERMISSION('MYSCHEMA',
'java.io.FilePermission','/export/stage/data','read,write');
but either:
a) they did not
b) they did it to 'MYSCHEMA' and 'MYSCHEMA' != 'DBT'
c) they did it on a different server.
UTL_FILE uses utl_file_dir
Java uses DBMS_JAVA (much much more granular) for it's permissions. because you can utl_file it
DOES NOT in any way imply java can read it.
You are right
December 12, 2003 - 10am Central time zone
Reviewer: Suhail from NY, USA
Actually our DBA has granted me EXECUTE on DBMS_JAVA and not the actual permission. Sorry for the
confusion.
Thanks a lot.
for Mike: date and time
December 13, 2003 - 2am Central time zone
Reviewer: Barbara Boehmer from Riverside County, CA USA
Here is some java by Cameron O'Rourke to list directories, including date and time:
http://marvel.oracle.com/pls/otn/f?p=17000:8:::::F17000_P8_DISPLAYID:14855931322
pl/sql and reading remote files
December 26, 2003 - 10am Central time zone
Reviewer: Sangeeta from Germany
Database Version: Oracle 8.1.7.4 (on HP Unix)
Hi Tom,
I want to be able to read a windows directory through a PL/SQL function (Database is on Unix..so
it is not a local directory on oracle server)..
and then do a string search on the directory..like find all the files that begins with the word
"SPECIAL_PROJECT". I know that the list of files expected won't be more than three to four at a
time..
so i want these file names to be returned in a comma delimited format..
like 'SPECIAL_PROJECT1993.pdf,SPECIAL_PROJECT1994.pdf,SPECIAL_PROJECT1995.pdf'
Have you done anything like this..
Thanks...
Followup December 26, 2003 - 10am Central time zone:
there will be 2 problems with this
a) how will the server get access to this remote filesystem. Solve that problem first (and it has
nothing whatsoever to do with the database -- when you can "ls" this windows directory, you can
read step b below to continue on). SAMBA can be used to mount the windows directory.
Unless you can read the directory at the os level, Oracle cannot either.
b) use the approach above -- all of the code to do 100% of what you ask is there.
how to get a list of available files.", version 8.1.5
December 27, 2003 - 2pm Central time zone
Reviewer: Leonard Anukam from Finland
Hei, Tom very great again. But do we have to create the DB directory pointing to the OS directory
before using this script.
Thanks
Followup December 27, 2003 - 2pm Central time zone:
no, this does not need a "direct object" in the database.
it does however require the appropriate dbms_java grant be issued.
How does one display the value of a directory created using "create directory ... as ..."?
February 13, 2004 - 3pm Central time zone
Reviewer: ht from California
Tom,
select * from dba_objects where object_type='DIRECTORY' will display the directories a user has
created. How does one display the actual directory value (TMPDIR=/tmp)?
Thanks,
ht
Followup February 13, 2004 - 3pm Central time zone:
DBA_DIRECTORIES
February 13, 2004 - 4pm Central time zone
Reviewer: A reader
How do I read files from a certain directory with PL/SQL, without
April 2, 2004 - 10am Central time zone
Reviewer: Adrian from Belgium
Hi,
First time that I've used your site, found this review via a google search. Solved my problem both
elegently and imaginatively,
wonderfull
many thanks
How to display files and sub directories.
June 24, 2004 - 4pm Central time zone
Reviewer: chandra from Los Angeles, CA USA
Tom,
I have successfully executed the procedure you have shown to display the list of files in a
directory.
Is there a way to display the sub_directories as well along with the files.
thanks
Followup June 24, 2004 - 8pm Central time zone:
it does show the sub directories in the directory.
if you mean "recursively show", like ls -R, then you need to query your java resource for a bit of
help...
UNIX Directory Read
July 20, 2004 - 5pm Central time zone
Reviewer: Deanna Pegg from SF
Hi Tom
Thanks again for the great information!
Will the solution on this page work if the files are located on a server that is different from the
Oracle database? If so how would the procedure change below?
ops$tkyte@8i> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /
Table created.
ops$tkyte@8i> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23
24 }
25 /
Java created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace
2 procedure get_dir_list( p_directory in varchar2 )
3 as language java
4 name 'DirList.getList( java.lang.String )';
5 /
Procedure created.
ops$tkyte@8i>
ops$tkyte@8i> exec get_dir_list( '/tmp' );
PL/SQL procedure successfully completed.
ops$tkyte@8i> select * from dir_list where rownum < 5;
FILENAME
------------------------------------------------------
data.dat
.rpc_door
.pcmcia
ps_data
And thats it...
Followup July 20, 2004 - 8pm Central time zone:
the file system needs to be accessible to the oracle database software that is running, nfs mounts
on unix would suffice
if you cannot "ls" it from the command line, we cannot magically do it either...
Just to let TOM how helpful he really is....
February 14, 2005 - 12am Central time zone
Reviewer: Richard Seneres
You always have the suited answer for most people!!!!
Always got the answer I really needed...
Thanks very much!!
Excellent
March 9, 2005 - 8pm Central time zone
Reviewer: stanley from Singapore
I wonder how you can response to so many queries promptly. You are not only expert in Oracle, you
are also expert in time management. Salute you.
Binu, I had similar error.....Tom, got around that, but have new one
March 14, 2005 - 11am Central time zone
Reviewer: Yuan from Newark, NJ USA
Binu, I had a similar error with Tom's original sample when I ran it from TOAD 7.5, but when I ran
it in SQL Plus 8.1.7, worked just fine.
Tom, you mentioned that JAVAUSERPRIV must be granted. I got my DBA to grant me that permission,
but get this error:
ESL-CHATDV>declare
2 PROCEDURE Get_Dir_Lst(pivDir VARCHAR2) AS LANGUAGE java
3 NAME 'DirLst.getLst( java.lang.String )';
4 begin
5 Get_Dir_Lst('D:\TEMP');
6 end;
7 /
PROCEDURE Get_Dir_Lst(pivDir VARCHAR2) AS LANGUAGE java
*
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00999: implementation restriction (may be temporary) Java methods not
permitted here
ORA-06550: line 2, column 5:
PL/SQL: Item ignored
Are there other privileges that I'm missing? Running on Oracle 9.2 via Toad and SQL Plus of the
versions mentioned above.
Followup March 14, 2005 - 1pm Central time zone:
you have to create or replace that procedure get_dir_lst
and -- after 8.1.5, you don't need/want to use javauserpriv -- dbms_java can be used to grant the
precise access you need in a very granular fashion.
THANKS!
March 14, 2005 - 1pm Central time zone
Reviewer: Yuan from Newark, NJ USA
That did the trick. Saved my butt again!
Greate work Tom!
April 15, 2005 - 10am Central time zone
Reviewer: hash
But the problem is that I have oracle 8.0.5.0.0. It would be really nice of you if you could give
exactly the same solution for 805
thanks
Followup April 15, 2005 - 10am Central time zone:
external procedures (if you have expert one on one Oracle, it is in there -- lots of information)
in C would be the only way.
Your book is not available here
April 15, 2005 - 1pm Central time zone
Reviewer: hash
Sorry but your book is not available in Pakistan. It would cost me my one month salary. & I don't
have expertise in C. do you have any handy example????
thanks
Followup April 15, 2005 - 2pm Central time zone:
Sure it is, I get the royalty statements. People in Pakistan have gotten it. (there is a reprint
available in various countries for real cheap, I know because the statements I get from the
publisher show that)...
Anyway, external procedures are documented in the plsql developers guide in that release
http://download-east.oracle.com/docs/cd/A64702_01/doc/server.805/a58236/10_procs.htm#433731
and many example on this site, search around for them.
April 26, 2005 - 9pm Central time zone
Reviewer: Murtala from Nigeria
please i am student,i checked every where to get your legitimate way to ask question but i couldnt
find any.
please kindly help me send me a written program in java that diagonalises a matrix of atleast
3x3(rows and columns).thanks
Followup April 27, 2005 - 7am Central time zone:
it's been about 20 years since I've actually had to do that.
And back then, we did it on paper typically, to learn how it was done.
April 26, 2005 - 9pm Central time zone
Reviewer: Murtala from Nigeria
please i am student,i checked every where to get your legitimate way to ask question but i couldnt
find any.
please kindly help me send me a written program in java that diagonalises a matrix of atleast
3x3(rows and columns).thanks
Thanx
June 2, 2005 - 5am Central time zone
Reviewer: Kifayah from Jordan
Thanx alot 4 such solution . I wonder f it works on oracle9i because i have done the steps, most
of them was done successfully but the last one (exec get_dir_list( 'c:\*.txt' ); faild as
exception handler in java as follows:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "user1trig.GET_DIR_LIST", line 0
ORA-06512: at line 1
Regards
Followup June 2, 2005 - 4pm Central time zone:
ctl-f for nullpointer
June 16, 2005 - 5pm Central time zone
Reviewer: A reader
Tom,
In 9204 can we read the contents of directory from PLSQL(utl_file or some other package) without
using Java?
Thanks.
Followup June 16, 2005 - 10pm Central time zone:
you could also use C, but plsql doesn't have anything "native"
August 16, 2005 - 3pm Central time zone
Reviewer: Khozema from CA, USA
Heres the cut and paste of the code
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) };
}
}
}
/
I get the following error while compiling
create or replace and compile java source named "DirList" as
*
ERROR at line 1:
ORA-29536: badly formed source: Encountered "<EOF>" at line 1, column 17.
Was expecting:
";" ...
I dont see anything wrong though.
Please help
Followup August 17, 2005 - 12pm Central time zone:
ops$tkyte@ORA9IR2> create or replace and compile java source named "DirList" as
2 import java.io.*;
3 import java.sql.*;
4
5 public class DirList
6 {
7 public static void getList(String directory)
8 throws SQLException
9 {
10 File path = new File( directory );
11 String[] list = path.list();
12 String element;
13
14 for(int i = 0; i < list.length; i++)
15 {
16 element = list[i];
17 #sql { INSERT INTO DIR_LIST (FILENAME)
18 VALUES (:element) };
19 }
20 }
21
22 }
23 /
Java created.
I just cut and pasted, you should do the same in sqlplus.
August 17, 2005 - 2pm Central time zone
Reviewer: Khozema Khambati from CA, USA
I did cut and paste. And I got the error.
I am on 8i - 8.1.7 database running Oracle Apps
And java is installed too. And I have granted javauserpriv from system to apps user. Do I need to
anything else.
Thanks
Followup August 17, 2005 - 2pm Central time zone:
why don't I get to see your cut and paste from sqlplus like me??!?!???
show me what I show you. prove to me that the text you say you are putting in, is in fact what you
are putting in.
August 18, 2005 - 1pm Central time zone
Reviewer: A reader
Heres what I found.
Sqlplus 8.0 doesnt support the compilation of Java class. I downloaded 10i client from OTN.
I am testing the script now, and looks like everything is working.
Thanks a lot for lots of help and great job helping.
President likes it woohoo
August 25, 2005 - 9pm Central time zone
Reviewer: Kevin Meade from Connecticut
Thanks Tom, once again you are the best.
Sorry I won't be at the local meeting you are comming to present here soon (CT), but many of my
friends are going so I'll have one say hello for me.
FYI, the President of our company learned we recently used this code as the only JAVA routine in
our system. He said: "why are you guys putting code in our system that you can't write?" (we
aren't great Java guys). We said: "Tom Kyte wrote it from asktom". He said: "OH if its from Tom
then its OK."
Thanks bud. Kevin CT
This is really great
October 6, 2005 - 11am Central time zone
Reviewer: Zohar from Israel
How do I change this code to distinguish between directories and files (or just ignore the
directories when inserting it into the temp-table)?
Thanks!
Zohar
Followup October 6, 2005 - 12pm Central time zone:
read up on the java API's to see what you can do with them.
Rename a file
December 2, 2005 - 10am Central time zone
Reviewer: daniel
Does anybody know how to rename a file on OS using java call from a database?
Followup December 2, 2005 - 11am Central time zone:
hows about just using utl_file?
Not sure if it will work
December 2, 2005 - 12pm Central time zone
Reviewer: daniel
I"m not sure if it would work...I've done some more research on your site and came up with an
aproach.
Here is what I need to do. Maybe I'm crazy and there is a better way to do it:) I need to read the
content of the directory to get the audit files (audit_trail_dest) and read all the files that are
currently out there and load them into a database.
1. Create a external table (using file name log_to_load.aud)
2. Read a directory and load the list of files into a temporary table (found the code on this site
"exec get_dir_list").
3. Using a stored procedure and a temporary table I would build the following statement (also
found the code on your site):
exec rc('/usr/bin/mv audit_file.aud log_to_load.aud).
4. Then simply use the external table to load each row into a table holding the audit information.
I'm open to any suguesstions. I need to implement it on about 50 servers (AIX, SUN, HP) so I
wanted to run it from a Database, instead of OS.
Followup December 2, 2005 - 12pm Central time zone:
why not just audit to the database in the first place??? I mean, you are trashing the audit logs
in the file system?
audit_sys_operations
December 2, 2005 - 2pm Central time zone
Reviewer: daniel
When you set audit_sys_operations=true that creates log files on the os for sysdba, sysoper
activities...we're also loging all other activity to sys.aud$.
Followup December 3, 2005 - 10am Central time zone:
and putting them into the database where sys et.al. can truncate/delete them freely?
or are you doing this into ANOTHER database (in which case, I'd just write a program outside the
database to load them)
10gr2 interestingly enough can dump the audit trail to the OS file system in XML *and* provides at
the same time a v$ view that lets you query the XML as if it were in a table.
Repository
December 3, 2005 - 1pm Central time zone
Reviewer: Daniel
I would be loading it into another database. We have a repository where we store all the sys.aud$
data from about 50 or so databases on about 50 or so servers.. Now we are required to store the
audit files from OS as well. I could write a scrip to do it, I would prefer not to...The reason is
that these servers range are Solarix, AIX, LINUX and HP-UX. We don't have a common transfer
protocol...some use sftp, some use ftp etc...I don't even have access to some of these
servers...(and all this is outside of my control and would take ages to change). So I wanted to
see if doing it from inside of the database would be possible. At least I have control over that.
Followup December 3, 2005 - 3pm Central time zone:
why not just alter the external table to point to the file of interest, load it, erase the file,
repeat
or, just use a bfile and dbms_lob to read files?
***
December 3, 2005 - 3pm Central time zone
Reviewer: Daniel
I wasn't sure what kind of performance hit I could take if I had to alter the table...I guess I
don't like doing ddl from within pl/sql. I'll look closely into bfile concept...haven't used it so
far.
Followup December 4, 2005 - 6am Central time zone:
well, the alter will invalidate the statement (causing a hard parse for each load), you can use
utl_file.frename to rename files as well (you don't need to "mv", which is just rename in
disguise).
Oracle 10 XE
June 22, 2006 - 6am Central time zone
Reviewer: Andre Meyer from GERMANY
Hello,
in reference to the original question:
Is it possible to do this in XE as it don't support Java?
Without .NET?
Regards
Andre
Followup June 22, 2006 - 12pm Central time zone:
external procedures could be used.
June 27, 2006 - 2pm Central time zone
Reviewer: Alexander the ok
Hello Tom,
Can we create files in Oracle using pl/sql? Especially if say we where using windows with 9i?
Thanks you.
Followup June 27, 2006 - 2pm Central time zone:
utl_file, yes.
June 27, 2006 - 3pm Central time zone
Reviewer: Alexander the ok
Are you sure about that? Could you tell me which subprogram it is? I can only find
reading/writing from existing files.
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/u_file.htm#996960
June 27, 2006 - 3pm Central time zone
Reviewer: Alexander the ok
That's really slick thanks.
reading directory
July 10, 2006 - 5pm Central time zone
Reviewer: sam
Tom:
Does reading directory work with 8i database. WHen I ran the java stored proedure it kept going. It
worked fine in 9i.
Followup July 11, 2006 - 7pm Central time zone:
"it kept going"
does not make sense to me, what do you mean by that.
the example you are using from above - well, it was written for 8i - 8.1.5 specifically.
a question
July 17, 2006 - 4am Central time zone
Reviewer: Kin from Hong Kong
Dear Tom,
I have tried your codes but I get this error:
SQL> create or replace and compile java source named "DirList"
2 AS
3
4
5 import java.io.*;
6 import java.sql.*;
7 import oracle.sql.*;
8 import oracle.jdbc.*;
9
10 public class DirList
11 {
12 public static void getList(String directory) throws SQLException
13
14 {
15
16 File path = new File( directory );
17 String[] list = path.list();
18 String element;
19
20
21 for(int i = 0;i < list.length; i++)
22 {
23 element = list[i];
24 #sql{ Insert into DIR_LIST (FILENAME) values (:element) };
25
26 }
27 }
28
29 };
30 /
create or replace procedure get_dir_list( p_directory in varchar2 )
as language java
name 'DirList.getList( java.lang.String )';
/
Java created.
SQL> SQL> SQL> 2 3 4
Procedure created.
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: Invalid column index
ORA-06512: at "AOSDEV.GET_DIR_LIST", line 1
ORA-06512: at line 1
I am using Oracle 10g with Unix. I am looking forward to hear from you~
best regards,
Kin
Followup July 17, 2006 - 1pm Central time zone:
how's about you give FULL example????? like, oh, the call you make?
ops$tkyte@ORA10GR2> create global temporary table DIR_LIST
2 ( filename varchar2(255) )
3 on commit delete rows
4 /
Table created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace and compile java source named "DirList"
2 AS
3 import java.io.*;
4 import java.sql.*;
5 import oracle.sql.*;
6 import oracle.jdbc.*;
7 public class DirList
8 {
9 public static void getList(String directory) throws SQLException
10 {
11 File path = new File( directory );
12 String[] list = path.list();
13 String element;
14 for(int i = 0;i < list.length; i++)
15 {
16 element = list[i];
17 #sql{ Insert into DIR_LIST (FILENAME) values (:element) };
18 }
19 }
20 };
21 /
Java created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure get_dir_list( p_directory in varchar2 )
2 as language java
3 name 'DirList.getList( java.lang.String )';
4 /
Procedure created.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_java.grant_permission( 'OPS$TKYTE', 'SYS:java.io.FilePermission',
'/tmp', 'read' )
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec get_dir_list( '/tmp' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select * from dir_list;
FILENAME
-------------------------------------------------------------------------------
jd_sockV4
.iroha_unix
.font-unix
.fam_socket
orbit-root
xtmpx.sql
ssh-RcnX4968
orbit-tkyte
ssh-aMYI5569
ssh-SmOS3421
ssh-dAdr4965
termout.sql
ssh-FONe3417
ssh-mxhH3418
ssh-uwaT3766
ssh-GcEO3490
ssh-crNF3698
ssh-bOtB3574
ssh-nIJZ3737
ssh-mhNT3596
ssh-Elqo3812
ssh-ksQC3466
notermout.sql
ssh-Uowj4393
ssh-OZc30228
jpsock.142_04.32534
OSL_PIPE_500_SingleOfficeIPC_88abacfc386e83b4df4a27cdd02ff
jpsock.142_04.6968
28 rows selected.
a Question
July 17, 2006 - 4am Central time zone
Reviewer: Kin from Hong Kong
Those errors appear all the time after I execute thte procedure.
July 17, 2006 - 8pm Central time zone
Reviewer: Kin from Hong kong
My call is just like:
exec get_dir_list('/mnt/home1/topro/asint/' );
select * from DIR_LIST where rownum < 5;
I don't know why...
July 17, 2006 - 8pm Central time zone
Reviewer: Kin from Hong kong
When I gramt the permission it shows like:
SQL> exec dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' )
BEGIN dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' ); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: -1 is not the number of a user or role
ORA-06512: at "SYS.DBMS_JAVA", line 313
ORA-06512: at line 1
SQL> exec get_dir_list( '/mnt/home1/toprod/asint/' );
BEGIN get_dir_list( '/mnt/home1/toprod/asint/' ); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.sql.SQLException: Invalid column index
ORA-06512: at "AOSDEV.GET_DIR_LIST", line 1
ORA-06512: at line 1
Is that because the grant right of me?
Followup July 18, 2006 - 8am Central time zone:
after seeing the dbms_java call - I'm inclined to believe the java installation is botched there -
that call should NOT be failing like that.
Please utilize support for this one.
Invalid File ID
August 9, 2006 - 12pm Central time zone
Reviewer: Paulo from Portugal
yes it helps, but i have another problem
when i'm running my package, appears the follow errors:
ERRO na linha 1:
ORA-29282: ID do ficheiro inválida
ORA-06512: na "SYS.UTL_FILE", linha 714
ORA-06512: na "EV01D_DATA.PRODUTO_OPORTUNIDADE", linha 275
ORA-29283: operação do ficheiro inválida
ORA-06512: na "SYS.UTL_FILE", linha 449
ORA-29283: operação do ficheiro inválida
ORA-06512: na linha 1
I have done:
create directory rw_dir as '/temp';
grant read on directory rw_dir to public;
HOST MD C:\temp;
All of this was ok.
I don't know what's missing.
This code works on my machine, but when I run on another (server), it fails. This machine SO is a
unix.
It's my problems with permissions?
I'm not a dba on the server
should I ask the dba to grant me the privelegies?
Bye
Followup August 9, 2006 - 1pm Central time zone:
you need to show the MIMIMAL code needed to produce this.
this sounds like something happening AFTER you call fopen
Invalid File ID
August 9, 2006 - 1pm Central time zone
Reviewer: Paulo from Portugal
ok were it goes
-- Name of the file
str := 'Seguros_'||to_char(LOCALTIMESTAMP), 'dd.mm.yyyy_HH24.MM')||'.txt';
-- Open the file
fseg := utl_file.fopen('RW_DIR',str,'W');
-- Put a line in a string
str := 'Produto|Nome|Sobrenome|Telefone|Telemovel|E_Mail|NIF|Código do Ponto de Entrega|Rua|Número
da Porta|Número do Complemento|Zona|Código Postal|Localidade';
-- Put the string in the file
utl_file.put_line(fseg,str);
The problem resides in 'RW_DIR'. I believe that this object it's not point to the correct
directory, because i'm not a dba of th DB. But when I execute:
SELECT DIRECTORY_PATH
FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'RW_DIR';
DIRECTORY_PATH
--------------------------------------------------------------------------------
/temp
he gives the correct path.
I'm out of ideas
The machine is a unix
Followup August 9, 2006 - 4pm Central time zone:
what line of code is failing - can you write a small snippet that actually runs from START to
FINISH in sqplus and capture the entire error stack and code.
Invalid File Id
August 9, 2006 - 1pm Central time zone
Reviewer: Paulo from Portugal
sorry, run this code instead
CREATE OR REPLACE PACKAGE BODY PRODUTO_OPORTUNIDADE AS
PROCEDURE PRODUTOS
IS
fseg utl_file.file_type;
str varchar2(500);
v_err_sqlcode varchar2(10);
v_err_sqlerrmsg varchar2(200);
BEGIN
str := 'Seguros_'||to_char(LOCALTIMESTAMP(), 'dd.mm.yyyy_HH24.MM')||'.txt';
fseg := utl_file.fopen('RW_DIR',str,'W');
str := 'Produto|Nome|Sobrenome|Telefone|Telemovel|E_Mail|NIF|Código do Ponto de
Entrega|Rua|Número da Porta|Número do Complemento|Zona|Código Postal|Localidade';
utl_file.put_line(fseg,str);
v_err_sqlcode := null;
v_err_sqlerrmsg := null;
EXCEPTION
WHEN OTHERS THEN
v_err_sqlcode := SQLCODE;
v_err_sqlerrmsg := substr(SQLERRM, 1, 200);
str := '*** ' ||v_err_sqlcode||' - '||v_err_sqlerrmsg;
dbms_output.put_line(str);
END PRODUTOS;
END PRODUTO_OPORTUNIDADE;
/
Followup August 9, 2006 - 4pm Central time zone:
you run it :) and cut and paste it.
I want the CREATE in the cut and paste so we have LINE NUMBERS to look at.
lose the useless and actually "information reducing and quite in the way" exception block you have.
get rid of the entire exception block - it will make your ability to figure out what the issue is
impossibly hard.
Never code exception blocks like that - just a waste of keystrokes and only makes the errors "seem
like they are not happening"
Thanks for the help??
October 6, 2006 - 8am Central time zone
Reviewer: omer from jeddah, saudia arabia
This was very helpful ,i was struggling to get to this, well lots of good stuff on your site.
Read from an Oracle defined directory
October 10, 2006 - 4am Central time zone
Reviewer: Mette from Denmark
Hi Tom
Thanks for the java program for reading the directory, it works perfectly apart from the fact that
we use Oracle dirs when accessing the filesystem. (create dir_udump as d:\......udump' fashion). So
I cant use it in prod unless I access through Oracle dirs
How do I modify the java program to use an Oracle dir for input instead of the actual path&dir
name?
regards
Mette
Followup October 10, 2006 - 7am Central time zone:
you would not, you see - java works on "real directories". You could pass this the directory name
and have it query dba_directories to get the real name but you will HAVE to grant permission on the
REAL directory name - not the oracle indirect directory name.
Think I solved it my self
October 10, 2006 - 6am Central time zone
Reviewer: Mette from Denmark
Hi again
Maybe not the most elegant solution - so no big laughs, please :-)
regards
Mette
-------------
CREATE OR REPLACE procedure get_dir_list_p( p_dir in varchar2)
is
w_dir varchar2(100);
begin
select directory_path into w_dir
from all_directories
where directory_name = p_dir;
get_dir_list(w_dir);
exception
when no_data_found then
get_dir_list(p_dir);
end get_dir_list_p;
/
Followup October 10, 2006 - 8am Central time zone:
that'll work - yes, but java still is working with "real directories" and needs be granted on real
directories so on production, they'll still be seeing "real directories"
How about date & length into the dirlist?
October 10, 2006 - 10am Central time zone
Reviewer: Mette from Denmark
Hi again.
I have added 2 new columns filelength, filetype and filemodified, but I dont have that much
knowledge of java, so the date is annoying me: (sorry for the weird tabs here - dont know how to
remove them ...)
translated to english the message is like this: cannot analyze the sql sentence. Cant decide
elementtype for host element #4 java.util.date
Can you guide me nearer to a solution?
regards
Mette (again)
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;
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];
String fpath=directory+"/"+list[i];
File f = new File(fpath);
long len;
Date date;
String ftype;
if (f.isFile()) {
len = f.length();
date = new Date(f.lastModified());
ftype = "F";
} else {
len = 0;
date = null;
ftype = "D";
}
#sql { INSERT INTO DIR_LIST (FILENAME, filelength, filetype, filemodified)
VALUES (:element, :len, :ftype, :date) };
}
}
}
/
Followup October 10, 2006 - 12pm Central time zone:
you'll need to find a java programmer to help you out :)
sorry... I don't have that code at hand, the concept is here, a little digging around should turn
up how to do that.
Java not installed
October 10, 2006 - 11am Central time zone
Reviewer: Vinayak from Zurich
Hi Tom,
I am using Oracle 10g Express Edition. When I tried to run your script on the very first followup
of this thread, it said "Java not installed".
Is there any way to run this on Oracle 10g XE.
Followup October 10, 2006 - 8pm Central time zone:
nope, no java in XE at all.
A little digging did the trick
October 10, 2006 - 1pm Central time zone
Reviewer: Mette from Denmark
... as you said:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DirList" AS
import java.io.*;
import java.sql.*;
import java.util.Date;
import java.text.SimpleDateFormat;
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];
String fpath=directory+"/"+list[i];
File f = new File(fpath);
long len;
Date date;
String ftype;
String sqldate;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
if (f.isFile()) {
len = f.length();
date = new Date(f.lastModified());
sqldate = df.format(date) ;
ftype = "F";
} else {
len = 0;
sqldate = null;
ftype = "D";
}
#sql { INSERT INTO DIR_LIST (FILENAME, filelength, filetype, filemodified)
VALUES (:element, :len, :ftype, to_date(:sqldate,'YYYY-MM-DD HH24:MI:SS')) };
}
}
}
/
------------------
Now I get date/time modified, length, type and filename.
Thank you very much for the great idea
regards
Mette
Followup October 10, 2006 - 8pm Central time zone:
and thank YOU very much for contributing back!
to Mette
October 12, 2006 - 9am Central time zone
Reviewer: Michel Cadot from France
Thank you very much for this update, Mette.
Just a remark: compared to the original DirList procedure you now need read privilege on all the
files in the directory and not only on the directory itself.
Regards
Michel
How to read a network directory
November 14, 2006 - 11am Central time zone
Reviewer: Edu from Spain
Hi Tom
Is it possible to read a network directory (f.e: \\server1\dir1) following your examples?
Thanks.
Followup November 15, 2006 - 6am Central time zone:
depends on whether the oracle process can see it.
remember, windows is not easy like unix/linux. Just because YOUR user session can see
\\server1\dir1 does not mean the user session running the oracle process can - in fact, you have to
bend over backwards to make that happen.
January 9, 2007 - 5am Central time zone
Reviewer: A reader
Hello Tom,
I tried to create java source "DirList" usin the code:
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) };
}
}
}
/
This is the error I get:
DirList:11: Class string not found.
I am on 10g 10.1.0.3.0 / Windows2003.
Thanks in advance,
Denes Kubicek
Followup January 11, 2007 - 9am Central time zone:
cut and paste straight from sqlplus the entire thing - just like I do.
January 14, 2007 - 12pm Central time zone
Reviewer: A reader from Bensheim, Germany
O.K.
This is what I did:
As SYS:
SQL> GRANT JAVAUSERPRIV to ORACLE_BOM;
As ORACLE_BOM user:
SQL> CREATE GLOBAL TEMPORARY TABLE dir_list
2 ( filename VARCHAR2(255) )
3 ON COMMIT DELETE ROWS
4 /
Tabelle wurde erstellt.
SQL> CREATE OR REPLACE
2 AND COMPILE JAVA SOURCE NAMED "DirList"
3 AS
4 import java.io.*;
5 import java.sql.*;
6
7 public class dirlist
8 {
9 public static void getlist(string directory)
10 throws sqlexception
11 {
12 file path = new file( directory );
13 string[] list = path.list();
14 string element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { insert into dir_list (filename)
20 values (:element) };
21 }
22 }
23
24 }
25 /
Warnung: Java mit Kompilierungsfehlern erstellt.
SQL>
SQL> CREATE OR REPLACE PROCEDURE get_dir_list (p_directory IN VARCHAR2)
2 AS
3 LANGUAGE JAVA
4 NAME 'DirList.getList( java.lang.String )';
5 /
Prozedur wurde erstellt.
(if this is what you meant)
Denes Kubicek
January 14, 2007 - 12pm Central time zone
Reviewer: A reader
O.K.
I found a mistake. Toad formating changed some capital letters
into lower.
Sorry for not seeing this.
Thanks again.
Denes Kubicek
Why i am getting this?
February 8, 2007 - 10am Central time zone
Reviewer: Sumanth from India
Hi Tom,
Thanks for this excellent source.
when i try to execute this source in my system
1 create or replace and compile java source named DirList
2 as
3 import java.io.*;
4 import java.sql.*;
5 public class DirList
6 {
7 public static void getList(String directory) throws SQLException
8 {
9 File path = new File( directory );
10 String[] list = path.list();
11 String element;
12 for(int i = 0; i < list.length; i++)
13 {
14 element = list[i];
15 #sql { INSERT INTO DIR_LIST (FILENAME)
16 VALUES (:element) };
17 }
18 }
19* }
20 /
create or replace and compile java source named DirList
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL/> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
what went wrong here?
Followup February 8, 2007 - 11am Central time zone:
please utilize support.
on another server
February 26, 2007 - 12pm Central time zone
Reviewer: A reader
exec system.get_dir_list( 'C:\Temp\Extract' );
if the directory is on another server
like I have to use '//tomkyte/datasource/scripts/'
instaed of 'C:\Temp\Extract' its not working like that.
Followup February 26, 2007 - 3pm Central time zone:
the file system must be available to the Oracle database software itself (which is running as a low privileged user as a service and cannot see your network shares)
waht about created_date
February 26, 2007 - 5pm Central time zone
Reviewer: A reader
How can I get cretaed date attribute of a file in that particular directory in Oracle and inserteing it into a table.
Followup February 26, 2007 - 5pm Central time zone:
same way you would read a list of files - a tiny bit of java.
doesn't know java
February 26, 2007 - 5pm Central time zone
Reviewer: A reader
nobody know java here.
Followup February 27, 2007 - 9am Central time zone:
I didn't either really, however, by searching around a bit - I was able to glue together the simple example. I'm sure if you looked into a little on your own, you would be able to figure out how to get the needed information from the Java APIs.
March 7, 2007 - 2pm Central time zone
Reviewer: A reader
Tom,
I asked my DBA to grant this permission:
dbms_java.grant_permission( 'WCLAIM', 'SYS:java.io.FilePermission','WCLAIM_FORD', 'read,write' )
and used your examples, but getting following error while executing the procedure:
SQL> exec get_dir_list('/WCLAIM_FORD')
BEGIN get_dir_list('/WCLAIM_FORD'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
\WCLAIM_FORD read) has not been granted to WCLAIM. The PL/SQL to grant this is
dbms_java.grant_permission( 'WCLAIM', 'SYS:java.io.FilePermission',
'\WCLAIM_FORD', 'read' )
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1
Could you please let me know what is wrong?
Followup March 7, 2007 - 3pm Central time zone:
either
a) you did not log in again after getting granted
b) your dba did not do the grant.
March 7, 2007 - 2pm Central time zone
Reviewer: A reader
Tom,
This is the output I am getting when I create the package:
SQL> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6
7 public class DirList
8 {
9 public static void getList(String directory)
10 throws SQLException
11 {
12 File path = new File( directory );
13 String[] list = path.list();
14 String element;
15
16 for(int i = 0; i < list.length; i++)
17 {
18 element = list[i];
19 #sql { INSERT INTO DIR_LIST (FILENAME)
20 VALUES (:element) };
21 }
22 }
23 /
and compile java source named "DirList"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 19, column 4.
Was expecting one of:
"#sql" ...
"static" ...
"abstract" ...
"final" ...
"private" ...
"protected" ...
"public" ...
"strictfp" ...
"boolean" ...
"byte" ...
"char" ...
"class" ...
"double" ...
"float" ...
"int" ...
"interface" ...
"long" ...
"native" ...
"short" ...
"synchronized" ...
"transient" ...
"void" ...
"volatile" ...
<IDENTIFIER> ...
"{" ...
"}" ...
";" ...
Followup March 7, 2007 - 3pm Central time zone:
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.
ops$tkyte%ORA9IR2> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6 public class DirList
7 {
8 public static void getList(String directory)
9 throws SQLException
10 {
11 File path = new File( directory );
12 String[] list = path.list();
13 String element;
14 for(int i = 0; i < list.length; i++)
15 {
16 element = list[i];
17 #sql { INSERT INTO DIR_LIST (FILENAME)
18 VALUES (:element) };
19 }
20 }
21 /
and compile java source named "DirList"
*
ERROR at line 2:
ORA-29536: badly formed source: Encountered "<EOF>" at line 17, column 1.
Was expecting one of:
"#sql" ...
"static" ...
"abstract" ...
"final" ...
"private" ...
"protected" ...
"public" ...
"strictfp" ...
"boolean" ...
"byte" ...
"char" ...
"class" ...
"double" ...
"float" ...
"int" ...
"interface" ...
"long" ...
"native" ...
"short" ...
"synchronized" ...
"transient" ...
"void" ...
"volatile" ...
<IDENTIFIER> ...
"{" ...
"}" ...
";" ...
ops$tkyte%ORA9IR2> @mine
ops$tkyte%ORA9IR2> create or replace
2 and compile java source named "DirList"
3 as
4 import java.io.*;
5 import java.sql.*;
6 public class DirList
7 {
8 public static void getList(String directory)
9 throws SQLException
10 {
11 File path = new File( directory );
12 String[] list = path.list();
13 String element;
14 for(int i = 0; i < list.length; i++)
15 {
16 element = list[i];
17 #sql { INSERT INTO DIR_LIST (FILENAME)
18 VALUES (:element) };
19 }
20 }
21 }
22 /
Java created.
March 7, 2007 - 4pm Central time zone
Reviewer: A reader
Thanks Tom, that worked. But getting following error when executing the stored procedure:
SQL> exec get_dir_list('WCLAIM_FORD')
BEGIN get_dir_list('WCLAIM_FORD'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1
Directory has files. I have tried with both the inputs:
1.'/wclaim_ford'
2.'wclaim_ford'
What is the mistake?
Thanks.
Followup March 7, 2007 - 4pm Central time zone:
i doubt wclaim_ford is a directory
it would have to be an immediate subdirectory of the current working directory (which you don't control)
ctl-f for nullpointer on this page.
use a real directory name.
March 8, 2007 - 9am Central time zone
Reviewer: A reader
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.
Followup March 8, 2007 - 11am Central time zone:
by fully qualifying the path
/path/to/where/the/stuff/actually/is
would let you access the subdirectory "is" under that path.
March 8, 2007 - 11am Central time zone
Reviewer: A reader
Thanks Tom, I did that but it gives following error:
SQL> exec get_dir_list('c:\1\jan_loads')
BEGIN get_dir_list('c:\1\jan_loads'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.NullPointerException
ORA-06512: at "WCLAIM.GET_DIR_LIST", line 1
ORA-06512: at line 1
on my c drive I have folder called 1 and a subfolder 'jan_loads' under it. And the subfolder has 10 files. What is the mistake?
Thanks
Followup March 8, 2007 - 12pm Central time zone:
on the DATABASE SERVER is there a c:\1\jan_loads
the server can only read the directory of..... the server.
So, I've a feeling YOU have a directory by that name, but the server does not.
on xe
March 30, 2007 - 4pm Central time zone
Reviewer: Hegyvari Krisztian from Hungary
Hi,
I came here when I had the problem of reading a directory list under 8.1.7.4 and been successfully using your solution up until now. (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?
<sigh> all the other limitations on XE can be understood, but why is the JVM gone? </sigh>
Cheers.
Krisztian
never mind
April 1, 2007 - 4pm Central time zone
Reviewer: Hegyvari Krisztian from Hungary
I found one of your old posts from 1997 on groups.google.com, where a "client" program is running on the server and listens and answers requests using dbms_pipe. I think I will write something like that.
K
July 18, 2007 - 8am Central time zone
Reviewer: Shehab
Hello
I followed Exactly the same steps and codes
but when i executed the procedure as
@ exec get_dir_list('/usr/src/bfile_blob');
i had the following errors
BEGIN get_dir_list('/usr/src/bfile_blob'); END;
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: O
ORA-06512: at "TEST.GET_DIR_LIST", line 1
ORA-06512: at line 1
Please its urgent to get over this problem
Followup July 18, 2007 - 10am Central time zone:
ok, put some debug into the code and see where it is going wrong.
put some system.out.println's in there and using dbms_java to set the output on, test it.
October 5, 2007 - 9am Central time zone
Reviewer: A reader
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.
The error I am faceing is that when I execute
exec get_dir_list( 'C:\temp' ); I get an error
"ORA-00900: invalid SQL statement".
Can you please address me where I am going wrong?
Thanks in Advance.
October 8, 2007 - 6am Central time zone
Reviewer: A reader
Execuse me for my previous posting.
I understand that the files should be available on same box/instance.
Installation
February 13, 2008 - 7am Central time zone
Reviewer: Ramesh from India
Hi Tom,
Can i install the Oracle8i (8.1.6.2.0) on A.S 4 it is possible r not r either i had to
install on A.S.3,plse
tell me as it is very urgent to me.
Thanks,
Followup February 13, 2008 - 1pm Central time zone:
no clue what "r not r" is
no clue what "A.S 4" could be
clueless as to what "A.S.3" is
and really at a loss to understand how installing an obsolete, unsupported piece of software on something has to do with reading files in a directory?
Do without Java for the directory listing problem
June 5, 2008 - 6am Central time zone
Reviewer: Mungo Henning from Scotland
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... sigh.
The PC hosting this XE database is otherwise idle, hence I stumbled upon a workaround.
Data files arrive into the aforementioned PC via FTP from some other (remote) process.
But here's the trick: these data files have a uniform name that includes a date stamp. Something
like "DataFile080605_12:23:59" or so: the name depending on the time of the data file. (080605
meaning 2008 June 5th for those that don't twig this)
So my trick is to record the name of the last processed data file in a database table and then
(brute force) cycle through all future names using PLSQL until the utl_file.fopen succeeds.
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
January 29, 2009 - 7pm Central time zone
Reviewer: kyle from Left Coast, Canada
When I gramt the permission it shows like:
SQL> exec dbms_java.grant_permission( 'aosdev', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' )
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: -1 is not the number of a user or role
ORA-06512: at "SYS.DBMS_JAVA", line 313
ORA-06512: at line 1
Followup July 18, 2006 - 8am US/Eastern:
after seeing the dbms_java call - I'm inclined to believe the java installation is botched there -
that call should NOT be failing like that.
Please utilize support for this one.
I resolved the SYS.DBMS_JAVA error by changing the username to uppercase.
Like this:
exec dbms_java.grant_permission( 'AOSDEV', 'SYS:java.io.FilePermission',
'/mnt/home1/toprod/asint/', 'read' )
Let's use a table function...
March 14, 2009 - 3am Central time zone
Reviewer: Anthony Wilson from Perth, Western Australia
My standard solution to this:
create or replace type dir_entry as object (
file_type varchar2(1),
readable varchar2(1),
writeable varchar2(1),
hidden varchar2(1),
file_size number,
modified date,
name varchar2(4000)
);
/
create or replace type dir_array as table of dir_entry;
/
create or replace and compile java source named "Util" as
import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;
import oracle.jdbc.driver.OracleDriver;
public class Util {
private static Connection conn;
static {
try {
conn = (new OracleDriver()).defaultConnection();
} catch (SQLException e) {
System.out.println(e);
}
}
/**
* List the files in the directory represented by the given Oracle DIRECTORY
* object.
*
* @param dirname The name of the DIRECTORY object for which we want to list
* the files (case sensitive).
* @throws IOException
* @throws SQLException
*/
public static ARRAY listFiles(String dirname)
throws IOException, SQLException {
String dirpath = getDirectoryPath(dirname);
File directory = getDirectory(dirpath);
STRUCT[] ret = fileList(directory);
// Create an array descriptor and return it.
ArrayDescriptor desc = ArrayDescriptor.createDescriptor (
"DIR_ARRAY", conn);
return new ARRAY(desc, conn, ret);
}
/**
* Create a File object with the abstract pathname given by the parameter.
*
* @param dirpath The filesystem path of the directory
* @throws IOException If the directory represented by this pathname does
* not exist, or if it is a file.
*/
private static File getDirectory(String dirpath) throws IOException {
File directory = new File(dirpath);
if(!directory.exists()) {
throw new IOException("Directory: "+dirpath+" does not exist.");
}
if(!directory.isDirectory()) {
throw new IOException("Path: "+dirpath+" is not a directory.");
}
return directory;
}
/**
* Get the filesystem path for the Oracle DIRECTORY object given by the
* input parameter.
*
* @param dir The name of the DIRECTORY object for which we want the path.
* @throws IOException If there is no DIRECTORY object with the given name.
*/
private static String getDirectoryPath(String dir)
throws SQLException, IOException {
String sql = "select directory_path from all_directories where " +
"directory_name = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setString(1, dir);
ResultSet rs = s.executeQuery();
// There should be one row and one only.
if(rs.next()) {
return rs.getString(1);
} else {
throw new IOException("Directory object "+dir+" does not exist.");
}
}
/**
* Create an array of STRUCT objects representing the files in the given
* directory.
*
* @param directory The File object representing the directory.
* @throws SQLException
*/
private static STRUCT[] fileList(File directory) throws SQLException {
// Create the array of files to add.
File[] files = directory.listFiles (
new FilenameFilter() {
// Accept all files
public boolean accept(File dirpath, String name) {
return true;
}
}
);
// No files in directory
if(files == null) {
return null;
}
STRUCT[] ret = new STRUCT[files.length];
// Create the struct entry for each file.
for(int i=0; i<files.length; i++) {
File f = files[i];
StructDescriptor sd = StructDescriptor.createDescriptor (
"DIR_ENTRY", conn);
Object[] attributes = {
f.isDirectory() ? "D" : (f.isFile() ? "F" : "U"),
f.canRead() ? "Y" : "N",
f.canWrite() ? "Y" : "N",
f.isHidden() ? "Y" : "N",
new Long(f.length()),
new Timestamp(f.lastModified()),
f.getName()
};
STRUCT s = new STRUCT(sd, conn, attributes);
ret[i] = s;
}
return ret;
}
}
/
create or replace package util as
function ls(p_dirname in varchar2) return dir_array;
end;
/
create or replace package body util as
function ls(p_dirname in varchar2) return dir_array is
language java
name 'Util.listFiles(java.lang.String) return oracle.sql.ARRAY';
end;
/
Then you can run queries like the following:
1 select *
2 from table(util.ls('YOUR_DIR_NAME'))
3 where name like '%.zip'
4 and modified >= sysdate - 3
5* and file_size > 1000000
SQL> /
F R W H FILE_SIZE MODIFIED NAME
- - - - ---------- --------- ----------------------------------------
F Y Y N 1321135 09-MAR-09 w00076.20090309101312.zip
F Y Y N 1321337 09-MAR-09 w00076.20090309102948.zip
F Y Y N 1321576 09-MAR-09 w00076.20090309112136.zip
F Y Y N 1320715 09-MAR-09 w00076.20090309110427.zip
F Y Y N 1321437 09-MAR-09 w00076.20090309123505.zip
Hope that helps...
cheers,
Anthony
Packaged pipelined function to list directory
August 24, 2009 - 4am Central time zone
Reviewer: Adrien Sales from France
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.
Here is a call example :
<--
select *
from table(LIST_FILES(iDirectory => 'H:\Incoming', -- The dir
iMatchStrIngRegExp => NULL,-- All files
iJackSumAlgorithm => 'crc32',
iJacksumEncodIng => 8,
iJacksumSeparator => NULL,
iJacksumFormat => '#CHECKSUM',
iJacksumGroupingIndex => -1,
iJacksumGroupingSymbol => NULL,
igetMimeType => 1
));
-->
It returns such pipelined table :
FILE_ABSOLUTE_PATH NVARCHAR2(255),
FILE_CANONICAL_PATH NVARCHAR2(255),
FILE_NAME NVARCHAR2(255),
FILE_PARENT NVARCHAR2(255),
FILE_PATH NVARCHAR2(255),
PATH_SEPARATOR NVARCHAR2(1),
PATH_SEPARATOR_CHAR NVARCHAR2(1),
SEPARATOR NVARCHAR2(1),
SEPARATOR_CHAR NVARCHAR2(255),
IS_FILE_READABLE INTEGER,
IS_FILE_WRITABLE INTEGER,
IS_FILE_EXISTING INTEGER,
FILE_HASH_CODE NVARCHAR2(255),
IS_FILE_ABSOLUTE INTEGER ,
IS_FILE_DIRECTORY INTEGER,
IS_FILE INTEGER,
IS_FILE_HIDDEN INTEGER,
LAST_MODIFIED INTEGER,
LAST_MODIFIED_DATE TIMESTAMP,
LENGTH_KO INTEGER,
LENGTH_MO NUMBER,
JACKSUM_ALGORITHM VARCHAR2(10),
JACKSUM_BRUTE_HASH VARCHAR2(500),
JACKSUM_FORMATTED_HASH NVARCHAR2(2000),
MIME_DESCRIPTION VARCHAR2(100),
MIME_EXTENSION VARCHAR2(10),
MIME_TYPE VARCHAR2(100),
MIME_COMPARATOR VARCHAR2(100)
Have a look at screenshot for more details :
https://sourceforge.net/project/screenshots.php?group_id=255704
Hope this helps.
Regards,
Adrien
Re: Michel Cadot's on Mette's modified DirList
September 10, 2009 - 1pm Central time zone
Reviewer: Doug Hersh from Charlestown MA US
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."
For the /tmp directory in Tom's example, this is accomplished by executing:
exec dbms_java.grant_permission ('OPS$TKYTE', 'SYS:java.io.FilePermission', '/tmp/*', 'read' )
The asterisk provides access to all the files in the directory. Without this privilege (i.e.
without the asterisk), for example:
exec dbms_java.grant_permission ( 'OPS$TKYTE', 'SYS:java.io.FilePermission',
'/tmp', 'read' )
a call to the modified DirList would produces an error message like this one:
-----
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:
the Permission (java.io.FilePermission '/tmp/somefilename' read) has not been granted to EQLOAD.
The PL/SQL to grant this is dbms_java.grant_permission( 'OPS$TKYTE',
'SYS:java.io.FilePermission', '/tmp/somefilename', 'read' )
-----
Also, on our Windows server (with RDBMS 9.2.0.8.0), I find that the drive letter is case-sensitive.
If a permission is created for 'C:\tmp\*' then input to DirList must be an upper-case "C". The
filename itself is case-insensitive.
Everything compiled correctly but no file shown
January 15, 2010 - 2pm Central time zone
Reviewer: Ligon from Canberra, ACT, Australia
Hi Tom,
I tried all the step you have explained correctly. All the objects created/compiled correctly and
the permissions are set as requested.
I then did exec get_dir_list( 'C:\Temp' );
and then select * from dir_list
but no results.
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
Followup January 18, 2010 - 5pm Central time zone:
perhaps c:\temp on the server is empty, you do know that c:\temp is on the DATABASE server right? Not your pc.
put some debug in there. You could insert a row or two of your own into the table to give yourself a message about what is happening.
Pipelined ??
January 19, 2010 - 12pm Central time zone
Reviewer: bc from macomb twp, mi
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 ?
Thanks
Followup January 19, 2010 - 5pm Central time zone:
the java code would not pipeline, plsql code can. the java code could return an object type (a collection) and that could be selected from though.
so you would change the code to return an object type (collection) and then you could select * from table(java_stored_procedure)
Pipelined ??
January 26, 2010 - 12pm Central time zone
Reviewer: bc from macomb twp, mi
Thanks Tom
|