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.
:

honest question from a business standpoint:

if you're developing a commercial product (IIUC) that runs against an oracle database, don't you have anybody on staff that knows oracle (or database design, for that matter)?

having your product's stored procedures developed via a public forum doesn't sound like a real good approach to product development... have you thought about taking some oracle classes, hiring a consultant, or otherwise getting some in-house oracle skills? you might want to search CDO for 'beginner' and 'tutorial' for some good suggestions on getting a good foundation in oracle

++ mcs Received on Thu May 25 2006 - 07:10:02 CDT