A schema may contain a large amount of procedure. Sometimes you may want to view the specific information through tools such as toad and plsqldev.
A schema may contain a large amount of procedure. Sometimes you may want to view the specific information through tools such as toad and plsql dev.
A schema may contain a large amount of procedure. Sometimes you may want to view the specific information through tools such as toad and plsql dev ,, sometimes, if you try to get rid of the graphical tools, you can use shell scripts to quickly find some information as soon as possible, which is 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 < 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 < 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;