Oracle uses shell scripts to view procedure information

Oracle uses shell scripts to view procedure information

A schema may contain a large amount of procedure. Sometimes, to view specific information, you must use tools such as toad and plsql dev. Sometimes, you can try to get rid of the graphic tool, if you want to find some information as soon as possible, you can use shell scripts faster, more accurate, and more direct.

You can use the following shell script to search for procedure information.
The following scripts can be used to find the required prcedure information.

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
Set linesize 150
Select owner, object_name, object_id, object_type, aggregate, pipelined, parallel, interface, deterministic, authid from dba_procedures
Where owner = upper ('$ 1') and object_type = 'processed' and object_name like' % '| upper (' $ 2') | '%'
/
Exit;
End'

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

The following script can view the corresponding procedure information

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
Set linesize 150
Select owner, object_name, object_id, object_type, aggregate, pipelined, parallel, interface, deterministic, authid from dba_procedures
Where owner = upper ('$ 1') and object_type = 'processed' and object_name like' % '| upper (' $ 2') | '%'
/
Exit;
End'

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

The script runs as follows:

[Ora11g @ rac1 dbm_lite] $ ksh findproc. sh n1
**************************************** ***

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE specify PIP PAR INT DET AUTHID
----------------------------------------------------------------------------------------------------
N1 TEST_DUMP_CSV 15163 PROCEDURE NO DEFINER
**************************************** ***
[Ora11g @ rac1 dbm_lite] $ ksh showproc. sh n1 test_dmp_csv
No object exists, please check again

[Ora11g @ rac1 dbm_lite] $ ksh showproc. sh n1 test_dump_csv
**************************************** ***

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE specify PIP PAR INT DET AUTHID
----------------------------------------------------------------------------------------------------
N1 TEST_DUMP_CSV 15163 PROCEDURE NO DEFINER
**************************************** ***
.
Procedure test_dump_csv
As
Rows number;
Begin
Rows: = dump_csv ('select *
From t
',
',', '/Tmp', 'test. dat ');
End;