What is wrong with this procedure?

[v ray]

Hi guys,
I have run into this problem, hope someone can see something I dont see here! I feel everything is fine, but obviously, if it was, it would be working! HELP!

1. call to the procedure from the code is like this:

   
   CallableStatement truncateStmt = conn.prepareCall("{call TRUNCATE_BATCH_IDS()}");
   truncateStmt.execute();
   
   

2. The error thrown is:

INFO: ***** Excecuting SQL*Loader INSERT for table BATCH_IDS
07/07/12 14:39:01 java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRUNCATE_BATCH_IDS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at oracle.jdbc.driver.DatabaseError.throwSqlException(Da tabaseError.java:137)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.j ava:304)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.j ava:271)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:625 )
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCa llableStatement.java:180)
at oracle.jdbc.driver.T4CCallableStatement.execute_for_r ows(T4CCallableStatement.java:869)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeo ut(OracleStatement.java:1153)
at oracle.jdbc.driver.OraclePreparedStatement.executeInt ernal(OraclePreparedStatement.java:2932)
at oracle.jdbc.driver.OraclePreparedStatement.execute(Or aclePreparedStatement.java:3023)
at oracle.jdbc.driver.OracleCallableStatement.execute(Or acleCallableStatement.java:4132)

And so on, and then it points to the "truncateStmt.execute();" line.

3. The code for the procedure is:

[code]

CREATE OR REPLACE
PROCEDURE truncate_batch_ids IS
str_sql varchar2(200);
str_table_name varchar2(30);

cursor c_table_names IS
select table_name
from user_tables
where table_name like 'BATCH_IDS';

BEGIN
FOR c_table_names_rec IN c_table_names
LOOP
str_table_name := c_table_names_rec.table_name;
str_sql := 'truncate table '||str_table_name;
EXECUTE IMMEDIATE str_sql;
END LOOP;
END truncate_batch_ids;

[Anupam Bhatt]

I am not sure, if it does have a effect, but did you try the procedure name in small letters and not caps, when calling from java? just a thought !

[v ray]

No, the same procedure works elsewhere on another environment, with the same code!!! Thats what's driving me crazy!
You think it has something to do with compiling the procedure, binding the procedure etc??

[Paul Sturrock]

Check who you log into your database as. The procedure might belong to a different schema than the one you use.

[v ray]

Originally posted by Paul Sturrock:
Check who you log into your database as. The procedure might belong to a different schema than the one you use.

How is that possible? The error occurs when the procedure name is being read by the program, so obviously, it cant be a problem with the procedure belonging to a different schema I think.

[Paul Sturrock]

How is that possible? The error occurs when the procedure name is being read by the program, so obviously, it cant be a problem with the procedure belonging to a different schema I think.

It can if the user the program logs in as has no visibility of the procedure. The error it telling you that the database doesn't know what TRUNCATE_BATCH_IDS is. Remember that if you logged in to one schema to create the procedure, then run the program in another you will need to refer to the procedure using the schema name as well as the prcedure name (unless you use a public synonym).

[v ray]

Originally posted by Paul Sturrock:

It can if the user the program logs in as has no visibility of the procedure. The error it telling you that the database doesn't know what TRUNCATE_BATCH_IDS is. Remember that if you logged in to one schema to create the procedure, then run the program in another you will need to refer to the procedure using the schema name as well as the prcedure name (unless you use a public synonym).

You are soo right! The same procedure exists in another schema and thats where I was checking, but this procedure is called in another schema and thats where the procedure needed to exist. So I listed all the procedures in the production environment, and I noticed the missing procedure and created and compiled and now its all fine .
Thanks for the help everyone!