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

<jeevan.bordoloi_at_ocwen.com> wrote in message news:1148557932.631458.184770_at_i40g2000cwc.googlegroups.com...
> 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.
>

I think you need someone who is very familiar with Oracle on your team. Your solution for your business need don't match as how I would probably do it. But there are probably a lot of other factors. Having a good Oracle guru would help a lot.
Jim Received on Thu May 25 2006 - 19:33:27 CDT