CODEWITHSUNDEEP
javaoracle-databasestored-proceduresjakarta-eejdbc
sreeroop
sreeroop

Reputation: 64

How to check Oracle procedure is created correctly by using java?

Am making a simple tool (using) that executes Oracle script from a text file. it's working fine, but while executing create procedure or trigger if an error occurs by oracle, java not throwing any exception, and in the same scenario, any table creation or execution of select/insert/ delete it throwing the exception with error .so my question is how to catch the error while executing procedure/trigger creating script? thank you.

Example: this function (it may be a procedure, trigger )I need to create Oracle. 

create or replace function fn_get_contract_invoice_amt(I_CompanyId varchar2, 
I_ContractNo Varchar2, I_Date1 Date, I_Date2 Date)
return number is
V_Amount    Number(13,3);
begin
 begin
  Select sum(nvl(Amount,0)) into v_Amount
  From Invmast im
  Where Company_id = I_CompanyId
  and   Contract_No = I_ContractNo
  and   Tran_Date between I_Date1 and I_Date2
  and   Deleted = 'N'
  and   Posted = 'Y';
 Exception
When No_Data_Found Then
  V_Amount := 0;
End;
  Return nvl(V_Amount,0);
end;

Java Code to execute script

private static String runSqlStatement(String script) {
    String status = "";
    System.out.println(script);

    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        stmt.execute(script);

    } catch (SQLException ex) {
        status = ex.getMessage();
        System.err.println("SQLException: " + ex.getMessage());
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                System.err.println("SQLException: " + e.getMessage());
            }
        }

    }
    return status;
}

Upvotes: 0

Views: 515

Answers (1)

TenG
TenG

Reputation: 4004

I don't think Oracle treats compilation errors when creating procedures as actual errors per say.

If you test this with sqlplus and put a WHENEVER SQLERROR EXIT FAILURE ROLLBACK then run a failed CREATE OR REPLACE you will see that only errors reported via the standard ORA-nnnnn type message is caught. The create with compliation errors result is not caught as a error.

The reason is because even it it is created with compilation errors it is still created.

The only way for you in your use case to check if the CREATE OR REPLACE succeeded is, perhaps as follows:

  1. Execute the CREATE or REPLACE statement
  2. Check DBA_OBJECTS (or ALL_OBJECTS or USER_OBJECTS as appropriate) :

SELECT STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME = 'OBject_name' AND OBJECT_TYPE = 'PACKAGE|PACKAGE BODY|PROCEDURE...' AND OWNER = 'the_owner'

If this returns VALID then it worked and compiled cleanly, if INVALID then query DBA_ERRORS :

SELECT * FROM DBA_ERRORS WHERE NAME = 'Object_name' AND TYPE = '....' AND OWNER = 'the_owner'

Upvotes: 3

Related Questions