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
791 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
9 Comments
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 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