We help IT Professionals succeed at work.

exception calling function in package

mkowales
mkowales asked
on
i am looking for help determining why i receive the following exception when trying to call the PA_SEARCH_NOTES() function that is in the PA_SEARCHING package:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'PA_SEARCH_NOTES' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:407)
at ...

this is the pl/sql:
PACKAGE Pa_Searching IS
TYPE tRowID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
FUNCTION Pa_Search_Notes(in_string IN VARCHAR2, in_search_type IN NUMBER, in_types IN VARCHAR2, out_rows OUT tRowID)
RETURN NUMBER;
END Pa_Searching;

this is the java code:
Connection con = new Connection( );
....
CallableStatement cs = con.prepareCall( "{ CALL PA_SEARCHING.PA_SEARCH_NOTES( ?, ?, ?, ? ) }" );
cs.setString( 1, bean.getPhrase( ) );
cs.setInt( 2, 1 );
cs.setString( 3, bean.getIssueTypeString( ) );
cs.registerOutParameter( 4, oracle.jdbc.OracleTypes.ARRAY, "TABLENUM" );
cs.execute( );

this is the oracle database information i'm using:

Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production
With the Partitioning option
JServer Release 8.1.6.2.0 - Production
using Oracle JDBC driver [ver. 8.1.7.0.0]
Comment
Watch Question

Just a guess:

Who is the owner of the procedure and have you connect to the db as the owner or as someone who can see and execute the procedure?

What happens when you try to excecute the procedure from the PL/SQL Developer, for example?
I suppose the package body is also defined - just want to be sure...
mkowales, have you seen which is the schema the procedure the package belongs to?
It would be better if you qualify the package name. For example, if the schema owner is "db_owner" you should call:
CALL db_owner.PA_SEARCHING.PA_SEARCH_NOTES(...)

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- Points for ia_ia_ia_1

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Venabili
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.