Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus):<\/p>\n
SQL> set serveroutput on\nSQL> exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW');\n-\nDEPENDENCIES ON HR.EMP_DETAILS_VIEW\n------------------------------------------------------------------\n*VIEW HR.EMP_DETAILS_VIEW()\n\nPL\/SQL procedure successfully completed.\n\nSQL> exec dbms_utility.get_dependency('TABLE','HR','EMPLOYEES');\n-\nDEPENDENCIES ON HR.EMPLOYEES\n------------------------------------------------------------------\n*TABLE HR.EMPLOYEES()\n VIEW HR.EMP_DETAILS_VIEW()\n TRIGGER HR.SECURE_EMPLOYEES()\n TRIGGER HR.UPDATE_JOB_HISTORY()\n\nPL\/SQL procedure successfully completed.\n\nSQL><\/pre>\nOracle Database 12c has a new procedure\u00a0to provide you with the SQL behind the SQL. DBMS_UTILITY.EXPAND_SQL_TEXT. Useful to expand the views and see the base tables involved, as well as to reveal the real SQL, especially when clauses like “FETCH and OFFSET” are used.<\/p>\n
There are two CLOB parameters to this procedure – input SQL text, and output SQL text. Try this in your Oracle Database 12c…<\/p>\n
SQL> set long 32000\nSQL> variable x1 CLOB\nSQL> exec dbms_utility.expand_sql_text('SELECT TABLE_NAME FROM DBA_TABLES',:x1);\nSQL> print x1<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus): SQL> set serveroutput on SQL> exec dbms_utility.get_dependency(‘VIEW’,’HR’,’EMP_DETAILS_VIEW’); – DEPENDENCIES ON HR.EMP_DETAILS_VIEW —————————————————————— *VIEW HR.EMP_DETAILS_VIEW() PL\/SQL procedure successfully completed. SQL> exec … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"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":""},"categories":[133],"tags":[],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-oracledb"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":3022,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/352\/revisions\/3022"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}