Using Nextval Outside Of PL/SQL?

Mar 15, 2011

I used the following code to create a sequence

create sequence mg_sites_seq
start with 1
increment by 1
maxvalue 10000
cycle
cache 5;

then in my values clause of an insert without a distinct I had mg_sites_seq.nextval as one of my values but I'm getting an error that says it's not allowed there.I also tried to use it as a default value on the create table description (my preference) and got the same message. When I did the create table I commented out the field in the insert and value clause.

View 1 Replies


ADVERTISEMENT

PL/SQL :: Currval Acting Like Nextval

Mar 16, 2013

I’m trying to avoid a cursor solution and I can’t accomplish this using any group by or aggregate/analytical functions. This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order). why is currval giving me the nextval we should get somewhere?

Query:

INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)
select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,

[code]...

The key is that when you have cs_count of 1… get nexval, when you have value of -1 get currval, zero is zero… The first row would get nexval…. So 1 the next seven rows should be 1, then next row 0, the next row 2 and the last 5 rows will also be 2.

Query with currval and nextval:

Query:
INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)

[code]...

View 13 Replies View Related

Sequence Nextval Storage Information?

Dec 23, 2012

Sequence nextval is a pseodocolumn, but where the value of nextval is stored and why before we use CURRVAL for a sequence in our session, we must first initialize the sequence with NEXTVAL?

View 5 Replies View Related

SQL & PL/SQL :: Set Sequence Nextval As Default To Primary Key Row

Jul 15, 2012

I'm using Oracle 11g and I'm trying to set the sequence nextval as default to Primary Key row, when I create the table, Oracle throws an exception that columns are not allowed. Here is an example.

CREATE TABLE EMP (
EMP_ID VARCHAR2(50) PRIMARY KEY DEFAULT MYSECUENCE.NEXTVAL)

View 10 Replies View Related

SQL & PL/SQL :: Using Sequence.NEXTVAL From DUAL Versus In INSERT Statement?

Apr 8, 2013

I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.

--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);

-- Option1 - Using sequence.NEXTVAL from DUAL

DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause

DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved