Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 803
  • Last Modified:

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

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
SayYou_SayMe
Asked:
SayYou_SayMe
7 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
ajexpertCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Devinder Singh VirdiCommented:
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
 
slightwv (䄆 Netminder) Commented:
dba/user/all_dependencies works unless there is dynamic SQL in any of the code.
0
 
SayYou_SayMeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
SayYou_SayMeAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now