• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Pl/SQL Help-Procedures

 
Ranch Hand
Posts: 282
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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

reply
    Bookmark Topic Watch Topic
  • New Topic