Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Pl/SQL Help-Procedures

 
James Hambrick
Ranch Hand
Posts: 282
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic