{"id":128,"date":"2014-09-25T19:48:31","date_gmt":"2014-09-25T18:48:31","guid":{"rendered":"http:\/\/blog.ulrich-schulte.net\/?p=128"},"modified":"2014-09-27T10:06:58","modified_gmt":"2014-09-27T09:06:58","slug":"oracle-plsql-rekursive-bestimmung-von-abhaengigkeiten","status":"publish","type":"post","link":"https:\/\/ulrich-schulte.net\/?p=128","title":{"rendered":"Oracle PL\/SQL: Rekursive Bestimmung von Abh\u00e4ngigkeiten"},"content":{"rendered":"<p>Mit folgendem Statement l\u00e4\u00dft sich ein Baum von Abh\u00e4ngigkeiten eines PL\/SQL-Packages bestimmen.<\/p>\n<pre><span style=\"color: teal;\">SELECT<span style=\"color: navy;\">\u00a0<\/span>lpad<span style=\"color: navy;\">(<\/span><span style=\"color: blue;\">' '<\/span><span style=\"color: navy;\">,\u00a0<\/span><span style=\"color: blue;\">2<\/span><span style=\"color: navy;\">\u00a0* (<\/span>LEVEL<span style=\"color: navy;\">\u00a0-\u00a0<\/span><span style=\"color: blue;\">1<\/span><span style=\"color: navy;\">)) || to_char(<\/span>LEVEL<span style=\"color: navy;\">,\u00a0<\/span><span style=\"color: blue;\">'999'<\/span><span style=\"color: navy;\">)\u00a0<\/span>AS<span style=\"color: navy;\">\u00a0c1,\r\n       owner ||\u00a0<span style=\"color: blue;\">'.'<\/span>\u00a0||\u00a0<span style=\"color: teal;\">NAME<\/span>\u00a0||\u00a0<span style=\"color: blue;\">' ('<\/span>\u00a0||\u00a0<span style=\"color: teal;\">TYPE<\/span>\u00a0||\u00a0<span style=\"color: blue;\">')'<\/span>\u00a0<span style=\"color: teal;\">AS<\/span>\u00a0c2,\r\n<\/span><\/span><span style=\"color: #000000;\">       referenced_owner ||\u00a0<\/span><span style=\"color: blue;\">'.'<\/span><span style=\"color: #000000;\">\u00a0|| referenced_name ||\u00a0<\/span><span style=\"color: blue;\">' ('<\/span><span style=\"color: #000000;\">\u00a0|| referenced_type ||\u00a0<\/span><span style=\"color: blue;\">')'<\/span><span style=\"color: #000000;\">\u00a0<\/span><span style=\"color: teal;\">AS<\/span><span style=\"color: #000000;\">\u00a0c3\r\n<\/span><span style=\"color: teal;\">FROM<\/span><span style=\"color: #000000;\">\u00a0dba_dependencies \r\n<\/span><span style=\"color: teal;\"> START<\/span>\u00a0<span style=\"color: teal;\">WITH<\/span>\u00a0owner =\u00a0<span style=\"color: teal;\">USER <span style=\"color: teal;\">AND<\/span>\u00a0<span style=\"color: teal;\">NAME<\/span>\u00a0=\u00a0<span style=\"color: blue;\">'&amp;NAME'\r\n<\/span><\/span>CONNECT<span style=\"color: navy;\">\u00a0<\/span>BY<span style=\"color: navy;\">\u00a0<\/span>PRIOR<span style=\"color: #000080;\">\u00a0referenced_owner = owner\r\n<\/span><span style=\"color: teal;\">       AND<\/span><span style=\"color: #000080;\">\u00a0<\/span><span style=\"color: teal;\">PRIOR<\/span><span style=\"color: #000080;\">\u00a0referenced_name =\u00a0<\/span><span style=\"color: teal;\">NAME\r\n<\/span>\u00a0\u00a0\u00a0\u00a0   <span style=\"color: teal;\">AND<\/span>\u00a0<span style=\"color: teal;\">PRIOR<\/span>\u00a0referenced_type =\u00a0<span style=\"color: #008080;\">TYPE\r\n<\/span><span style=\"color: navy;\">\u00a0\u00a0\u00a0    <span style=\"color: teal;\">AND<\/span>\u00a0<span style=\"color: teal;\">TYPE<\/span>\u00a0=\u00a0<span style=\"color: blue;\">'PACKAGE'<\/span><\/span><span style=\"color: #008080;\">;\r\n<\/span><\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Mit folgendem Statement l\u00e4\u00dft sich ein Baum von Abh\u00e4ngigkeiten eines PL\/SQL-Packages bestimmen. SELECT\u00a0lpad(&#8218; &#8218;,\u00a02\u00a0* (LEVEL\u00a0&#8211;\u00a01)) || to_char(LEVEL,\u00a0&#8218;999&#8216;)\u00a0AS\u00a0c1, owner ||\u00a0&#8218;.&#8216;\u00a0||\u00a0NAME\u00a0||\u00a0&#8218; (&#8218;\u00a0||\u00a0TYPE\u00a0||\u00a0&#8218;)&#8216;\u00a0AS\u00a0c2, referenced_owner ||\u00a0&#8218;.&#8216;\u00a0|| referenced_name ||\u00a0&#8218; (&#8218;\u00a0|| referenced_type ||\u00a0&#8218;)&#8216;\u00a0AS\u00a0c3 FROM\u00a0dba_dependencies START\u00a0WITH\u00a0owner =\u00a0USER AND\u00a0NAME\u00a0=\u00a0&#8218;&amp;NAME&#8216; CONNECT\u00a0BY\u00a0PRIOR\u00a0referenced_owner = owner AND\u00a0PRIOR\u00a0referenced_name =\u00a0NAME \u00a0\u00a0\u00a0\u00a0 AND\u00a0PRIOR\u00a0referenced_type =\u00a0TYPE \u00a0\u00a0\u00a0 AND\u00a0TYPE\u00a0=\u00a0&#8218;PACKAGE&#8216;; &nbsp;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[],"class_list":["post-128","post","type-post","status-publish","format-standard","hentry","category-oracle"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8NCNL-24","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/posts\/128","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=128"}],"version-history":[{"count":2,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/posts\/128\/revisions"}],"predecessor-version":[{"id":130,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=\/wp\/v2\/posts\/128\/revisions\/130"}],"wp:attachment":[{"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ulrich-schulte.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}