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
796 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

615 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