error message while dropping synonyms

[rohanem]

Hi all,
I have written a PL/SQL which will first check the names of the synonyms created in my schema,which have the same name as that of the tables in an another schema and drop them using a "EXECUTE IMMEDIATE" DDL statement.
If I do it explicitly,it does not show any errors and does the job.
Using it in a PL/SQL shows the following error:

ERROR: ORA-00995: missing or invalid synonym identifier--995

 

[karluk]

Sorry, but without knowing exactly what statement is being executed, the natural suspicion is that the error means exactly what it says - that you've somehow constructed a "drop synonym" statement that has an invalid synonym name.

Can you provide the statement that's being dynamically executed when you get the ORA-00995 error?

 

[rohanem]

Here is the PL/SQL..

CREATE OR REPLACE PROCEDURE STP_CREATE_SYN(
A_OBJTYP IN VARCHAR2,
A_OWNER IN VARCHAR2)
AUTHID CURRENT_USER
AS
CURSOR CUR_SNR_OBJ IS
SELECT OBJECT_NAME FROM ALL_OBJECTS
WHERE
OBJECT_TYPE = UPPER(A_OBJTYP)
AND
OWNER = UPPER(A_OWNER)
ORDER BY OBJECT_NAME;
CURSOR CUR_SNR_SYN IS
SELECT A.SYNONYM_NAME FROM USER_SYNONYMS A,ALL_OBJECTS B
WHERE A.SYNONYM_NAME = B.OBJECT_NAME
AND B.OBJECT_TYPE = UPPER(A_OBJTYP)
ORDER BY SYNONYM_NAME;
V_OBJ_NM ALL_OBJECTS.OBJECT_NAME%TYPE;
V_SYN_NM USER_SYNONYMS.SYNONYM_NAME%TYPE;
V_DDL VARCHAR2(1000);
V_SQLMSG VARCHAR2(2000) := null;
V_SQLCODE VARCHAR2(2000) := null;
BEGIN
OPEN cur_snr_syn;
DBMS_OUTPUT.PUT_LINE('Dropping Synoyms for the Following '||A_OBJTYP||'(s) ...');
LOOP
FETCH CUR_SNR_SYN INTO V_SYN_NM;
EXIT WHEN CUR_SNR_SYN%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(UPPER(A_OBJTYP)||' NAME:'||V_OBJ_NM);
V_DDL := 'DROP SYNONYM '|| V_SYN_NM;
DBMS_OUTPUT.PUT_LINE('DDL STMT: '||V_DDL);
EXECUTE IMMEDIATE 'DROP SYNONYM :v1'
USING V_SYN_NM;
END LOOP;
CLOSE CUR_SNR_SYN;
DBMS_OUTPUT.PUT_LINE('Creating Synoyms for the Following '||A_OBJTYP||'(s) ...');
OPEN cur_snr_obj;
LOOP
FETCH CUR_SNR_OBJ INTO V_OBJ_NM;
EXIT WHEN CUR_SNR_OBJ%NOTFOUND;
V_DDL := 'CREATE SYNONYM '|| V_OBJ_NM ||' FOR '|| A_OWNER ||'.'||V_OBJ_NM ||';';
DBMS_OUTPUT.PUT_LINE(UPPER(A_OBJTYP)||' NAME:'||V_OBJ_NM);
DBMS_OUTPUT.PUT_LINE('DDL STMT: '||V_DDL);
EXECUTE IMMEDIATE 'CREATE SYNONYM :V1 FOR :V2'||'.'||V_OBJ_NM||' ;'
USING V_OBJ_NM,UPPER(A_OWNER);
END LOOP;
CLOSE CUR_SNR_OBJ;
EXCEPTION
WHEN OTHERS THEN
V_SQLMSG := SQLERRM;
V_SQLCODE := SQLCODE;
DBMS_OUTPUT.PUT_LINE('ERROR: '||V_SQLMSG||'-'||V_SQLCODE);
END STP_CREATE_SYN;
/

 

[ThomVF]

I think your problem is that you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

EXECUTE IMMEDIATE 'DROP SYNONYM :v1'
USING V_SYN_NM;

You shuold do this instead:

EXECUTE IMMEDIATE 'DROP SYNONYM '|| V_SYN_NM;

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

Right you are, ThomVF. The relevant excerpt from the Oracle SQL reference manual is

Passing the Names of Schema Objects
Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. Using dynamic SQL, you might write the following stand-alone procedure:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;
END;


However, at run time, this procedure fails with an invalid table name error. That is because you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. Instead, you must embed parameters in the dynamic string, then pass the names of schema objects to those parameters.

To debug the last example, you must revise the EXECUTE IMMEDIATE statement. Instead of using a placeholder and bind argument, you embed parameter table_name in the dynamic string, as follows:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;


Now, you can pass the name of any database table to the dynamic SQL statement.

 

[rohanem]

Yeah!!..I think that did solve the problem 2 an extent..But now I am getting this error

Dropping Synoyms for the Following table(s) ...
Creating Synoyms for the Following table(s) ...
TABLE NAME:ACES_ODR
DDL STMT: CREATE SYNONYM ACES_ODR FOR mssowner.ACES_ODR;
ERROR: ORA-00911: invalid character--911

dont know whats wrong now

 

[karluk]

The ORA-00911 is probably caused by the semicolon in your create synonym statement. You shouldn't have a command separator when you are executing only one statement.

However, I don't see how you expect the create synonym loop to do anything. You want to query the catalog to find what synonyms to create, but you've already dropped them, so you won't find any synonyms in the catalog.

 

[rohanem]

Karluk,
the synonyms are dropped by the 1st cursor..the second cursor will just create new synonyms with the same name as that of the table_name..I dont c any problem with that.

 

[karluk]

I guess you're right. I was thinking that A_OBJTYP was set to "SYNONYM", but I see it's set to "TABLE". You're trying to create a synonym for every table in the schema.

 

[rohanem]

Thats right..problem is resolved...thanks 2 u and ThomVF