As part of my java application, I have to create oracle packages from java code. Sometimes, the package code might have issues and compilation might fail. However, I am unable to capture simple failures from java. So, i have to compile > 350 oracle packages from java, and if there are errors in the package, I need to inform the user to fix them. I have pasted oracle package and java code below.
CREATE OR REPLACE PACKAGE plat_test IS
FUNCTION getmsg (
p_empno IN NUMBER
) RETURN VARCHAR2;
END plat_test;
/
CREATE OR REPLACE PACKAGE BODY plat_test AS
FUNCTION getmsg (
p_empno IN NUMBER
) RETURN VARCHAR2 IS
BEG
RETURN 'sss';
END getmsg;
END plat_teest;
Compiling / running the above code in sql developer throws:
LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00201: identifier 'PLAT_TEEST' must be declared
1/14 PLS-00304: cannot compile body of 'PLAT_TEEST' without its specification
Errors: check compiler log
I want to create the above package in java and get the result. So that I can inform the user on the failure. In java I am not able to capture the errors and the program always succeeds.
How can I capture the output in java
The java code I have:
import java.sql.*;
public class NewJDBCTester {
public static void one() {
String s_sql = "CREATE OR REPLACE PACKAGE BODY plat_test AS\n" +
" FUNCTION getmsg (\n" +
" p_empno IN NUMBER\n" +
" ) RETURN VARCHAR IS\n" +
" BEG" +
" RETURN 'ret_val';\n" +
" END getmsg;\n" +
"\n" +
"END plat_test\n" +
"/";
// String s_sql ="alter table Metric_idf from ssssssss_ssst";
// System.out.println(" SQL Stmt: " + sql);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement stmt = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:bhasoor/[email protected]:34171/ssssssssssdb");
stmt = conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
try {
stmt.execute (s_sql);
System.out.println(" SQL Executed Successfully ");
} catch (SQLException sqe) {
System.out.println("Error Code = " + sqe.getErrorCode());
// sqe.
System.out.println("SQL state = " + sqe.getSQLState());
System.out.println("Message = " + sqe.getMessage());
System.out.println("printTrace /n");
sqe.printStackTrace();
}
}
public static void main(String[] args) throws ClassNotFoundException {
one();
}
}
So finally I acheived this like this. Execute the create or replace package then get the errors from dba_errors...
Posting code here...
import java.sql.*;
public class NewJDBCTester {
public static void one() throws ClassNotFoundException {
String s_sql = "CREATE OR REPLACE PACKAGE BODY plat_test AS\n" +
" FUNCTION getmsg (\n" +
" p_empno IN NUMBER\n" +
" ) RETURN VARCHAR2 IS\n" +
" BEGIN\n" +
// " RETURN 'sss';\n" +
" END getmsg;\n" +
"END plat_test;\n";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:user/[email protected]:34171/somedb");
stmt = conn.createStatement();
System.out.println("EXECUTING QUERY");
rset = stmt.executeQuery(s_sql);
rset.close();
stmt.close();
stmt = conn.createStatement();
rset = stmt.executeQuery("SELECT line, text FROM DBA_ERRORS WHERE OWNER = 'Bhasoor' AND NAME = 'PLAT_TEST' ORDER BY SEQUENCE desc");
while (rset.next()) {
System.out.println("ERROR AT LINE ::: " + rset.getString("line"));
System.out.println("\n");
System.out.println("ERROR DETAILS::: " + rset.getString("text"));
}
} catch (SQLException sqe) {
System.out.println("Error Code = " + sqe.getErrorCode());
System.out.println("SQL state = " + sqe.getSQLState());
System.out.println("Message = " + sqe.getMessage());
System.out.println("printTrace \n");
sqe.printStackTrace();
}
}
public static void main(String[] args) throws ClassNotFoundException {
one();
}
}