Oracle Insert Script Error

Question

Ok So I a looking at oracle for school and am having a lot of issues getting my database to create. I am using SQL developer. I am getting multiple errors for my inserts. Looking through I do not see anything wrong with the script. Can someone look it over and tell me what I did wrong? I am new here so go easy on me.

`DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE; 

CREATE TABLE Location
(Locid NUMBER (5) Primary Key,
Bldg_code VARCHAR2 (10) NOT NULL,
Room VARCHAR2 (6) NOT NULL,
Capacity NUMBER (5) 
);

INSERT INTO Location VALUES
(53, BUS, 424, 45);
INSERT INTO Location VALUES
(54, BUS, 402, 35);
INSERT INTO Location VALUES
(55, BUS, 433, 100);
`

Bacs · Accepted Answer · 2014-08-18 14:58:00Z

1

Your VARCHAR2 values need to be in single quotes:

insert into location values
(53, 'BUS', '424', 45);

etc...

edited Aug 18, 2014 at 14:58

answered Aug 18, 2014 at 14:38

Bacs

9191 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

neshkeev Over a year ago

you don't have to explicitly convert numbers into varchar2, sometimes even varchar2 to number

neshkeev Over a year ago

Once I had a project where we didn't specify columns intentionally, we had dynamic SQL something like this: INSERT INTO table1@dblink1 SELECT * FROM table1. If the local table1 table had structure changes (drop/add columns) we could track errors right away. If we specified the columns we would've taken a lot of times to find all the problems.

Bacs Over a year ago

I think explicit conversion is generally considered best practice - a matter or opinion perhaps, but it is certainly the recommendation in the Oracle docs.

neshkeev Over a year ago

So then you have to use the TO_CHAR function it is more best practice:) Of course implicit conversion depends on your NLS environment, but there is really small amount of system that needs to change the NLS environment.

Bacs Over a year ago

I like to specify column names to keep my code clearer, but I appreciate this may be a matter of opinion so I've deleted that part of my answer.

|

neshkeev · Accepted Answer · 2014-08-19 20:09:00Z

0

BUS has to be 'BUS':

INSERT INTO Location VALUES
(53, 'BUS', 424, 45);
INSERT INTO Location VALUES
(54, 'BUS', 402, 35);
INSERT INTO Location VALUES
(55, 'BUS', 433, 100);

When you pass a numeric value to a varchar2 column oracle convert it to varchar2 automatically:

First session:

SQL> conn hr/hr
Connected.
SQL> create table table_for_mine(
  2    id varchar2(20)
  3  );

Table created.

SQL> insert into table_for_mine values (123);

1 row created.

Second session:

SQL> conn / as sysdba

SQL> l
  1* SELECT distinct member LOGFILENAME FROM V$LOGFILE
SQL> /

LOGFILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log

SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/oradata/orcl/redo01.log');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/oradata/orcl/redo02.log');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/oradata/orcl/redo03.log');

PL/SQL procedure successfully completed.

SQL> execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select sql_redo from v$logmnr_contents where table_name = 'TABLE_FOR_MINE' and sql_redo like 'insert%';

SQL_REDO
--------------------------------------------------------------------------------
insert into "HR"."TABLE_FOR_MINE"("ID") values ('123');

SQL> execute DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.

As you can see, Oracle added single quotes to your numeric value and wrote it to the online redo log file. So if there is a blackout, next time Oracle startups he will execute this command:

insert into "HR"."TABLE_FOR_MINE"("ID") values ('123');

And it doesn't matter if you passed just the numeric value: 123 or the string value: '123'

edited Aug 19, 2014 at 20:09

answered Aug 18, 2014 at 14:37

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

6 Comments

user2874437 Over a year ago

Quotes are missing for third column: INSERT INTO Location VALUES (53, 'BUS', '424', 45); INSERT INTO Location VALUES (54, 'BUS', '402', 35); INSERT INTO Location VALUES (55, 'BUS', '433', 100);

neshkeev Over a year ago

@LeszekRymszewicz your are wrong, we don't need to explicitly convert number to varchar2, just checked it

user2874437 Over a year ago

Checked, You are right, quotes can be omitted and Oracle accepts it.

Bacs Over a year ago

Yes, Oracle is capable of all sorts of clever implicit conversions. You can read about them here, together with a list of the reasons why Oracle recommends you specify conversions explicitly: docs.oracle.com/cd/E11882_01/server.112/e26088/…

user330315 Over a year ago

@zaratustra: but it's good coding style to use proper literals. Relying on implicit data type conversion is begging for problems in the long run.

|

Collectives™ on Stack Overflow

Oracle Insert Script Error

2 Answers 2

6 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related