Query to find foreign keys in an Oracle database

by Cliff 30. November 2012

The following query will return all of the columns for a table and will include an indicator if the column is a foreign key:

 

select utc.*,
case when exists(
select 1 
from all_cons_columns ucc
join all_constraints uc on ucc.constraint_name = uc.constraint_name
where uc.constraint_type = 'R'
and uc.table_name = utc.table_name
and ucc.column_name = utc.column_name)
then 1
else 0
end IsForeign
from all_tab_columns utc where utc.table_name = 'YOURTABLE' and utc.owner = 'THEOWNER'

 

Tags: oracle

Oracle

Oracle Data Provider for .NET and Disposing of DbCommand and Parameters

by Cliff 4. October 2012

There are quite a few examples of using Microsoft's Enterprise Library Data Access Block that does not illustrate calling Dispose on the DbCommand and Parameters collection.  When working with ODP.NET it is a recommended best practice to explicitly dispose of both. For the DBCommand, a "using" block is a good choice as once out of scope, Dispose will always be called.

In terms of parameters, the following example can be used to ensure they are cleaned up:

foreach (OracleParameter p in cmd.Parameters)
{
if (p.Value is IDisposable)
{
((IDisposable)(p.Value)).Dispose();
}
((IDisposable)p).Dispose();
}

 

Oracle Data Provider for .NET and Disposing of DbCommand and Parameters

by Cliff 4. October 2012

There are quite a few examples of using Microsoft's Enterprise Library Data Access Block that does not illustrate calling Dispose on the DbCommand and Parameters collection.  When working with ODP.NET it is a recommended best practice to explicitly dispose of both. For the DBCommand, a "using" block is a good choice as once out of scope, Dispose will always be called.

In terms of parameters, the following example can be used to ensure they are cleaned up:

foreach (OracleParameter p in cmd.Parameters)
{
if (p.Value is IDisposable)
{
((IDisposable)(p.Value)).Dispose();
}
((IDisposable)p).Dispose();
}

 

Cliff Gray's Info

Cliff Gray
Developer/Founder GrayTechnology.com.

E-mail me Send mail

Authors

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

Blogroll

Download BlogEngine.NET

Download at CodePlex

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Subscribe