Zależności między obiektami bazodanowymi w Oracle


Na jakiś czas skończyłem zabawę z JBoss, Maven oraz EJB. Dla odmiany zagłębie się na chwile w otchłań Oracle’owej bazy danych. Przygoda ta będzie wiązała się z wyszukiwaniem wszystkich komponentów wykorzystujących dany obiekt bazodanowy. Oracle w swojej uprzejmości dostarcza widok DEPTREE oraz bardziej przyjazny dla oczu IDEPTREE. Perspektywy te mają za zadanie wskazać wszystkie miejsca wykorzystania danej funkcji, procedury, pakietu, widoku oraz tabeli (mam nadzieję, że wymieniłem wszystkie). W celu uzupełnienia danych prezentowanych przez wcześniej wspomniane perspektywy, należy wykonać jako SYS skrypt $ORACLE_HOME/RDBMS/admin/utldtree.sql, a następnie wygenerować informację o interesującym Nas obiekcie:

EXECUTE deptree_fill('TABLE','HR','EMPLOYEES');

Operacja to spowoduje uzupełnienie widoków IDEPTREE oraz DEPTREE odpowiednimi danymi.

SELECT * FROM ideptree;

Wszystko wygląda pięknie. No może poza brakiem takich informacji jak np. numery linii, w których znajduje się dane odwołanie. Bez informacji owej można sobie jednak poradzić. Problem powstaje gdy grupujemy funkcje oraz procedury w pakiety i mamy na celu wyszukanie wywołań konkretnej z nich. DEPTREE obsługuje jedynie całe pakiety, ewentualnie uwzględniając podział na specyfikację oraz ciało.

Trochę dłubaniny i napisałem prosty skrypt umożliwiający wyszukanie wszystkich wystąpień danego fragmentu kodu. Przedstawia się on następująco:

CREATE OR REPLACE TYPE usage_location IS OBJECT (
   object_type VARCHAR2(50),
   object_name VARCHAR2(50),
   line_number NUMBER,
   CONSTRUCTOR FUNCTION usage_location(object_type VARCHAR2
                                     , object_name VARCHAR2
                                     , line_number NUMBER) RETURN SELF AS RESULT
   );

CREATE OR REPLACE TYPE BODY usage_location AS
   CONSTRUCTOR FUNCTION usage_location(object_type VARCHAR2
                                     , object_name VARCHAR2
                                     , line_number NUMBER) RETURN SELF AS RESULT
   AS
   BEGIN
      SELF.object_type := object_type;
      SELF.object_name := object_name;
      SELF.line_number := line_number;
      RETURN;
   END;
END;

CREATE OR REPLACE TYPE usage_location_list
IS TABLE OF usage_location;

CREATE OR REPLACE FUNCTION find_usage(p_text IN VARCHAR2
                                    , p_owner VARCHAR2 := user) RETURN usage_location_list PIPELINED
IS
   c_view_type_name CONSTANT VARCHAR2(10) := 'VIEW';

   v_view_source CLOB;
   v_location usage_location;
BEGIN
   -- Checking views
   FOR r IN (SELECT * FROM all_views WHERE owner = p_owner)
   LOOP
      SELECT dbms_metadata.get_ddl(c_view_type_name, r.view_name)
        INTO v_view_source
        FROM dual;
      IF (dbms_lob.instr(LOWER(v_view_source), LOWER(p_text)) != 0) THEN
         v_location := NEW usage_location(c_view_type_name, r.view_name, NULL);
         PIPE ROW ( v_location );
      END IF;
   END LOOP;

   -- Checking packages & triggers
   FOR r IN ( SELECT *
                FROM all_source
               WHERE owner = p_owner
                 AND LOWER(text) like LOWER('%' || p_text || '%') )
   LOOP
      v_location := NEW usage_location(r.type, r.name, r.line);
      PIPE ROW ( v_location );
   END LOOP;
END;

SELECT *
  FROM TABLE ( find_usage('employees') );

Na początku tworzymy typ obiektowy usage_location z jednym konstruktorem. Typ ten określa wszystkie właściwości pojedynczego wystąpienia danego obiektu, jakie będziemy prezentować: rodzaj komponentu, jego nazwę oraz numer linii. Kolejny krok polega na zdefiniowaniu kolekcji wspomnianego typu, którą zwracać będzie docelowa funkcja pipeline’owa find_usage. Polecam zapoznanie się z jej ciałem. Na końcu skryptu zaprezentowano przykładowe wywołanie. W celu odszukania konkretnej procedury umieszczonej w pakieci napisalibyśmy coś w stylu: SELECT * FROM TABLE ( find_usage(‘mypackage.myprocedure’) );. Kod PL/SQL posłuży zapewne czytelnikom w celach edukacyjnych.

Advertisements

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

%d bloggers like this: