capture sql developer script output in java

Question

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();

    }
}

Answer 1

You can know if an error occurs by this way:

boolean result = stmt.execute(s_sql);
System.out.println(result ? " SQL Executed Successfully " : " SQL Executed with error ");

Then, the following query give you the error:

select * 
from ALL_ERRORS  
where owner = 'METRICSTREAM' 
and name = 'PLAT_TEST' 
--and type = 'PACKAGE BODY' 
order by sequence

Here, there is no SQLException because the compilation has been completed, but with some errors.

You should use try-with-resource like this to avoid memory leaks:

try ( //
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.100.1.61:34171/pdb", "metricstream", "password"); //
        Statement stmt = conn.createStatement(); //
) {
    boolean result = stmt.execute(s_sql);
    System.out.println(result ? " SQL Executed Successfully " : " SQL Executed with error ");
}
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();
}