Inserting rows in oracle livesql

Question

I am experimenting on https://livesql.oracle.com/ but got stuck with an error.
On executing select * from customers; I see the message no data found
When I run INSERT INTO customers (customer_id, customer_name, city) VALUES (1, "name-1","city-1");
I get an error message ORA-00984: column not allowed here

What could be the reason for this? How can I insert values in the table?

Could you share the whole code? For example the creation of the table, too. — gotqn, Mar 23 '23 at 07:28
Please check the syntax of [Text Literals](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-1824CBAA-6E16-4921-B2A6-112FB02248DA) (it's worth reading at least because it describes q-string format also) — astentx, Mar 23 '23 at 08:38

score 1 · Accepted Answer · answered Mar 23 '23 at 07:28

In Oracle, strings are enclosed into single quotes:

INSERT INTO customers (customer_id, customer_name, city) 
VALUES 
(1, 'name-1', 'city-1');

You enclosed them into double quotes; to Oracle, it means that you actually specified column names.

Generally speaking, when working with Oracle you should omit double quotes (with identifiers) and avoid mixed letter case. You can create table e.g.

SQL> create table "Test" (ID number, "LastName" varchar2(20));

Table created.

but then you have to use double quotes and match letter case every time you access such a table:

SQL> insert into test (id, lastname) values (1, 'Littlefoot');
insert into test (id, lastname) values (1, 'Littlefoot')
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into "Test" (id, "LastName") values (1, 'Littlefoot');

1 row created.

SQL>

If you created it as

SQL> create table test (id number, LASTnAme varchar2(20));

Table created.

then you can write identifiers any way you want:

SQL> insert into teST (id, lasTNamE) values (1, 'Littlefoot');

1 row created.

SQL>

Inserting rows in oracle livesql

1 Answers1