Solved

how to find out the package name and table name which use a column name " check_column" in Oracle

Posted on 2011-02-14
9
794 Views
Last Modified: 2012-05-11
i have to find out the package name and table name which use the column name "check_column" in Oracle.

because the data type of  this column has been changed, so have to check all the package ,procedure and function would afacte it .

select * from dba_tab_columns
select  * from dba_source

is there any quick method to debug, because it involves a lot of package?

thanks



0
Comment
Question by:SayYou_SayMe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 357 total points
ID: 34892693
All stored packages/procedures/functoins can be found in dba_source.

Something like:
Select distinct owner, name from dba_source where lower(text) like '%column_name%;
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 72 total points
ID: 34892717
If you are using TOAD, it's quite easy

Searching text or column_name in procedure may take lot of time.

SELECT * FROM DBA_SOURCE
WHERE TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACAKGE BODY')
AND UPPER(TEXT) LIKE '%CHECK_COLUMN%'

SELECT * FROM DBA_TAB_COLUMNS WHERE COLUMN_NAME = 'CHECK_COLUMN'
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 357 total points
ID: 34892778
What is the data type change?

You might also need to check dba_constraints SEARCH_CONDITION column.  There might be check constraints.

In theory, you should not be able to modify the column but if you are looking for what you 'might' need to be aware of, you need to look.

The problem here is SEARCH_CONDITION is a LONG and is not easily searched.  Once you get the tables with that column it in, search dba_constraints for that table and manually look.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 71 total points
ID: 34892780
You can try this one also.

select * from user_dependencies ud, user_tab_columns utc
where utc.table_name = REFERENCED_NAME
and utc.column_name='ITS_ME'
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 357 total points
ID: 34892804
dba/user/all_dependencies works unless there is dynamic SQL in any of the code.
0
 

Author Comment

by:SayYou_SayMe
ID: 34893412
thanks guys

questions:

--if the store procedure declare number datatype, can pass 1000.12 value to it?

--if the column data type is number, we  can not pass the 1000.12 to it ,right?

--if the column datatype is number(10,2), we can pass the 1000 to it ,correct?

does it raise error message or trucate the values?

thanks
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 357 total points
ID: 34893432
Bset thing to do is just try it with a simple test.

Number by itself can handle decimals.

Create or replace procedure testproc(val in number)
Is
Begin
Dbms_output.put_line('got: ' to_char(val,'999,999.99'));
End;
/

Exec testproc(1000.12);

Number(10,2) will handle numbers with 8 to the left of the decimal and two to the right.  No truncation.
0
 

Author Comment

by:SayYou_SayMe
ID: 34893520
if we define the column is number(10,0)
we can not insert the value 1000.12

insert into test_table values(1000.12)

it would only insert 1000, is it correct?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 357 total points
ID: 34893546
This is getting a little off the original question.  If this continues to deviate, it should probably be a new question.

Again, set up a quick test.

It will round:

Create table tab1 (col1 number(10,0));
Insert into tab1 values(1000.12);
Insert into tab1 values(1000.99);

Select * from tab1;
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question