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

Question

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?

The eclipse shows this error: 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 — Sas
– Sas, Commented Nov 26, 2011 at 4:02
If you look at the exception, you will see that it says GetInfoFunc is invalid. I recommend looking to see if this function exists, and if you are using it correctly — Adam Wenger
– Adam Wenger, Commented Nov 26, 2011 at 4:10
Yes I created the function before running it. and my function code is on the top of the post, and It seems correct. Please have a look at my function code. — Sas
– Sas, Commented Nov 26, 2011 at 4:10
@SasvathanSarvanandan - One of the table doesn't exist in current schema. Please check the name of tables you have written in the function. — KV Prajapati
– KV Prajapati, Commented Nov 26, 2011 at 4:16

KV Prajapati · Accepted Answer · 2011-11-26 05:23:30Z

2

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.

answered Nov 26, 2011 at 5:23

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sas Over a year ago

yes your right, the package was the problem. Thank you very much for you help.

Cade Roux · Accepted Answer · 2011-11-26 04:32:24Z

0

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).

answered Nov 26, 2011 at 4:32

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

2 Comments

Sas Over a year ago

I checked all the tables and columns, but It doesn't seems like any thing wrong in my table's structure.

Cade Roux Over a year ago

If you go into Toad, it will show you which objects are invalid and why.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related