Re: Using dual to simulate a table

In <J34Mv9.MD0_at_igsrsparc2.er.usgs.gov> Brian Peasland <dba_at_nospam.peasland.net> writes:

>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;

Hmm, we were doing fine untill here

SQL> CREATE OR REPLACE VIEW my_view
  2 AS SELECT get_seq_fn() AS next_seq_num FROM dual;   AS SELECT get_seq_fn() AS next_seq_num FROM dual

			*
			ERROR at line 2:
			ORA-00919: invalid function

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

Not quite certain how this applies to my problem.

Let me define the problem a bit better.

The idea is to be able to return the same value we were called with, without having to store the complete range of possible call values, and do it in a fashion that a simple esistign SELECT can access.

>--
>===================================================================

>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

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin