CODEWITHSUNDEEP
oracle-database
Rubin kumar
Rubin kumar

Reputation: 1

create table with values in oracle

create table careers 
(s_no number(2), 
course varchar2(20), 
topic1 varchar2(20), 
status1 varchar2(20), 
topic2 varchar2(20),
status2 varchar2(20),
topic3 varchar2(20),
status3 varchar2(20)) 
values (1,'oracle','sql','not','BI','not','apex','not');

I got a error like this:

Error starting at line : 2 in command - create table careers (s_no number(2), course varchar2(20), topic1 varchar2(20), status1 varchar2(20), topic2 varchar2(20), status2 varchar2(20), topic3 varchar2(20), status3 varchar2(20)) values (1,'oracle','sql','not','BI','not','apex','not') Error report - ORA-00922: missing or invalid option 00922. 00000 - "missing or invalid option"

Please suggest me a proper things.

Upvotes: 0

Views: 750

Answers (3)

Littlefoot
Littlefoot

Reputation: 142710

Create table first, insert next.

SQL> CREATE TABLE careers
  2  (
  3     s_no      NUMBER (2),
  4     course    VARCHAR2 (20),
  5     topic1    VARCHAR2 (20),
  6     status1   VARCHAR2 (20),
  7     topic2    VARCHAR2 (20),
  8     status2   VARCHAR2 (20),
  9     topic3    VARCHAR2 (20),
 10     status3   VARCHAR2 (20)
 11  );

Table created.

SQL>
SQL> INSERT INTO careers (s_no,
  2                       course,
  3                       topic1,
  4                       status1,
  5                       topic2,
  6                       status2,
  7                       topic3,
  8                       status3)
  9       VALUES (1,
 10               'oracle',
 11               'sql',
 12               'not',
 13               'BI',
 14               'not',
 15               'apex',
 16               'not');

1 row created.

SQL>

Alternatively, you could have created the table directly as

SQL> create table careers as
  2    select 1 s_no, 'oracle' course,
  3      'sql'  topic1, 'not' status1,
  4      'BI'   topic2, 'not' status2,
  5      'apex' topic3, 'not' status3
  6    from dual;

Table created.

but that's probably a bad idea - check datatypes and their lengts - you'll have problems for subsequent inserts (for example, you wouldn't be able to insert TOPIC2 value whose length is larger than 2 characters). Also, CHAR datatype is most probably not what you want to have (VARCHAR2 would be better):

SQL> desc careers
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 S_NO                                               NUMBER
 COURSE                                             CHAR(6)
 TOPIC1                                             CHAR(3)
 STATUS1                                            CHAR(3)
 TOPIC2                                             CHAR(2)
 STATUS2                                            CHAR(3)
 TOPIC3                                             CHAR(4)
 STATUS3                                            CHAR(3)

SQL>

So - stick to the first option.

Upvotes: 2

Nyweron
Nyweron

Reputation: 16

I think we can not create table and add values in your way. You can try this(example how should looks):

CREATE TABLE recipes (
  recipe_id INT NOT NULL AUTO_INCREMENT,
  recipe_name VARCHAR(30) NOT NULL,
  PRIMARY KEY (recipe_id),
  UNIQUE (recipe_name)
);

INSERT INTO recipes 
    (recipe_name) 
VALUES 
    ("Tacos"),
    ("Tomato Soup"),
    ("Grilled Cheese");

Upvotes: 0

dbusern
dbusern

Reputation: 315

what you have posted has some syntax errors. You have to first create the table and then insert data into it. For example the following works in my system from sqlplus...please try it:

CREATE TABLE careers
(
   s_no      NUMBER (2),
   course    VARCHAR2 (20),
   topic1    VARCHAR2 (20),
   status1   VARCHAR2 (20),
   topic2    VARCHAR2 (20),
   status2   VARCHAR2 (20),
   topic3    VARCHAR2 (20),
   status3   VARCHAR2 (20)
);

INSERT INTO careers
     VALUES (1,
             'oracle',
             'sql',
             'not',
             'BI',
             'not',
             'apex',
             'not');
commit;

Upvotes: 0

Related Questions