Need to write a procedure that returns rows like a 'SELECT *' from any table

Hi all,

We are using Oracle 9i database for our product that deals with financial solutions. We are using an ASP.NET application and this application contains many DROPDOWNLISTs. Each of these drop down lists is associated with a 'MASTER TABLE' (we call them 'CODE TABLE's) in the DB - an one-to-one relationship.

Initially, we had only one client, and now we are moving to multiple clients. Depending on the active client, the contents of 'SOME OF' these DROPDOWNLISTs may differ. So, we need to add an extra column in some of these CODE TABLEs. This column will contain a 'comma-separated list' of client ids which 'DO NOT NEED' this particular value to be shown. But our application uses a very generic function (written in C#) to read all these CODE TABLEs using a 'SELECT * FROM [tablename]'. Now, I want to replace this SQL with a procedure, passing the tablename and the client id as input parameters. This procedure should:

1. First check whether the new column (don't need to pass this column name, this is same for each of these tables, and can be hard-coded within the procedure) exists for the table name or not -- if not simply return all the rows, same as the previous SELECT *.
2. If the column exists, return only those rows for which are either NEWCOLUMNNAME IS NULL or CLIENTID (will be passed as a parameter) is not a substring of the value stored in NEWCOLUMNNAME.

I am not a database guy, and has absolutely no idea how to do that. Please help me.

Regards,
Jeevan. Received on Thu May 25 2006 - 06:52:12 CDT