fetching field information from oracle db

[Florian Albrecht]

Hi there,

as I am trying to write a recordset class, I am interested in fetching
field information from an oracle db. Several information on the fields
are very simple to request. So I already receive the field properties
name, max_length, type not_null, has_default and defaul_value. This is
achieved by using functions like oci_field_name( ). But I am not able to
request the information on if the field is primary key or marked as
unique key.

Is there a way to fetch the properties unique and primary key?

thanks,
;) Florian

[Mladen Gogala]

Re: fetching field information from oracle db

On Mon, 13 Feb 2006 00:33:14 -0800, Florian Albrecht wrote:
[color=blue]
> Is there a way to fetch the properties unique and primary key?[/color]

select col.owner,col.t able_name,col.c olumn_name,col. constraint_name
from dba_constraints con,dba_cons_co lumns col
where con.owner=col.o wner and
con.table_name= col.table_name and
con.constraint_ name=col.constr aint_name and
con.constraint_ type in ('P','U') and
con.table_name= :TABLE and
con.owner=:OWNE R

You can user all_constraints and all_cons_column s instead the dba
varieties, even LUSER_CONSTRAIN TS and LUSER_CONS_COLU MNS.


--

http://www.mgogala.com

[Michael Austin]

Re: fetching field information from oracle db

Mladen Gogala wrote:
[color=blue]
> On Mon, 13 Feb 2006 00:33:14 -0800, Florian Albrecht wrote:
>
>[color=green]
>>Is there a way to fetch the properties unique and primary key?[/color]
>
>
> select col.owner,col.t able_name,col.c olumn_name,col. constraint_name
> from dba_constraints con,dba_cons_co lumns col
> where con.owner=col.o wner and
> con.table_name= col.table_name and
> con.constraint_ name=col.constr aint_name and
> con.constraint_ type in ('P','U') and
> con.table_name= :TABLE and
> con.owner=:OWNE R
>
> You can user all_constraints and all_cons_column s instead the dba
> varieties, even LUSER_CONSTRAIN TS and LUSER_CONS_COLU MNS.
>
>[/color]


--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)