Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-02287 sequence number not allowed here

Posted on 2011-02-23
6
Medium Priority
?
1,351 Views
Last Modified: 2012-05-11
i have this sequence
-- Create sequence
create sequence SEQ_CODIGOAPLICACIONFONDOTMP
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
nocache;

i try execute this query
Insert into TEMP_APLICACIONFONDO (CODIGOAPLICACIONFONDO, NUMEROINSTRUCCION, PORCENTAJE, CODIGOPRODUCTO, NUMEROCREDITO )
      select * from
            (
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje1 as ApliPor, AplicacionCodProducto1 as ApliCodProd, AplicacionCredito1 as AplicaCred
              from TMP_SOLICITUD
             where AplicacionPorcentaje1 is not null or AplicacionCodProducto1 is not null or AplicacionCredito1 is not null
            union
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje2 as ApliPor, AplicacionCodProducto2 as ApliCodProd, AplicacionCredito2 as AplicaCred  
              from TMP_SOLICITUD
             where AplicacionPorcentaje2 is not null or AplicacionCodProducto2 is not null or AplicacionCredito2 is not null
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje5 as ApliPor, AplicacionCodProducto5 as ApliCodProd, AplicacionCredito5 as AplicaCred  
              from TMP_SOLICITUD
             where AplicacionPorcentaje5 is not null or AplicacionCodProducto5 is not null or AplicacionCredito5 is not null
            )  s
            
            but i have this error:
            ORA-02287 sequence number not allowed here
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964154
You can only select the nextval like that in 10g or above.

Are you on 10g?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34964171
you can't have sequences in a subquery
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964174
From a previous question of yours you are using 9.2.0.1.

You will need to select the nextval into a variable and insert that variable.

If you are doing this insert statement outside code of code where you can create and use a variable, you will need to write a before insert trigger to populate CODIGOAPLICACIONFONDO.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 74

Expert Comment

by:sdstuber
ID: 34964195
I also notice your select query only inserts 4 columns, but you have 5 columns specified in your insert
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34964218
change your select to something like this...

and then also make sure you are inserting the right number of columns
either remove a column from the insert or select an additional column



SELECT seq_codigoaplicacionfondotmp.NEXTVAL, aplipor, aplicodprod, aplicacred
  FROM (SELECT aplicacionporcentaje1 AS aplipor,
               aplicacioncodproducto1 AS aplicodprod,
               aplicacioncredito1 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje1 IS NOT NULL
               OR aplicacioncodproducto1 IS NOT NULL
               OR aplicacioncredito1 IS NOT NULL
        UNION
        SELECT aplicacionporcentaje2 AS aplipor,
               aplicacioncodproducto2 AS aplicodprod,
               aplicacioncredito2 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje2 IS NOT NULL
               OR aplicacioncodproducto2 IS NOT NULL
               OR aplicacioncredito2 IS NOT NULL
        UNION
        SELECT aplicacionporcentaje5 AS aplipor,
               aplicacioncodproducto5 AS aplicodprod,
               aplicacioncredito5 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje5 IS NOT NULL
               OR aplicacioncodproducto5 IS NOT NULL
               OR aplicacioncredito5 IS NOT NULL) s
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964278
sdstuber is correct with the subselect.  

Forget my post.  To be honest I cannot remember what version of Oracle allows the use of NEXVAL on an insert into select statement.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question