Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE PROCEDURE ERROR
ORACLE PROCEDURE ERROR [message #224360] Wed, 14 March 2007 01:34 Go to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Hi,
i have the following procedure

CREATE OR REPLACE PROCEDURE Conversion_To_Russian(OWN1 IN VARCHAR2,OWN2 IN VARCHAR2,OWN3 IN VARCHAR2,OWN4 IN VARCHAR2) IS

NO_TABLES_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT(NO_TABLES_FOUND , -942);
CURSOR convert_data IS SELECT 'alter table '||a.owner||'.'||a.table_name||' modify '||a.column_name||' '||
a.data_type||'('||a.data_length||' CHAR)' COL
FROM all_tab_columns a,all_tables b
WHERE data_type LIKE '%CHAR%' AND char_used='B' AND a.owner IN (OWN1,OWN2,OWN3,OWN4) AND a.TABLE_NAME=
b.TABLE_NAME ORDER BY a.table_name;
BEGIN
FOR A IN CONVERT_DATA
LOOP
EXECUTE IMMEDIATE A.COL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('End of procedure Conversion_To_Russian');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'NO TABLES TO BE CONVERTED INTO RUSSIAN');
WHEN NO_TABLES_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No tables found for converting to multilingual');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,SQLCODE||SQLERRM);


END Conversion_To_Russian ;
/

I am executing above procedure by belwo statement

exec CONVERSION_TO_RUSSIAN('TESTUTF','IMS','SCY','GEM');
It is showing me error
*
Quote:
ERROR at line 1:
ORA-06550: line 1, column 29:
PLS-00201: identifier 'TESTUTF' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



Kindly tell me where I am wrong
Re: ORACLE PROCEDURE ERROR [message #224426 is a reply to message #224360] Wed, 14 March 2007 04:27 Go to previous message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
If you could have print the value a.col
EXECUTE IMMEDIATE A.COL;
you can easily track where the problem is.

By
Vamsi
Previous Topic: SQL-SPOOL
Next Topic: difference between oracle 8i and 9i
Goto Forum:
  


Current Time: Sun Dec 08 19:20:02 CST 2024