Sinister Synonyms and Dependencies in Oracle

It’s been quite an eventful week. Deb has got her results back and is now officially a lady of “Distinction” (two of them, no less). Even Wales’ narrow defeat to England in the Rugby hasn’t put a dent in her good mood.
I, on the other hand, found myself doing my Marvin-the-paranoid-android-as-a-DBA impression the other day….”Synonyms. Loathe them or hate them, you can’t ignore them”.
Now, whilst synonyms definitely have their uses, they can be something of a double-edged sword.
The cause of this particular downbeat assessment of their merits was the fact that I’d deployed my CRUD tool on a new database, but it had failed to pick up some dependencies.
Let’s have a closer look at this issue and see how Oracle’s own DBMS_UTILITY copes with these circumstances.

A Test Case

OK, we need a table, a synonym on the table, and a database object that references the synonym…

create table shy ( retiring varchar2(1))
/
create synonym hidden for shy
/
CREATE OR REPLACE FUNCTION get_shy RETURN varchar2 AS
l_rtn hidden.retiring%TYPE;
BEGIN
  SELECT retiring INTO l_rtn
  FROM hidden;
  RETURN l_rtn;
END;
/

Now, let’s take a look….

SQL> select owner, name, type
  2  from dba_dependencies
  3  where referenced_owner = USER
  4  and referenced_name = 'SHY';

OWNER		     NAME		  TYPE
-------------------- -------------------- --------------------
MIKE		     HIDDEN		  SYNONYM

OK, so the synonym is there but the function isn’t. It does however have a dependency on the synonym according to DBA_DEPENDENCIES…

SQL> select owner, name, type
  2  from dba_dependencies
  3  where referenced_owner = USER
  4* and referenced_name = 'HIDDEN'
SQL> /

OWNER		     NAME		  TYPE
-------------------- -------------------- --------------------
MIKE		     GET_SHY		  FUNCTION

SQL> 

Nothing up my sleeve…

At this point, it’s probably worth mentioning DBMS_UTILITY.GET_DEPENDENCY, which does actually understand about synonyms…despite it’s own little quirks.
This procedure relies on DBMS_OUTPUT to return it’s findings ( none of that boring OUT parameter stuff, oh no)…

set serveroutput on size 1000000
BEGIN
    DBMS_UTILITY.GET_DEPENDENCY( 'TABLE' , USER, 'SHY');
END;
/
DEPENDENCIES ON MIKE.SHY
------------------------------------------------------------------
*TABLE MIKE.SHY()
*   SYNONYM MIKE.HIDDEN()
*      FUNCTION MIKE.GET_SHY()

PL/SQL procedure successfully completed.

SQL> 

That’s handy, we’ve now got our complete dependency hierarchy – i.e. that the function is dependent on the table via the synonym. However, we can’t dictate the output format so using it programatically is going to be a bit fiddly.
Furthermore, look what happens when the synoym is not owned by the object owner…

GRANT SELECT on shy TO hr
/
conn hr/pwd@xe

create synonym shy for mike.shy
/

CREATE OR REPLACE FUNCTION get_shy_hr RETURN varchar2 AS
l_rtn shy.retiring%TYPE;
BEGIN
  SELECT retiring INTO l_rtn
  FROM shy;
  RETURN l_rtn;
END;
/

Now when we ask DBMS_UTILITY.GET_DEPENDENCY…

set serveroutput on size 1000000
BEGIN
    DBMS_UTILITY.GET_DEPENDENCY( 'TABLE' , USER, 'SHY');
END;
/
DEPENDENCIES ON MIKE.SHY
------------------------------------------------------------------
*TABLE MIKE.SHY()
*   SYNONYM MIKE.HIDDEN()
*      FUNCTION MIKE.GET_SHY()

PL/SQL procedure successfully completed.

SQL> 

No, I haven’t just pasted in the wrong result set. It just doesn’t know about a synonym on this object if it’s in another schema.
Fortunately, there is another way…

SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_') as type, 
    NULL as synonym_name
FROM dba_dependencies dep
WHERE dep.type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE BODY', 'TRIGGER',
    'VIEW', 'MATERIALIZED VIEW')
AND dep.referenced_owner = USER
AND dep.referenced_name = 'SHY'
UNION
SELECT dep.owner, dep.name, REPLACE( dep.type, ' ', '_') as type,
    syn.synonym_name
FROM dba_dependencies dep, dba_synonyms syn
WHERE dep.referenced_owner = syn.owner
AND dep.referenced_name = syn.synonym_name
AND dep.referenced_type = 'SYNONYM'
AND syn.table_owner = USER
AND syn.table_name = 'SHY'
/

When we run this we get


OWNER		     NAME		  TYPE		       SYNONYM_NA
-------------------- -------------------- -------------------- ----------
HR		     GET_SHY_HR 	  FUNCTION	       SHY
MIKE		     GET_SHY		  FUNCTION	       HIDDEN

SQL> 

By plugging this query into the CRUD procedure everything is now working. I’ll post the complete updated code separately. In the meantime, it’s Saturday evening and I’ve promised someone that I’ll pretend I have a life.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s