Re: Oracle 6 Table Universe?

From: Siqiao Li <sli_at_ascii.csc.lsu.edu>
Date: 13 Apr 1994 17:48:29 GMT
Message-ID: <2ohb9d$ffb_at_te6000.otc.lsu.edu>


In article <2oem0s$94j_at_fido.asd.sgi.com> kxr_at_sierra.corp.sgi.com (Keith Rich) writes:
>I'm trying to debug a problem with an application that uses Oracle 6.
>I'd like to print out a bunch of my tables but I don't know the names
>of the tables. I think I should be able to do something like:
> select * from system.tables;
>Can anyone help me by telling what the name of the master table is?

The place you want to look for is probably the oracle data dictionary. all_catalog shows all tables, views synonyms sequences accessible to the current user. ACCESSIBLE_COLUMNS gives you the table column definitions--data type, precision, nullable,etc.

ALL_CONSTRAINTS and ALL_CONS_COLUMNS gives you the CONSTRAINTS when you create the table such as 'P' for Primary Key, 'R' for reference 'C' for 'Check'.

If you create table giving constraints NOT NULL Primary KEY Check(field1>10), field2 CHAR(1) CHECK(field2=UPPER(field2) and field2 in ('Y', 'N')),

Then the constraint will be stored as field1>10, field2=UPPER(field2), field2 in ('Y', 'N') in separate row of ALL_CONSTRAINTS.

The thing is Oracle 6 won't enforce to check its data dictionary. So you can still enter '5' in field1 and oracle would NOT complain.

I am writing a routine to read this information to do validation. The ultimate goal is to save other programmers' time in hard coding validation into their own apps. Whenever the database changes, those apps will get less affected.

My problem is: after reading in constraint: 'field1>10', how can I do validation on this? Using SQL or do some complier stuff such as lexical and syntax checking? There must be an elegant way to do it. Any Thoughts?

--SQL Received on Wed Apr 13 1994 - 19:48:29 CEST

Original text of this message