posted 17 years ago
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;