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

Need to make a quick json fixes – JSONPath for rescue

From time to time I have a need to do some fixes in my json data. In a world of flat files I do this with grep/sed/awk tool chain. How to handle it for JSON? Searching for a solution I came across the JSONPath. It quite mature tool (from 2007) but I haven't hear about it so I decided to share my experience with others.

First of all you can try it without pain online: http://jsonpath.curiousconcept.com/. Full syntax is described at http://goessner.net/articles/JsonPath/



But also you can download python binding and run it from command line:
$ sudo apt-get install python-jsonpath-rw
$ sudo apt-get install python-setuptools
$ sudo easy_install -U jsonpath

After that you can use inside python or with simple cli wrapper:
#!/usr/bin/python
import sys, json, jsonpath

path = sys.argv[1]

result = jsonpath.jsonpath(json.load(sys.stdin), path)
print json.dumps(result, indent=2)

… you can use it in your shell e.g. for json:
{
"store": {
"book": [
      {
"category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
      },
      {
"category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
      },
      {
"category": "fiction",
"author": "Herman Melville",
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"price": 8.99
      },
      {
"category": "fiction",
"author": "J. R. R. Tolkien",
"title": "The Lord of the Rings",
"isbn": "0-395-19395-8",
"price": 22.99
      }
    ],
"bicycle": {
"color": "red",
"price": 19.95
    }
  }
}

You can print only book nodes with price lower than 10 by:
$ jsonpath '$..book[?(@.price

Result:
[
  {
"category": "reference",
"price": 8.95,
"title": "Sayings of the Century",
"author": "Nigel Rees"
  },
  {
"category": "fiction",
"price": 8.99,
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"author": "Herman Melville"
  }
]

Have a nice JSON hacking!From time to time I have a need to do some fixes in my json data. In a world of flat files I do this with grep/sed/awk tool chain. How to handle it for JSON? Searching for a solution I came across the JSONPath. It quite mature tool (from 2007) but I haven't hear about it so I decided to share my experience with others.
DDevelopment & Design

Simple trick to DRY your Grails controller

Grails controllers are not very DRY. It's easy to find duplicated code fragments in default generated controller. Take a look at code sample below. It is duplicated four times in show, edit, update and delete actions:

class BookController {
def show() {
def bookInstance = Book.get(params.id)
if (!bookInstance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
return
        }
        [bookInstance: bookInstance]
    }
}

Why is it duplicated?

There is a reason for that duplication, though. If you move this snippet to a method, it can redirect to "list" action, but it can't prevent controller from further execution. After you call redirect, response status changes to 302, but after method exits, controller still runs subsequent code.

Solution

At TouK we've implemented a simple trick to resolve that situation:

  1. wrap everything with a simple withStoppingOnRender method,
  2. whenever you want to render or redirect AND stop controller execution - throw EndRenderingException.

We call it Big Return - return from a method and return from a controller at once. Here is how it works:

class BookController {
def show(Long id) {
withStoppingOnRender {
Book bookInstance = Book.get(id)
validateInstanceExists(bookInstance)
            [bookInstance: bookInstance]
        }
    }

protected Object withStoppingOnRender(Closure closure) {
try {
return closure.call()
        } catch (EndRenderingException e) {}
    }

private void validateInstanceExists(Book instance) {
if (!instance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
throw new EndRenderingException()
        }
    }
}

class EndRenderingException extends RuntimeException {}

Example usage

For simple CRUD controllers, you can use this solution and create some BaseController class for your controllers. We use withStoppingOnRender in every controller so code doesn't look like a spaghetti, we follow DRY principle and code is self-documented. Win-win-win! Here is a more complex example:

class DealerController {
@Transactional
def update() {
withStoppingOnRender {
Dealer dealerInstance = Dealer.get(params.id)
validateInstanceExists(dealerInstance)
validateAccountInExternalService(dealerInstance)
checkIfInstanceWasConcurrentlyModified(dealerInstance, params.version)
            dealerInstance.properties = params
saveUpdatedInstance(dealerInstance)
redirectToAfterUpdate(dealerInstance)
        }
    }
}