Re: Pass a view as a parameter?

Howard,

Don't know if this would help but.... i'll tell you anyway....

I have a date/time based table similar to yours with DT_FROM and DT_TO fields in them.

CREATE TABLE POLICY (

KEY                    NUMBER NOT NULL,
.

DT_FROM                DATE NOT NULL,
DT_TO                      DATE);

Giving me all version of a policy. No policy are deleted from our table, if something changes in one record, a new version of it is generated. The current policy version has DT_TO set to NULL.

KEY DT_FROM DT_TO

------------- ------------------ ------------------
1             98/01/01 00:00:00  99/06/01 10:59:59
1             99/06/01 11:00:00  99/06/04 16:00:00
1             99/06/04 16:00:01

In my application, i need my user to see the current version of the policy based on the current date. This is easy to do, all i have to find is the policy having a DT_TO set to NULL and voila. BUT, the user can modify it's current date/time and i wanted to do something like what you are trying to do.

What i did :
- Created a "context" package with two functions : "SetCurrentDate" and "WhatDateAreWe".
- By default, the package sets the date with "sysdate". - If the application user changes it's context date/time, i call "context.SetCurrentDate" with the date he (she) desires. - Created a view like this.

CREATE OR REPLACE VIEW POLICY_IN_CONTEXT AS     SELECT KEY,...

      FROM POLICY
     WHERE CONTEXT.WhatDateAreWe() BETWEEN DT_FROM and NVL(DT_TO,SYSDATE);

This way, i don't have to dynamically create views and, since i use packages, each user session has it's own "context".

Don't know if this helps.

Have fun,

Marc Mazerolle
InforMaze Technologies.

Howard Lee Harkness wrote:

> 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 - 14:31:17 CDT