Q: tablename as parameter to PL/SQL procedure

From: Howard Lee Harkness <hlh_nospam_at_excite.com>
Date: Tue, 20 Jul 1999 13:33:00 GMT
Message-ID: <6761E31D9F46633D.18FFEAB4B42936E6.E672E6294E9990EC@lp.airnews.net>

hlh_nospam_at_excite.com is a spamtrap. If you wish to respond, please post in this newsgroup, or email me at harkness at skeptics dot org.

My client has a problem with a document management system (Centra 2000 by Autotrol) which uses Oracle 7.3.4. The custom import program that Autotrol wrote for them created the drawing entries in a random order, and the client wishes them to be displayed in revision order. Centra 2000 does not provide any ordering of the display except by an internal numeric (unique) identifier called object_id, which is used as a foreign key in 32 other tables. Each revision of a drawing has a globally unique object_id.

I am soliciting suggestions for how to solve this problem. What I have come up with so far is to generate a temporary table (XREF) with the object_id in revision order, with another column for the object_id in numerical order, and then do a substitution of all the object_id's in one pass, at a time that Centra is shut down. I think I have invented a satisfactory way to build such a cross-reference table.

In pseudocode:

For each drawing in the objects table

   list the object id's in revision order in XREF    associate new object id's in numeric order    For each table that uses object_id

       For each old_object_id, new_object_id in XREF
           Update old_object_id with new_object_id in current table
       End if

   End if
End if

Obviously, that glosses over a lot of detail. For instance, I don't know how to pass a table name to a PL/SQL procedure. Is that possible? Is there a better way? The only thing I have come up with so far is a cursor variable (reference), and code to assign it to each of the tables in turn, which strikes me as pretty gross.

TIA.
hlh_NOSPAM_at_excite.com is a valid, unmunged address! It is also so full of spam(!) that I don't read it. Received on Tue Jul 20 1999 - 08:33:00 CDT