Oracle ORA-02287: sequence number not allowed here

Oracle ORA-02287: sequence number not allowed here

Get Social!

I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:

ORA-02287: sequence number not allowed here

I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.

I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.

Solution

The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:

CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/

You can now query the view without any pesky ORAs.

SELECT * FROM view_seq_test
/

    SEQ_ID D
---------- -
         1 X

1 row selected.

See the test script here.


Oracle View Sequence Test

Get Social!
CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/
SELECT * FROM view_seq_test
/
DROP SEQUENCE seq_test
/
DROP FUNCTION func_seq_test
/
DROP VIEW view_seq_test
/

 


Explain Why An Oracle Database Materialized View Does Not Rewrite

Get Social!

Using Oracle Database materialized views for query rewrite, when used in the right way, can really help aid performance with specific queries. It’s one of my favorite ways to quickly help increase performance of specific queries – often queries issued by front end reporting tools.

Materialized views, however, can be finicky in which queries they re-write, if any and the optimiser output doesn’t give away many clues.

Luckily, help is at hand with a small amount of setup and the DBMS_MVIEW.EXPLAIN_REWRITE package.

Setup

First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message 
  measure_in_msg        VARCHAR2(30),   -- Measure in current message 
  join_back_tbl         VARCHAR2(30),   -- Join back table in message 
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used 
  reerved2              VARCHAR2(10))   -- currently not used;

Rewrite output

Once the output table is in place we can run the DBMS_MVIEW.EXPLAIN_REWRITE package to begin to understand why queries are/ are not being rewritten.

Run the package and substitute the following parameters:

  • arg0 is the query you’re trying to re-write with the materialized view.
  • arg1 is the materialized view itself.
declare
begin  
    DBMS_MVIEW.EXPLAIN_REWRITE (
      query=>q'[SELECT SUM(value) val FROM performance.bill_detail]',
      mv=>'performance_mvs.mv_bills');
end;
/

Finally, view the result

SELECT * FROM REWRITE_TABLE

Scripted Install of Oracle Java 8 on Ubuntu 16.04

Get Social!

Please see Install Oracle Java In Debian/ Ubuntu using apt-get for more information.

apt install -y software-properties-common
apt-add-repository -y ppa:webupd8team/java
apt update
echo oracle-java8-installer shared/accepted-oracle-license-v1-1 select true | /usr/bin/debconf-set-selections
apt install -y oracle-java8-installer

 


Visit our advertisers

Quick Poll

Do you use ZFS on Linux?

Visit our advertisers