CODEWITHSUNDEEP
javasqloracle-database
user1344022
user1344022

Reputation: 193

java.sql.SQLException: ORA-06550: after calling procedure from java code

Hi I have problem with calling store procedure. when i am trying to call my procedure from my following java code

connection = ConnectionManager.getInstance().getConnection(dataBaseURL, serverName, portNumber, sid, username, password);

callable = connection.prepareCall("{call SCHEMA_RESTORE.restore()}");

callable.executeUpdate();
..................................

I am getting this Exception

Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7:

PLS-00201: identifier 'SCHEMA_RESTORE.RESTORE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)

My Prodedure is 

CREATE OR REPLACE PACKAGE BODY SCHEMA_RESTORE IS 
  PROCEDURE backup (pbRecreateBackupTable IN BOOLEAN DEFAULT FALSE)
    IS
      TableAlreadyExists EXCEPTION;
      PRAGMA EXCEPTION_INIT (TableAlreadyExists, -955);
      nCount NUMBER;
      CURSOR cTables IS SELECT tbls.table_name tbl, SUBSTR(tbls.table_name,4) name FROM user_tables tbls, FT_T_TBDF tbdf
        WHERE tbls.table_name NOT LIKE gsPrefix || '%' AND tbls.table_name NOT LIKE 'FT_CFG%' AND tbls.table_name NOT IN ('FT_WF_APEV','FT_WF_WFTJ','FT_WF_WFTN','FT_WF_WFNP','FT_WF_WFNV','FT_WF_WFIO','FT_WF_WFGV','FT_WF_WFND','FT_WF_WFDF','EXCEPTIONS','TESTDSFP') and tbls.table_name NOT LIKE 'FT_LOG%'
          AND tbdf.tbl_id(+) = SUBSTR(tbls.table_name,-4) AND tbdf.tbl_desc NOT LIKE '%Note: This table is obsolete%';
BEGIN

        RAISE;
    END;
  END LOOP; 
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END backup;

Upvotes: 3

Views: 57796

Answers (3)

ArockiaRaj
ArockiaRaj

Reputation: 608

It comes when you make a procedure in one user and connect the db in another. To solve this issue try this 

GRANT EXECUTE ON procedureName TO username

Otherwise connect with correct username which you using

Upvotes: 4

Nrj
Nrj

Reputation: 6831

Did the procedure compile successfully ? Also check if you can see it via some editor (PL/SQL dev ?) under the schema

or you may use this to see if it is there -

SELECT * FROM DBA_OBJECTS WHERE object_name = '%your proc name%';

Upvotes: 1

Codo
Codo

Reputation: 78835

The error message says that SCHEMA_RESTORE.RESTORE cannot be found. There are several possible causes:

  • The package (and procedure) are in a different schema, e.g. you compiled it as user A but are trying to call them as user B.
  • You don't have the access right to execute procedures from the package. Thus it becomes invisible.
  • You have defined the procedure in the package body, but haven't declared it in the package header.

A further problem I can see are the parentheses. If you call a procedure without arguments, the prentheses shoul be omitted:

{ call SCHEMA_RESTORE.restore }

Upvotes: 4

Related Questions