Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
800 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 1428 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 288 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 1428 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 284 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 1428 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 1428 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 1428 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

730 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