Oracle uses shell scripts to view package information

Oracle uses shell scripts to view package information

Sometimes I want to view the information of a package, but I am not sure about the package name. For example, I only know a rough idea and some keywords. In this case, I cannot find the package information through a graphical tool, for package information, I only care about the stored procedures and functions in the package. Let's take a look at simple parameters, similar to the desc format of sqlplus.

The implementation of shell scripts is as follows,

The following script is used to check whether the package information exists.

PROC_OWNER = 'sqlplus-silent $ DB_CONN_STR @ $ SH_DB_SID <END
Set pagesize 50 feedback off verify off heading on echo off
Col owner format a20
Col object_name format a30
Col subobject_name format a10
Set linesize 150
Break on object_name
Select object_name, owner, subobject_name, object_type, object_id, created, last_ddl_time, status
From dba_objects where object_type like 'package % 'and object_name like upper (' $ 2% ') and owner = upper (' $1 ')
Order by object_name
/
Exit;
End'
If [-z "$ PROC_OWNER"]; then
Echo "no object exists, please check again"
Exit 0
Else
Echo '************************************** *****'
Echo "$ PROC_OWNER"

 

PACK_LIST = 'sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID <END
Col name format a30
Col text format a100
Set linesize 200
Set pages 50
Break on name
Select name, text from dba_source where owner like UPPER ('$ 1') and name like upper (' $ 2% ') and type = 'package'
And (text like '% PROCEDURE %' or text like '% FUNCTION % ')
Order by name, line;
Exit;
End'
Echo "$ PACK_LIST"
Echo '************************************** *****'
Fi
Exit

The script is run as follows.

[Ora11g @ rac1 dbm_lite] $ ksh findpack. sh sys dbms_metadata
**************************************** ***

OBJECT_NAME owner subobject _ OBJECT_TYPE OBJECT_ID CREATED LAST_DDL _ STATUS
------------------------------------------------------------------------------------------------------------------
DBMS_METADATA sys package body 11981 23-JAN-14 23-JAN-14 VALID
Sys package 8399 23-JAN-14 23-JAN-14 VALID
DBMS_METADATA_BUILD sys package 8401 23-JAN-14 23-JAN-14 VALID
Sys package body 11984 23-JAN-14 23-JAN-14 VALID
DBMS_METADATA_DIFF sys package body 11986 23-JAN-14 23-JAN-14 VALID
Sys package 8405 23-JAN-14 23-JAN-14 VALID
DBMS_METADATA_DPBUILD sys package body 11985 23-JAN-14 23-JAN-14 VALID
Sys package 8403 23-JAN-14 23-JAN-14 VALID
DBMS_METADATA_INT sys package body 11982 23-JAN-14 23-JAN-14 VALID
Sys package 9666 23-JAN-14 23-JAN-14 VALID
DBMS_METADATA_UTIL sys package 9681 23-JAN-14 23-JAN-14 VALID
Sys package body 11983 23-JAN-14 23-JAN-14 VALID

NAME TEXT
------------------------------ Success ----------------------------------------------------------------------------------------------------------------------------------
DBMS_METADATA FUNCTION open (
PROCEDURE set_filter (
PROCEDURE set_filter (
PROCEDURE set_filter (
PROCEDURE set_count (
PROCEDURE set_xmlformat (
FUNCTION get_query (

If you want more package information, you can use the following script.

PROC_OWNER = 'sqlplus-silent $ DB_CONN_STR @ $ SH_DB_SID <END
Set pagesize 40 feedback off verify off heading on echo off
Col owner format a20
Col object_name format a30
Col subobject_name format a10
Set linesize 150
Break on object_name
Select object_name, owner, subobject_name, object_type, object_id, created, last_ddl_time, status from dba_objects where object_type like 'package % 'and object_name = upper (' $2 ') and owner = upper ('$1 ')
Order by OBJECT_ID
/
Exit;
End'

If [-z "$ PROC_OWNER"]; then
Echo "no object exists, please check again"
Exit 0
Else
Echo '************************************** *****'
Echo "$ PROC_OWNER"
Echo '************************************** *****'
Fi

Sqlplus-silent $ DB_CONN_STR @ $ SH_DB_SID <EOF
Prompt.
Sets long 99999
Set pages 0
Select text
From dba_source
Where type in ('package body', 'package') and name = upper ('$ 2') and owner = upper (' $1 ')
Order by type, line;

 

EOF
Exit

The script runs as follows:
[Ora11g @ rac1 dbm_lite] $ ksh showpack. sh sys DBMS_METADATA_UTIL | less
**************************************** ***

OBJECT_NAME owner subobject _ OBJECT_TYPE OBJECT_ID CREATED LAST_DDL _ STATUS
------------------------------------------------------------------------------------------------------------------
DBMS_METADATA_UTIL sys package 9681 23-JAN-14 23-JAN-14 VALID
Sys package body 11983 23-JAN-14 23-JAN-14 VALID
**************************************** ***
.
PACKAGE dbms_metadata_util AUTHID DEFINER
------------------------------------------------------------
-- Overview
-- This pkg implements utility functions of the mdAPI.
---------------------------------------------------------------------
-- SECURITY
-- This package is owned by SYS. It runs with definers, not invokers rights
-- Because it needs to access dictionary tables.

-------------
-- EXCEPTIONS
--
Invalid_argval EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_argval,-31600 );
Invalid_argval_num NUMBER: =-31600;
-- "Invalid input value % s for parameter % s in function % s"
-- * Cause: a null or invalid value was supplied for the parameter.
-- * Action: Correct the input value and try the call again.

Invalid_operation EXCEPTION;
PRAGMA EXCEPTION_INIT (invalid_operation,-31601 );
Invalid_operation_num NUMBER: =-31601;
-- "Function % s cannot be called now that fetch has begun"
-- * Cause: The function was called after the first call to FETCH_xxx.
-- * Action: Correct the program.