Re: Using dual to simulate a table

Stan Brown wrote:
> I have a really old piece of (binary only) code that makes a call like
> "select tstamp from tstamp where tstamp = 123456". I've always maintained a
> table with a continuous sequence of numbers for this app but I would like
> to be able to stop doing this.
>
> Isn't there a way I can create a view by the name tstamp that queries dual?
> or a sequence, somehow. The query always contains the desired answer.
>
> This is on version 7 BTW.
>

I'm not sure when sequences first made their appearance, but IIRC, they were in 7.3 if not earlier. You might want to try the following:

SQL> CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

Sequence created.

SQL> CREATE OR REPLACE FUNCTION get_seq_fn

   2 RETURN NUMBER AS
   3 seq_val NUMBER;
   4 BEGIN
   5 SELECT my_sequence.NEXTVAL INTO seq_val FROM dual;    6 RETURN seq_val;
   7 END;
   8 /

Function created.

SQL> CREATE OR REPLACE VIEW my_view

   2 AS SELECT get_seq_fn() AS next_seq_num FROM dual;

View created.

SQL> SELECT * FROM my_view;

NEXT_SEQ_NUM

            1

SQL> SELECT * FROM my_view;

NEXT_SEQ_NUM

            2

You can't put NEXTVAL in a view, but you can put it in a function and then call that function from your view.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown