1

Executing the procedure, isb.pkg_po_test.get_component, I have to get a cursor from remote database.

Our schema is ISBREPORTER and we have limited access to the schema ibs. However, on Oracle SQL Plus the following is executed very well:

SQL> var r refcursor
SQL> exec isb.pkg_po_test.get_component(15048,6,1,1,to_date('17.12.2017'), :r);
SQL> print;

Printed result:

    ID PRODUCT_TYPE_ID COMPONENT_ID COMPONENT_NAME                          

     1               1      1012161 some text_1                      
     2               1      1012160 some text_2        
     3               1      1012158 some text_3                               
     4               1        10078 some text_4              
     5               1        10078 some text_5                
     6               1        10040 some text_6                       
     7               1      1012149 some text_7

When I try to call it from java code I get this exception:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'ISB.PKG_PO_TEST must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored         

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:210)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:53)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5617)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:197)

Here is how I am calling it from java:

public void printCursor(PrintWriter out) {
        Connection ktConnection;
        try {
            ktConnection = ConnectionPool.getConnectionKT();
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }

        String statement = "{CALL isb.pkg_po_test.get_component(?,?,?,?,?,?)}";
        try {
            CallableStatement call = ktConnection.prepareCall(statement);
            call.setFloat(1, 15048);
            call.setFloat(2, 6);
            call.setFloat(3, 1);
            call.setInt(4, 1);
            call.setDate(5, Date.valueOf("2017-12-17"));
            call.registerOutParameter(6, OracleTypes.CURSOR);

            call.execute();

            ResultSet rs = (ResultSet) call.getObject(6);

            while (rs.next()) {
                String column = rs.getString("COMPONENT_NAME");
                out.print(column); //to test if it is working
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

Is it a correct way to execute stored procedure from other schema? If not, could you tell me how to handle it properly?

I have read through other similar questions which were not helpful. Here are some of them:

Improve this question
2
  • 2
    Are you sure you're connect as the same user (and to the same DB!) from your Java connection pool and through SQL*Plus? Commented Dec 28, 2017 at 9:58
  • You were right. In the connection pool I had been using wrong datasource. Thank you so much! Commented Dec 28, 2017 at 10:16
Related questions
3
Calling Oracle stored procedure from Java
1
Having trouble calling Oracle stored procedure in java
1
error calling stored procedure from java through callable statement
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.