[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1092
  • Last Modified:

Cannot user Sequence in Oracle 12 Insert

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'
);
0
gantone1
Asked:
gantone1
1 Solution
 
slightwv (䄆 Netminder) Commented:
My guess is it is complaining that you are trying to insert into the sequence not the actual table:
INSERT INTO TTA_LABOR_EXPORT_SEQ(
0
 
gantone1Author Commented:
I am going blind, looked at that multiple times and did not see it.   Thank you for pointing out the obvious!!!!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now