| Dynamic Execution [message #7404] |
Wed, 11 June 2003 16:05  |
MM
Messages: 27
Registered: July 2002
|
Junior Member |
|
|
Hi,
I am executing the following proc and it gives me a weird error. Any one can tell what wrong i'm doing.
Thanks in advance.
-------------------
CREATE OR REPLACE PROCEDURE cre_drop_synonyms IS
v_errmsg VARCHAR2(200);
v_module_name NPSPROV.NPS_PROCESS_ERRORS.MODULE_NAME%TYPE := 'CRE_DROP_SYNONYMS';
v_object_name VARCHAR2(30);
v_owner VARCHAR2(30);
v_synonym_name VARCHAR2(30);
CURSOR syn_minus_cur IS
SELECT object_name,
owner
FROM dba_objects
WHERE owner IN('NPSPROV','NPSTOP')
AND object_type IN('TABLE','PROCEDURE','FUNCTION','PACKAGE BODY','TRIGGER')
MINUS
SELECT synonym_name,
table_owner
FROM dba_synonyms
WHERE table_owner IN('NPSPROV','NPSTOP');
BEGIN
FOR syn_minus_rec IN syn_minus_cur LOOP
v_object_name := syn_minus_rec.OBJECT_NAME;
v_owner := syn_minus_rec.OWNER;
dbms_output.put_line(v_object_name);
dbms_output.put_line(v_owner);
EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM'|| v_object_name ||'FOR'|| v_object_name;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_errmsg := SUBSTR(SQLERRM, 1, 200);
END;
/
Run time Error mesg i'm getting is
ORA-00972: identifier is too long
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
