OraFAQ Forum: SQL & PL/SQL » Tables and Coloumns in a Stored Procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Tables and Coloumns in a Stored Procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Tables and Coloumns in a Stored Procedure [message #2007 is a reply to message #2004]

Sun, 16 June 2002 07:42

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

what do you mean by "Columns used for comparision only"?
anyhow this may be helping you.
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure tab_cols
2 as
3 cursor c1 is select table_name,column_name from user_tab_columns;
4 begin
5 for mag in c1 loop
6 exit when c1%notfound;
7 dbms_output.put_line(mag.table_name||' '||mag.column_name);
8 end loop;
9* end;
SQL> /

Procedure created.

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	How to transform messages between distributed databases
Next Topic:	Error in reading CLOB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 04:41:54 CDT 2024