{"id":575,"date":"2012-01-11T21:18:26","date_gmt":"2012-01-11T21:18:26","guid":{"rendered":"http:\/\/tech.avant.net\/q\/?p=575"},"modified":"2012-12-25T22:38:40","modified_gmt":"2012-12-25T22:38:40","slug":"sqlplus-view-stored-procedures","status":"publish","type":"post","link":"https:\/\/tech.avant.net\/q\/sqlplus-view-stored-procedures\/","title":{"rendered":"sqlplus, view stored procedures"},"content":{"rendered":"

I would like to view stored procedures and packages in sqlplus.<\/p>\n

All stored procedures, functions, and packages can be found in the user_source, dba_source, or all_source (depending on your access). Let’s examine user_source.<\/p>\n

\r\nfoo_owner@FOO> desc user_source\r\n Name\t\t\t\t\t   Null?    Type\r\n ----------------------------------------- -------- ----------------------------\r\n NAME\t\t\t\t\t\t    VARCHAR2(30)\r\n TYPE\t\t\t\t\t\t    VARCHAR2(12)\r\n LINE\t\t\t\t\t\t    NUMBER\r\n TEXT\t\t\t\t\t\t    VARCHAR2(4000)\r\n\r\nfoo_owner@FOO> \r\n<\/pre>\n
Let’s examine user_source.type to see what kind of objects are available to view,<\/p>\n
\r\nfoo_owner@FOO> SELECT DISTINCT type FROM user_source;\r\n\r\nTYPE\r\n------------\r\nPROCEDURE\r\nPACKAGE\r\nPACKAGE BODY\r\nFUNCTION\r\nTYPE\r\n\r\n5 rows selected.\r\n\r\nfoo_owner@FOO>\r\n<\/pre>\n
The user_source table (like dba_source and all_source) contains line-numbered text for functions, procedures, and packages.<\/p>\n
If you want a list a packages, you could simply run the following:<\/p>\n
\r\nfoo_owner@FOO> SELECT DISTINCT name FROM user_source WHERE type = 'PACKAGE';\r\n\r\nNAME\r\n------------------------------\r\nPROCS_GIFT_SUBS\r\nPROCS_TAX_EXCEPTION_REPORT_V1\r\nPROCS_GIFT_SUBS_TAX\r\nPROCS_FLASH_REPORT\r\nPROCS_TAX_V1\r\nPROCS_FISCAL\r\nPROCS_EXCEPTION_REPORT\r\nPROCS_TAX\r\n...\r\n<\/pre>\n
If you’d like to view the source for a package (the package and package body), you can query the user_source.text as follows,<\/p>\n
\r\nfoo_owner@FOO> SELECT text FROM user_source WHERE name = 'PROCS_DATE' ORDER BY type,line;\r\n\r\nTEXT\r\n----------------------------------------------------------------------------------------------------\r\nPACKAGE \t    \"PROCS_DATE\" AS\r\n  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE;\r\n  FUNCTION DATE_FROM_ID(IN_ID NUMBER) RETURN DATE RESULT_CACHE;\r\nEND;\r\n\r\nPACKAGE BODY\t\t \"PROCS_DATE\" AS\r\n\r\n  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE\r\n  IS\r\n    ID NUMBER;\r\n  BEGIN\r\n    SELECT D.ID INTO ID FROM DATES_DIM D WHERE SQL_DATE = TRUNC(IN_DATE);\r\n    RETURN ID;\r\n  END;\r\n\r\n  FUNCTION DATE_FROM_ID(IN_ID NUMBER) RETURN DATE RESULT_CACHE\r\n  IS\r\n    the_date DATE;\r\n  BEGIN\r\n    SELECT sql_date INTO the_date FROM dates_dim WHERE id = in_id;\r\n    return the_date;\r\n  END;\r\n\r\nEND;\r\n\r\nfoo_owner@FOO> \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
I would like to view stored procedures and packages in sqlplus. All stored procedures, functions, and packages can be found in the user_source, dba_source, or all_source (depending on your access). Let’s examine user_source. foo_owner@FOO> desc user_source Name Null? Type —————————————– ——– —————————- NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) foo_owner@FOO> Let’s examine user_source.type to […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/575"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=575"}],"version-history":[{"count":5,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"predecessor-version":[{"id":699,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/575\/revisions\/699"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}