CODEWITHSUNDEEP
sqloracle-databaseoracle11g
artemis
artemis

Reputation: 7241

CREATE TABLE..INSERT ALL INTO character error - Oracle SQL

I am trying to create a dbfiddle<> with the following query:

CREATE TABLE Requirements
    (Order_Number int, Requirement_Number varchar2(6))
;

INSERT ALL 
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-300')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-516')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-468')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-744')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (500, 'AA-100')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (500, 'AA-823')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (500, 'AA-239')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (130, 'AA-785')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (130, 'AA-171')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (200, 'AA-171')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (200, 'AA-337')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (200, 'AA-533')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (100, 'AA-100')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (493, 'AA-400')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (493, 'AA-401')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (630, 'AA-010')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (552, 'AA-112')



SELECT * FROM dual
;

CREATE TABLE Lookup
    (Requirement_ID varchar2(6), Replenishment_ID varchar2(5))
;

INSERT ALL 
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO601')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO111')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO435')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-300', 'RO608')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO734')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO245')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-516', 'RO754')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-468', 'RO120')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-468', 'RO96')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO576')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO592')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO104')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-744', 'RO169')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO567')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO90')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO202')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO764')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO434')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO752')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-823', 'RO204')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-239', 'RO367')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-239', 'RO732')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO573')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO139')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO252')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO561')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-785', 'RO775')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-171', 'RO92')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-171', 'RO532')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-337', 'RO245')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-337', 'RO367')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-533', 'RO216')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', NULL)
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-100', 'RO438')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-400', 'RO4')
    INTO Lookup (Requirement_ID, Replenishment_ID)
         VALUES ('AA-401', NULL)

SELECT * FROM dual;


CREATE TABLE Replenishments
    (Replenishment_ID varchar2(5), Replenishment_Type varchar2(6), Replenishment_Detail int, Replenishment_Date date)
;

INSERT ALL 
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO601', 'Bought', 963, '15-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO111', 'Make', 251, '23-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO435', 'Make', 837, '4-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO608', 'Make', 850, '27-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO734', 'Make', 415, '5-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO245', 'Bought', 130, '6-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO754', 'Bought', 874, '9-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO120', 'Make', 333, '28-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO96', 'Bought', 279, '11-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO576', 'Make', 452, '9-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO592', 'Bought', 967, '16-Jan-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO104', 'Make', 232, '30-Jan-19')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO169', 'Make', 804, '2-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO567', 'Make', 725, '22-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO90', 'Bought', 240, '14-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO202', 'Bought', 185, '26-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO764', 'Bought', 629, '15-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO434', 'Make', 314, '27-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO752', 'Bought', 504, '25-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO204', 'Make', 847, '9-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO367', 'Bought', 652, '14-Feb-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO732', 'Bought', 561, '3-Oct-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO573', 'Make', 616, '1-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO139', 'Make', 698, '16-Jul-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO252', 'Make', 190, '2-Aug-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO561', 'Make', 453, '13-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO775', 'Make', 974, '7-Aug-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO92', 'Bought', 493, '1-Apr-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO532', 'Make', 727, '17-May-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO29', 'Make', 402, '1-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO725', 'Make', 892, '9-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO216', 'Bought', 637, '1-Jun-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO438', 'Make', 125, '19-Mar-18')
    INTO Replenishments (Replenishment_ID, Replenishment_Type, Replenishment_Detail, Replenishment_Date)
         VALUES ('RO4', 'Bought', 591, '17-Apr-18')

SELECT * FROM dual;

I keep getting an error: ORA-00911: invalid character

I tried just running:

CREATE TABLE Requirements
    (Order_Number int, Requirement_Number varchar2(6))
;

And that works

But as soon as I tried:

CREATE TABLE Requirements
    (Order_Number int, Requirement_Number varchar2(6))
;

INSERT ALL 
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-300')
    INTO Requirements (Order_Number, Requirement_Number)
         VALUES (300, 'AA-516')

It gave the same error. How can I create this architecture on dbfiddle<>?

Upvotes: 0

Views: 55

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Semicolon delimits different statements and is usually interpreted by the client query tool, not the database, so oracle chokes on ';' as an illegal character for an SQL

Wherever you want to put a semicolon, in dbfiddle click the [+] on the left to start a new statement instead:

enter image description here

The fiddle I started with your statements:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=2b7957c49c96ec7b21193a4e5cc133e1

Upvotes: 3

Related Questions