Bonjour,
Sous Oracle 10g, j'ai défini une sequence SEQ_SYS_CAP sous le compte ops$ope, puis un synonym par :
1 2 3 4 5 6 7 8
| SQL>connect IPAS1/<password>@BASE
Connected
SQL>create synonym SEQ_SYS_CAP for ops$ope.SEQ_SYS_CAP;
Synonym created.
SQL>select owner,object_name from all_objects where object_name like 'SEQ%';
OWNER OBJECT_NAME
IPAS1 SEQ_SYS_CAP
OPS$OPE SEQ_SYS_CAP |
j'ai créé 2 procédures pour supprimer les synonyms :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| create or replace procedure dropsynonym(synonym varchar2) as
cursor1 integer;
begin
cursor1:=dbms_sql.open_cursor;
dbms_sql.parse(cursor1,'DROP SYNONYM ' || synonym,dbms_sql.NATIVE);
dbms_sql.close_cursor(cursor1);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
if dbms_sql.is_open(cursor1) then
dbms_sql.close_cursor(cursor1);
end if;
end;
/
create or replace procedure dropsynonymp(synonym varchar2) as
cursor1 integer;
begin
cursor1:=dbms_sql.open_cursor;
dbms_sql.parse(cursor1,'DROP PUBLIC SYNONYM ' || synonym,dbms_sql.NATIVE);
dbms_sql.close_cursor(cursor1);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
if dbms_sql.is_open(cursor1) then
dbms_sql.close_cursor(cursor1);
end if;
end;
/ |
Je tente de supprimer le synonyme nouvellement créé :
1 2 3 4
| SQL>execute dropsynonym('SEQ_SYS_CAP');
PL/SQL procedure successfully completed.
SQL>execute dropsynonymp('SEQ_SYS_CAP');
PL/SQL procedure successfully completed. |
Mais mon synonyme est toujours là :
1 2 3 4
| SQL>select owner,object_name from all_objects where object_name like 'SEQ%';
OWNER OBJECT_NAME
IPAS1 SEQ_SYS_CAP
OPS$OPE SEQ_SYS_CAP |
Par contre directement avec un
drop synonym IPAS1.SEQ_SYS_CAP
le synonym est bien supprimé.
Merci de votre aide
Partager