Programatic access to schema data
Date: 2 Jan 2003 08:55:04 -0800
Message-ID: <477625b6.0301020855.c095321_at_posting.google.com>
I have a need to write a program (PL/SQL) that can read and work with data in the meta data tables that define the schema in an Oracle database. Can someone(s) point me in the right direction here, I would need info on where all the meta data is described, how I access it programatically, and some sample code would be a huge help.
For a given application table, I want to be able to enumerate all the fields and referential constraints defined for that table, and then chase some of those referential contraints to locate "dependant" records in other tables.
Say for instance I had a zipcode table, and also an address table, where the zipcode was one column in the address table. And there was a contraint that tied the zipcode in the address table to the zipcode in the zipcode table. Naturally Oracle prevents deletion of a zipcode row if there are existing address rows that reference that zipcode. I want a little program that can look at the zipcode table, see that it has a constraint to the address table, and then read the address table to see if there are records that have a particular zipcode being used.
I know this may not be described very well, I tried to use a simple example, my problem is a lot larger, and hence the reason I'm thinking about some code that might do some of the manual work I have to do know in researching some of these items. Received on Thu Jan 02 2003 - 17:55:04 CET