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

<<  April 2018  >>
MoTuWeThFrSaSu
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456

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 2018

Subscribe