Pass a view as a parameter?

From: Howard Lee Harkness <hl_harkness_at_my-deja.com>
Date: Wed, 16 Jun 1999 18:07:33 GMT
Message-ID: <7k8p4s$red$1@nnrp1.deja.com>

I'm not sure this is possible, but I would like to find out for sure.

I have a multi-user application, in which I build some temporary tables, based on an effective date. To apply the effective date, I use a view that looks like:

PROCEDURE effective_view(strDate IN VARCHAR2) IS

   command VARCHAR2(512);
BEGIN
   command :=

      'CREATE OR REPLACE VIEW sec_psf_effective_view AS SELECT * FROM sec_psf' ||

         ' WHERE (psf_date_stop >= TO_DATE('''          ||
            strDate                                     ||
            ''', ''dd-mon-yyyy'' )'                     ||


            ' OR psf_date_stop IS NULL) AND'            ||


            '( psf_date_start <= TO_DATE('''            ||
            strDate                                     ||
            ''', ''dd-mon-yyyy'' )'                     ||


            ' OR psf_date_start IS NULL) AND'           ||


            ' psf_part <> psf_assy';


	--The last part is to remove a data-entry error, where
	-- somebody entered a self-referential part/assembly

   execute_immediate(command);
...

The temporary table is a tree-structure which pulls stuff from several other tables in order to simplify the process of running reports. The heart of the tree-builder is the following cursor:

CURSOR where_cur (partnumber IN VARCHAR2) IS

   SELECT LEVEL,

          psf_find_nbr,
          psf_assy,
          psf_part

   FROM sec_psf_effective_view
   CONNECT BY PRIOR psf_assy = psf_part    START WITH psf_part = partnumber;

The primary reason for the use of a view is that the CONNECT BY seems to be allergic to WHERE clauses, and a view seemed to be the easy way around that. I can build any number of tree-structures in the tree table, and select by the user, but I don't know how to change the name of the view dynamically.

What I want to do is allow several users to simultaneously run the report application, using possibly different dates for the view. Is it possible to create a view using a name based on the user's name and pass that view to the cursor? What would that look like?

I currently use a table and sequence to keep track of users to keep them from stepping on each other, but I need true concurrency, not just a serial queueing mechanism.

I am also open to hearing any different approaches. --
Howard Lee Harkness
Spammer Note: Replies sent to this email address are discarded unread. Note to others: I will be automatically notified about replies to this article. I may also be reached at harkness at skeptics dot org.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 16 1999 - 13:07:33 CDT