Home » Developer & Programmer » JDeveloper, Java & XML » SQL using Java (oracle 10g)
SQL using Java [message #332584] Wed, 09 July 2008 01:36 Go to next message
amit.navapara
Messages: 5
Registered: December 2007
Location: Bangalore
Junior Member

I want to get list of file from perticular directory, for this i am using the following

java code....

the resulting filenames i want to store somewhere.

when i am inserting this data into table in the java code itself its working fine.....

as this 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;
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:element) };
}
}

}

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

this is working fine..................

but i dont want to use any table because in bulk records the DDL will b performance problem

so i have tried the following 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)

{

File path = new File( directory );
String[] list = path.list();
String element;

for(int i = 0; i < list.length; i++)
{

element = ( String ) list;
#sql {dmstat_request.filelist.extend};
#sql {dmstat_request.filelist := :element};
}

}

}


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

here dmstat_request is my package

and filelist is a collection type variable or array..

this is giving me error as follows......

LINE/COL ERROR
-------- -----------------------------------------------
0/0 DirList:18: Unbalanced curly braces.
0/0 #sql {dmstat_request.filelist := :element};
0/0 ^
0/0 DirList:18: Illegal token '[' will be ignored.
0/0 #sql {dmstat_request.filelist := :element};
0/0 Info: 3 errors
0/0 DirList:18: Illegal token ']' will be ignored.
0/0 #sql {dmstat_request.filelist := :element};
0/0 ^
0/0 Sqlj error in source DirList :
0/0 ^



Thanks a lot..........
Re: SQL using Java [message #332588 is a reply to message #332584] Wed, 09 July 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Now you have to understand the code you got (from T. Kyte) BEFORE trying to modify it.
Of course, for this you have to a little bit on Java programming in Oracle.
Have a look at Java Developer's Guide.

Regards
Michel
Re: SQL using Java [message #332814 is a reply to message #332588] Wed, 09 July 2008 15:43 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I found this in some of my old saved stuff. I don't remember for sure, but I think the original was by Cameron O'Rourke when he had a site for Java stuff that used the same format as Tom Kyte's site. I once posted a link to the code on Tom Kyte's site, but that link is no longer valid. Also, I don't remember whether I made any modifications or not, so it may not be quite the same as the original.



SCOTT@orcl_11g> create or replace and compile java source named "DirList"
  2  as
  3  import java.io.File;
  4  import java.util.Date;
  5  import java.text.DateFormat;
  6  
  7  
  8  public class DirList  {
  9  
 10    public static void getDirList(String directory) {
 11  	   File dir = new File( directory );
 12  	   if (!dir.isDirectory()) {
 13  	     System.out.println("Path: " + directory + " is not a directory.");
 14  	     return;
 15  	   }
 16  	   System.out.print("\nDirectory " + dir + ": ");
 17  
 18  	   File[] dirList = dir.listFiles();
 19  	   if (!dir.canRead() || dirList == null) {
 20  	     System.out.println("unreadable");
 21  	     return;
 22  	   }
 23  
 24  	   if (dirList.length < 1) {
 25  	     System.out.println("empty");
 26  	     return;
 27  	   }
 28  	   System.out.println(dirList.length + " files");
 29  
 30  	   // Sort the filenames
 31  	   java.util.Arrays.sort(dirList);
 32  
 33  	   // Print out all of the files in this directory
 34  	   for(int i = 0; i < dirList.length; i++) {
 35  	       File file = dirList[i];
 36  
 37  	       // Print the file's modification date
 38  	       Date modDate = new Date(file.lastModified());
 39  	       DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT);
 40  	       System.out.print("\t" + df.format(modDate));
 41  
 42  	       // Print the file's length
 43  	       System.out.print("\t" + file.length());
 44  
 45  	       // Print the file name
 46  	       System.out.print("\t" + file.getName());
 47  	       // Print slash if File is a directory
 48  	       if (file.isDirectory()) System.out.print(File.separator);
 49  
 50  	       System.out.print("\n");
 51  	   }
 52  
 53  	   // Now recursively list all the directories
 54  	   for(int i = 0; i < dirList.length; i++) {
 55  	       File file = dirList[i];
 56  	       if (file.isDirectory()) {
 57  		 getDirList(file.getAbsolutePath());
 58  	       }
 59  	   }
 60    }
 61  
 62  
 63    public static void main(String[] args) {
 64  	 getDirList(args[0]);
 65    }
 66  }
 67  /

Java created.

SCOTT@orcl_11g> show err
No errors.
SCOTT@orcl_11g> create or replace procedure get_dir_list( p_directory in varchar2 )
  2  as language java
  3  name 'DirList.getDirList( java.lang.String )';
  4  /

Procedure created.

SCOTT@orcl_11g> exec dbms_java.set_output(100000);

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec get_dir_list( 'c:\temp' );
Directory c:\temp: 1 files
	3/28/08	6	PROBCK22.XML

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec get_dir_list( 'c:\test' );
Directory c:\test: 2 files
	10/30/07	3218	cats.sql
	12/8/07	3785	cats.txt

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

Previous Topic: Passing values to the Validation Rule error message
Next Topic: XML 'IN' list
Goto Forum:
  


Current Time: Fri Mar 29 10:37:52 CDT 2024