DDevelopment & Design

How to find all objects that use dblinks

  • byradek
  • November 2, 2020
  • 1 minute read

First

Create a temporary table as data of dictionary view ALL_VIEWS and convert column TEXT (view text) to CLOB type.

CREATE TABLE tmp_views AS SELECT a.owner , a.view_name, to_lob(a.text) text FROM all_views a;

Second

Use regular expression to find definitions with dblinks (using a hierarchical query to display objects used in joins).

WITH view_def AS
(--search views use db_link with name "DB_LINK_NAME"
SELECT owner,
     view_name,
     text,
     upper(regexp_count(text, '[^ ](\w+\.)?(\$|\w)+@DB_LINK_NAME', 1, 'i')) call_cnt --this is count of remote objects in view definition. I need it to build hierarchy
FROM tmp_views t
WHERE t.owner IN ('DHP_KENAN_PROD') AND
     regexp_like(upper(text), '\w+@DB_LINK_NAME', 'i') 
),
all_src_obj AS
--this subgery get all use of remote objects 
(SELECT owner dh_owner,
     view_name dh_object_name,
     LEVEL lv,
     dbms_lob.substr(upper(regexp_substr(text, '[^ ](\w+\.)?(\$|\w)+@DB_LINK_NAME', 1, LEVEL, 'i')), --get n''th use of db_link
                     4000, 1) src_obj_call_text --convert from lob to varchar2. One view call can have length 93 char 
FROM view_def dt
CONNECT BY nocycle owner = PRIOR owner AND
         view_name = PRIOR view_name AND
         PRIOR sys_guid() IS NOT NULL AND
         LEVEL <= call_cnt --hierarchy level must be less or equel to db_links used in view count
) 
--now I parse text with object call to separate columns: src_object_owner and src_object_name
SELECT DISTINCT CASE
              WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text '.+@', 1, 1),
                                       '@', ''),
                               '(\w+)\.((\$|\w)+)') THEN
               regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),
                                      '@', ''),
                              '(\w+)\.((\$|\w)+)', '\1')
              ELSE
               'db_link user'
            END src_object_owner,
            CASE
              WHEN regexp_like(REPLACE(regexp_substr(src_obj_call_text, '.+@',1, 1),
                                       '@', ''),
                               '(\w+)\.((\$|\w)+)') THEN
               regexp_replace(REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1),
                                      '@', ''),
                              '(\w+)\.((\$|\w)+)', '\2')
              ELSE
               REPLACE(regexp_substr(src_obj_call_text, '.+@', 1, 1), '@', '')
            END src_object_name
FROM all_src_obj;

Last

Drop temporary table.

DROP TABLE tmp_views;

— Previous article

Announcing Krush - idiomatic persistence layer for Kotlin, based on Exposed

Next article —

TouK Hackathon - April 2021

You May Also Like
DDevelopment & Design

Private fields and methods are not private in groovy

I used to code in Java before I met groovy. Like most of you, groovy attracted me with many enhancements. This was to my surprise to discover that method visibility in groovy is handled different than Java!

Consider this example:

class Person {
private String name
public  String surname

private Person() {}

private String signature() { "${name?.substring(0, 1)}. $surname" }

public String toString() { "I am $name $surname" }
}

How is this class interpreted with Java?

  1. Person has private constructor that cannot be accessed
  2. Field "name" is private and cannot be accessed
  3. Method signature() is private and cannot be accessed

Let's see how groovy interpretes Person:

public static void main(String[] args) {
def person = new Person()   // constructor is private - compilation error in Java
println(person.toString())

    person.@name = 'Mike'       // access name field directly - compilation error in Java
println(person.toString())

    person.name = 'John'        // there is a setter generated by groovy
println(person.toString())

    person.@surname = 'Foo'     // access surname field directly
println(person.toString())

    person.surname = 'Bar'      // access auto-generated setter
println(person.toString())

println(person.signature()) // call private method - compilation error in Java
}

I was really astonished by its output:

I am null null
I am Mike null
I am John null
I am John Foo
I am John Bar
J. Bar

As you can see, groovy does not follow visibility directives at all! It treats them as non-existing. Code compiles and executes fine. It's contrary to Java. In Java this code has several errors, pointed out in comments.

I've searched a bit on this topic and it seems that this behaviour is known since version 1.1 and there is a bug report on that: http://jira.codehaus.org/browse/GROOVY-1875. It is not resolved even with groovy 2 release. As Tim Yates mentioned in this Stackoverflow question: "It's not clear if it is a bug or by design". Groovy treats visibility keywords as a hint for a programmer.

I need to keep that lesson in mind next time I want to make some field or method private!