Re: Keeping ROWNUM across two SELECT statements

bert.cushman@xxxxxxxxxxxxxxxxxxxx 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...
.

Relevant Pages

  • Re: COBOL/DB2 Date edit question
    ... Stored procedures are becoming a way of life on many sites, ... Banging away at databases with primitive SQL is ... Within all of this a lot of validation has been going on. ... and submit them together to an UPDATE that triggers the cross validation. ...
    (comp.lang.cobol)
  • Re: Suggestoins for Local DB to use with Shareware
    ... standard support for stored procedures, ... Stored procs and triggers are another issue altogether, ... Oracle, @Identity in MS SQL, etc. ...
    (borland.public.delphi.non-technical)
  • Re: ANN: AnySQL Maestro (freeware cross-database tool)
    ... instance, I am connecting to a SQL Anywhere database, and I don't see ... a way to explore and view stored procedures. ... About SPs and triggers: please see my answer to Kyle A. Miller in this ...
    (borland.public.delphi.thirdpartytools.general)
  • Keeping ROWNUM across two SELECT statements
    ... I'm trying to load a table which will have a column that uniquely ... I'm not allowed to use Stored Procedures or ... sequence number not allowed here ... SEQUENCE that way, but as I say, I'm not allowed to use triggers. ...
    (comp.databases.oracle.misc)
  • Re: Web app that limits access by the # of objects created
    ... I'm very comfortable with SQL (not so much TSQL), ... triggers and stored procedures with this project. ... that there are potential problems with stored procedures and triggers ...
    (microsoft.public.dotnet.framework.aspnet)
Loading