This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Other Languages and the fly likes Pl/SQL Help-Procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Languages » Other Languages
Bookmark "Pl/SQL Help-Procedures" Watch "Pl/SQL Help-Procedures" New topic
Author

Pl/SQL Help-Procedures

James Hambrick
Ranch Hand

Joined: Sep 04, 2004
Posts: 282

Okay here's the situation. This DB has metadata tables. An example table name would be g3e_attribute. Once you insert a record into a metadata table a insert trigger runs and insert records into the g3e_attribute_0009 table(language table is what I call it). It would insert into g3e_attribute_000C is the default language was French. There is also a delete trigger so that if you delete a record from a metadata table it removes it from the corresponding language table. Well somehow we have entries in the language tables that are not in the metadata table. This is causing a problem with the system.

There are 139 language tables so I prefer not to do this manually.


So I am wanting to do this code

delete from <language table> where <primary key> not in(select <primary key> from <metadata table>);

So I need to loop through the table and and corresponding primary keys.

I have this code to get the table names
select table_name,substr(table_name,0,(length(table_name)-5)) from all_tables where table_name like 'G3E_%_000%';

This displays both the language table and metadata tale names.
Since the language table has either _0009 or _000C as a suffix I take everything but the last 5 characters to get the metadata table.

And I have the code to find the primary key of the table.
SELECT column_name FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = 'G3E_LEGENDENTRY' AND C.CONSTRAINT_TYPE = 'P';


So I just need to loop through all the tables. Putting these pieces together is where I run into a problem. I started making a procedure but i ran into an issue.

When you loop you move the current record of the explicit cursor into a variable. Well you can define the variable as a vertain column type(%TYPE), or row type(%ROWTYPE). Well the sql I use to create the cursor is taking columns from more than one table and is returning three columns. So I am unsure how ot define the variable. The only work around i found was to make a table that has table,table,column_name. The define the variable as that rowType.

Is there a simplier way to do this?


Thanks


Visit my blog! http://jameshambrick.com
 
Consider Paul's rocket mass heater.
 
subject: Pl/SQL Help-Procedures