Home » SQL & PL/SQL » Client Tools » How to pass SQL "create DIRECTORY TMP_DIR as 'c:\courses\cs601'" in Java to Oracle?
How to pass SQL "create DIRECTORY TMP_DIR as 'c:\courses\cs601'" in Java to Oracle? [message #25786] Sun, 18 May 2003 20:34 Go to next message
Yue
Messages: 4
Registered: May 2003
Junior Member
Thanks Barbara for answering my question. It works perfectly. I really appreciate it.
Now I get another problem.
When I use Java to programming and I pass the SQL as "CREATE DIRECTORY tmp_dir AS 'c:coursescs601' " to server, I will get the error message as following:

Message: ORA-00900: invalid SQL statement
SQLState: 42000
ErrorCode: 900

And I try to run the same SQL query in SQL*PLUS, it work fine.
And when I use SQL "select directory_name from all_directories where directory_path='c:coursescs601' " , I encounter the same problem as above.

Would anyone help me out ?
Thanks you in advance.

Yue
Re: How to pass SQL "create DIRECTORY TMP_DIR as 'c:\courses\cs601'" in Java to Oracle? [message #25811 is a reply to message #25786] Tue, 20 May 2003 05:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Please post your complete java code, so that I can copy it and test it. Without seeing the code, I can only guess that it might have to do with case sensitivity or the directory already existing, or embedded quote marks, or insufficient permissions. Java is even more case sensitive than Oracle. It may be that, if you have already previously created the directory, that you need to use "CREATE OR REPLACE DIRECTORY tmp_dir AS 'c:coursescs601'". Or it may be that you need to use dynamic SQL, like EXECUTE IMMEDIATE. Or, bearing in mind that the directory objects are owned by SYS, it could be that your java does not have sufficient privileges to execute the command. These are just a few possibilities. If I can copy the code and test it on my system, it will probably be easier to debug the problem.
The problem is in calling procedure in Java [message #25831 is a reply to message #25811] Tue, 20 May 2003 13:09 Go to previous messageGo to next message
Yue
Messages: 4
Registered: May 2003
Junior Member
Thanks.

After I read your reply, I try my code . And I find the problem is not as my previous description . It is in the place when I call an oracle procedure. The following is my Java Code:

/* I had created a table PDFTable as following:
create table PDFTable(
directory_name varchar2(30) NOT NULL,
directory_path varchar2(30) NOT NULL,
table_name varchar2(30) NOT NULL,
file_name varchar2(30) NOT NULL,
source_bfile BFILE
)
*/

/* I had created a procedure as following:

create or replace procedure createPDFTable(
p_dirName varchar2,
p_dirPath varchar2,
p_tableName varchar2,
p_fileName varchar2
)
is
v_src BFILE;
v_dirPath varchar2(30);
begin
v_dirPath:=upper(p_dirPath);
v_src:=bfilename(v_dirPath, p_fileName);
insert into PDFTable(directory_name,directory_path,table_name,file_name,source_bfile)
values(p_dirName,v_dirPath,p_tableName,p_fileName,v_src);

end createPDFTable;
/
I try the following in SQL*PLUS , it works.
exec createPDFTable('TMP_DIR','c:coursescs601','emp','S5.pdf');

*/
import java.awt.*;
import javax.swing.*;
import javax.swing.event.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.table.AbstractTableModel;
import javax.swing.filechooser.*;
import java.io.File;
import com.adobe.acrobat.Viewer;
import java.io.InputStream;
import java.io.*;
import java.lang.*;

// Oracle Specific JDBC classes:
import oracle.sql.*;
//import oracle.jdbc.driver.*;
import oracle.jdbc.*;
//oracle recomment to use oracle.jdbc instead of oracle.jdbc.dirver

public class problem
{
private static String connString= "jdbc:oracle:thin:@host:1521:exerdb";
private static String strUser="scott";
private static String strPass="tiger";

public static void main(String args[[]]){

Statement stmt=null;
ResultSet rset;
String pathName="C:\tomcat_program\exer";
String dirName;
String opTable="emp";
String fileName="test.pdf";
String strSql="";

System.out.println(pathName); //it will print out: c:coursescs601
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(connString,strUser,strPass);
stmt = conn.createStatement();

dirName="TMP_DIR";
strSql="exec createPDFTable("+"'"+dirName+"',"+"'"+pathName+"',"+"'"+opTable+"',"+"'"+fileName+"'"+ ")";
System.out.println(strSql);
//it print out :exec createPDFTable('TMP_DIR','C:tomcat_programexer','emp','test.pdf')
//in SQL*PLUS , it works.
stmt.execute(strSql);
JOptionPane.showMessageDialog(new JFrame()," Table "+opTable+" associate PDF created successfully");
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
catch(SQLException ex) {
String errMessage="";
System.out.println("n--- SQLException caught ---n");
while (ex != null) {
errMessage=errMessage+" "+ex.getMessage();
System.out.println("Message: "+ ex.getMessage ());
System.out.println("SQLState: " + ex.getSQLState ());
System.out.println("ErrorCode: " + ex.getErrorCode ());
ex = ex.getNextException();
System.out.println("");
/* Error Message as following:
--- SQLException caught ---

Message: ORA-00900: invalid SQL statement

SQLState: 42000
ErrorCode: 900

*/
}//while
JOptionPane.showMessageDialog(new JFrame(),strSql+"\n"+errMessage);

}//catch

}//main

}

Thank you so much. I really appreciate it.
Yue
Re: The problem is in calling procedure in Java [message #25966 is a reply to message #25831] Wed, 28 May 2003 17:15 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
SQL> -- I believe you need to place your call to the createPDFTable between BEGIN and END,
SQL> -- instead of using EXEC, when creating your strSql.
SQL> -- I also believe you need to use a callable statement and doLoad.
SQL> -- When testing, it also helps if you use the same parameters in SQL*Plus and Java.
SQL> -- Please see the example below.  I did not test your connection and exceptions.
SQL> --
SQL> CREATE TABLE PDFTable
  2    (directory_name VARCHAR2 (30) NOT NULL,
  3  	directory_path VARCHAR2 (30) NOT NULL,
  4  	table_name     VARCHAR2 (30) NOT NULL,
  5  	file_name      VARCHAR2 (30) NOT NULL,
  6  	source_bfile   BFILE)
  7  /

Table created.

SQL> CREATE OR REPLACE PROCEDURE createPDFTable
  2    (p_dirName   IN VARCHAR2,
  3  	p_dirPath   IN VARCHAR2,
  4  	p_tableName IN VARCHAR2,
  5  	p_fileName  IN VARCHAR2)
  6  AS
  7    v_src	       BFILE;
  8    v_dirPath       VARCHAR2 (30);
  9  BEGIN
 10    v_dirPath := UPPER (p_dirPath);
 11    v_src	 := BFILENAME (v_dirPath, p_fileName);
 12    INSERT INTO PDFTable
 13  	 (directory_name, directory_path, table_name, file_name, source_bfile)
 14    VALUES
 15  	 (p_dirName, v_dirPath, p_tableName, p_fileName, v_src);
 16  END createPDFTable;
 17  /

Procedure created.

SQL> SHOW ERRORS
No errors.
SQL> create or replace and compile java source named problem as
  2  import java.awt.*;
  3  import javax.swing.*;
  4  import javax.swing.event.*;
  5  import java.awt.event.*;
  6  import java.sql.*;
  7  import javax.swing.table.AbstractTableModel;
  8  import javax.swing.filechooser.*;
  9  import java.io.File;
 10  import java.io.InputStream;
 11  import java.io.*;
 12  import java.lang.*;
 13  import oracle.sql.*;
 14  import oracle.jdbc.*;
 15  public class problem
 16  {
 17  	public static void doLoad() throws Exception
 18  	{
 19  	   try
 20  	   {
 21  	      Connection conn = null;
 22  	      Class.forName("oracle.jdbc.driver.OracleDriver");
 23  	      conn = new OracleDriver().defaultConnection();
 24  
 25  	      String dirName  = "TMP_DIR";
 26  	      String pathName = "C:\courses\cs601";
 27  	      String opTable  = "emp";
 28  	      String fileName = "S5.pdf";
 29  	      System.out.println(pathName);
 30  	      String strSql   = "BEGIN createPDFTable ("
 31  			      + "'" + dirName  + "',"
 32  			      + "'" + pathName + "',"
 33  			      + "'" + opTable  + "',"
 34  			      + "'" + fileName + "'"
 35  			      + "); END;";
 36  	      System.out.println (strSql);
 37  
 38  	      CallableStatement stmt = conn.prepareCall(strSql);
 39  	      boolean xx = stmt.execute();
 40  	      stmt.close();
 41  	   }
 42  	   catch (ClassNotFoundException e)
 43  	   {
 44  	      e.printStackTrace();
 45  	      System.err.print("ClassNotFoundException: ");
 46  	      System.err.println(e.getMessage());
 47  	   }
 48  	   catch (SQLException ex)
 49  	   {
 50  	      String errMessage="";
 51  	      System.out.println("n--- SQLException caught ---n");
 52  	      while (ex != null)
 53  	      {
 54  		 errMessage=errMessage+" "+ex.getMessage();
 55  		 System.out.println("Message: "+ ex.getMessage ());
 56  		 System.out.println("SQLState: " + ex.getSQLState ());
 57  		 System.out.println("ErrorCode: " + ex.getErrorCode ());
 58  		 ex = ex.getNextException();
 59  		 System.out.println("");
 60  	      } // end while
 61  	   }
 62  	   catch (Exception e)
 63  	   {
 64  	      e.printStackTrace();
 65  	   } // end catch
 66  	} // end doLoad
 67  } // end problem class
 68  /

Java created.

SQL> SHOW ERRORS
No errors.
SQL> CREATE OR REPLACE PROCEDURE test_problem
  2  AS
  3    language java name 'problem.doLoad() ';
  4  /

Procedure created.

SQL> SHOW ERRORS
No errors.
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_JAVA.SET_OUTPUT (1000000)

PL/SQL procedure successfully completed.

SQL> EXEC test_problem
C:coursescs601                                                                
BEGIN createPDFTable ('TMP_DIR','C:coursescs601','emp','S5.pdf'); END;        

PL/SQL procedure successfully completed.

SQL> SELECT COUNT (*) FROM PDFTable
  2  /

  COUNT(*)                                                                      
----------                                                                      
         1                                                      
Previous Topic: BIND VARIABLE..URGENT
Next Topic: Slow about join
Goto Forum:
  


Current Time: Tue Aug 04 14:34:20 CDT 2020