Tables and Coloumns in a Stored Procedure [message #2004] |
Sat, 15 June 2002 20:41 |
Syed Mehar Raza
Messages: 2 Registered: June 2002
|
Junior Member |
|
|
How to find all the Tables and their Coloums in an Stored Procedure? (If possible ) with the distinction of "Select List" coloums, "Columns used for comparision only" and so far. In short, is there any way that I can track all the database objects the stored procedure looking for.
Thanx
|
|
|
|
Re: Tables and Coloumns in a Stored Procedure [message #2018 is a reply to message #2004] |
Mon, 17 June 2002 04:10 |
Syed Mehar Raza
Messages: 2 Registered: June 2002
|
Junior Member |
|
|
I want to know the coloumns used in my Stored Procedure not in the whole database. Like in underlying example i am using 3 cols. in 'Select List' (i.e., Acct_id, Pos_id, Instr_id) and 4 columns in where cluase (i.e., acct_id, instr_id, local_curr_cde, ldgr_id).
Is there any query or tool that can tell me what "entities" and their "attributes" am I using in my Stroed procedure.
-- here is an example--
CREATE OR REPLACE PROCEDURE PROD407REP.RP_P_02C(REF_CURSOR IN OUT RP_K_02C.REF_CURSOR,
cursor MAIN is
select
acct.acct_id,
posn.pos_id,
issu.instr_id
from
ft_t_acct acct,
ft_t_posn posn,
ft_t_issu issu
where
acct.acct_id = posn.acct_id
and posn.instr_id = issu.instr_id
and acct.acct_id = '0689'
and posn.local_curr_cde = 'AUD'
and posn.ldgr_id = '8020'
BEGIN
...
OPEN REF_CURSOR FOR SELECT * FROM RP_T_02C;
...
END;
Thanx
|
|
|
Re: Tables and Coloumns in a Stored Procedure [message #2020 is a reply to message #2018] |
Mon, 17 June 2002 09:09 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I am not very sure, whether u can find the entity.attribute on which the stored procedure is dependent.
But u can find schema.entity from user_dependencies
SQL> COLUMN OBJECT FORMAT A30
SQL> ED
Wrote file afiedt.buf
1 SELECT NAME,TYPE,REFERENCED_OWNER||'.'||REFERENCED_NAME OBJECT
2 FROM USER_DEPENDENCIES WHERE NAME='F10'
3* AND REFERENCED_NAME NOT IN ('SYS_STUB_FOR_PURITY_ANALYSIS','STANDARD')
SQL> /
NAME TYPE OBJECT
---------- ------------ ------------------------------
F10 FUNCTION MAG.SAMPLE
|
|
|