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
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
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
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
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
