troubleshooting Question

Cannot user Sequence in Oracle 12 Insert

Avatar of Gary Antonellis
Gary AntonellisFlag for United States of America asked on
Oracle Database
2 Comments1 Solution1524 ViewsLast Modified:
I am trying to use a Sequence in an Oracle 12 insert statement, one sample works, the other does not.  I need help understanding why second one does not work

This works:

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

create table suppliers (supplier_id integer, supplier_name nvarchar2(40));

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');


This does not work, it generates the following error:

Error report -
SQL Error: ORA-02201: sequence not allowed here
02201. 00000 -  "sequence not allowed here"
*Cause:    An attempt was made to reference a sequence in a from-list.
*Action:   A sequence can only be referenced in a select-list.

CREATE SEQUENCE TTA_LABOR_EXPORT_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

CREATE TABLE TTA_LABOR_EXPORT
(
Record_Number              integer,
Employee_Number            nvarchar2(40) not null,
Cost_Center                  nvarchar2(80),
SAP_Activity_Type      nvarchar2(10),
Facility                  nvarchar2(40) not null,
Charge_Type                  char(1) not null,
Charge_Number            nvarchar2(80),
Operation_Number      nvarchar2(80),
Posting_Date            Date not null,
Hours_Type                  nvarchar2(2) not null,
Hours_Original            Number not null,
Hours_TTA                  Number,
Rate_Original            Number,
Rate_TTA                  Number,
Cost_Original            Number,
Cost_TTA                  Number,
Trans_action_Id            Number not null,
Export_Output_Id      Number,
Record_Status            Char(1) not null,
Error_Code                  nvarchar2(40),
Error_Text                  nvarchar2(250)
);

INSERT INTO TTA_LABOR_EXPORT_SEQ(RECORD_NUMBER,EMPLOYEE_NUMBER, COST_CENTER, SAP_ACTIVITY_TYPE, FACILITY, CHARGE_TYPE, CHARGE_NUMBER, OPERATION_NUMBER, POSTING_DATE,
HOURS_TYPE, HOURS_ORIGINAL, HOURS_TTA, RATE_ORIGINAL, RATE_TTA, COST_ORIGINAL, COST_TTA, TRANS_ACTION_ID, EXPORT_OUTPUT_ID,
RECORD_STATUS, ERROR_CODE, ERROR_TEXT ) VALUES (TTA_LABOR_EXPORT_SEQ.nextval,'T', 'T', 'T', 'T', 'T', 'T', 'T',
to_date('2014/08/21 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'T', 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0, 'T', 'T', 'T'
);
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros