CODEWITHSUNDEEP
javasqloracle-databasejdbcplsql
Sas
Sas

Reputation: 2503

"SQLException: ORA-06550" when calling PL/SQL function from Java

I want to use Java to retrieve data from database and display it, so I created this PL/SQL function, which returns a cursor:

create or replace function std_getInfoFunc return types.cursortype 
as 
    my_cursor    types.cursorType; 
begin 
    open my_cursor FOR
    SELECT s.FirstName, s.LastName, s.Address, s.City, s.Province
        , s.PostalCode, c.CourseName
    FROM Students s, Courses c, StudentAndCourses cs
    Where s.StudentID = cs.StudentID
        AND c.CourseID = cs.CourseID;
    Return my_cursor;
end;

In my Java code, I call the function as follows:

try{
    CallableStatement cst=connection.prepareCall("{? = call std_getInfoFunc}");
    cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
    cst.execute();

    res=(ResultSet) cst.getObject(1);

    while(res.next()){
        System.out.println(res.getString(1));
    }
}
catch(SQLException e){
    e.printStackTrace();
}
finally{
    res.close();
    cst.close();
    conn.close();
}

The code produces the following exception:

 run:
  java.sql.SQLException: ORA-06550: line 1, column 13:
   PLS-00905: object SAS.STD_GETINFOFUNC is invalid
  ORA-06550: line 1, column 7:
  PL/SQL: Statement ignored

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
at        
  oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at DBControler.viewStdInfo(DBControler.java:95)
at Test_02.main(Test_02.java:18)

Why?

Upvotes: 3

Views: 23556

Answers (2)

KV Prajapati
KV Prajapati

Reputation: 94635

Possible causes of error are : (in the snippet - function)

1. The package "types" doesn't exists.

  create or replace package types 
    as 
        type cursorType is ref cursor; 
    end; 
    /

2 Invalid SQL select statement (Table or column has been dropped or altered after creating the function).

You need to write a new function with simple sql statement and run it at Sql prompt and with Java code.

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89661

Did the function get created without error? If the type or any tables are missing, the function will be created, but the body of the function will be invalid because it cannot be compiled.

If the function was created without error, and then an object upon which it depends is dropped or altered, the function will be marked invalid and may be recompiled, but then may fail if it cannot be compiled (missing table or column or whatever).

Upvotes: 0

Related Questions