• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

What is wrong with this procedure?

 
v ray
Ranch Hand
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 81
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Check who you log into your database as. The procedure might belong to a different schema than the one you use.
 
v ray
Ranch Hand
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 223
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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!
 
sandeeprajsingh tandon
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Old thread..
But still..
What i found was that i got errors of 'Function Name' must be declared in hibernate. I was running it via a Spring configured junittest.
I checked that my test classes werent taking connections strings to the right jdbc url...(A little foolish of me, but a solution none the less:))
 
reply
    Bookmark Topic Watch Topic
  • New Topic