Re: Keeping ROWNUM across two SELECT statements

bert.cushman_at_formationsystems.com wrote:
> I'm trying to load a table which will have a column that uniquely
> identifies the rows. I'm not allowed to use Stored Procedures or
> Triggers (because of how we deploy our product).
>

Sigh. What's the product called? Would like to know, in order to avoid it.

> I know I can do:
>
> INSERT INTO Results_Table
> SELECT ROWNUM, Val1, Val2 FROM Table1;

Rownum is a pseudo column. Bears no value. Try this:

INSERT INTO Results_Table
SELECT ROWNUM, Val1, Val2 FROM Table1
order by val1;

>
> But would like to then have the SELECT from Table2 start at one greater
> than the next ROWNUM.
>
> I've tried:
>
> CREATE SEQUENCE my_sequence;
> INSERT INTO Results_Table
> SELECT my_sequence.NEXTVAL, Val1, Val2 FROM Table2;

Sigh. What happened to pl/sql programming techniques?!?

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> create sequence my_sequence;
Sequence created.

SQL> create table foo as (select object_id, object_type, object_name   2 from all_objects where 1=0);
Table created.

SQL> insert into foo (object_id, object_type, object_name)   2 (select my_sequence.nextval, object_type,   3 object_name from all_objects);

24520 rows created.
>
> Only to be told:
>
> ORA-02287: sequence number not allowed here
>

It's used:
SQL> select my_sequence.currval from dual;

   CURRVAL

     24520
> I've tried:
>
> DECLARE @CNT NUMBER(5,0);
> SELECT @CNT = COUNT(*) FROM Results_Table;
> INSERT INTO Results_Table
> SELECT ROWNUM + @CNT, Val1, Val2 FROM Table2;
>
> But this is clearly SQL Server syntax, and Oracle throws up on the "@"
> sign. I've seen posts on creating a Trigger on the table and using a
> SEQUENCE that way, but as I say, I'm not allowed to use triggers.

Yes - well? Does SS2K talk pl/sql? And please explain why you should use triggers and stored procedures to management. Your current excluse is... (looking for a polite word...) lame.

The above code will cause you problems, sooner or later. Sequences are autonomous; if I were to rollback the transaction, and rerun it, my object_id would start at 24521.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...