Home » Other » 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 |
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 |
|
Barbara Boehmer
Messages: 9102 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 |
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 |
|
Barbara Boehmer
Messages: 9102 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
|
|
|
Goto Forum:
Current Time: Sat Jan 18 11:07:09 CST 2025
|