We help IT Professionals succeed at work.

Cannot user Sequence in Oracle 12 Insert

Gary Antonellis
on
1,487 Views
Last Modified: 2014-08-22
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'
);
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Gary AntonellisSenior Application Consultant

Author

Commented:
I am going blind, looked at that multiple times and did not see it.   Thank you for pointing out the obvious!!!!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.