aspose file tools*
The moose likes JDBC and the fly likes What is wrong with this procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What is wrong with this procedure?" Watch "What is wrong with this procedure?" New topic
Author

What is wrong with this procedure?

v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
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:



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
Ranch Hand

Joined: Mar 12, 2004
Posts: 81
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
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
v ray
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Ranch Hand

Joined: Mar 15, 2007
Posts: 223
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!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What is wrong with this procedure?