CODEWITHSUNDEEP
sqloracle-database
seth grayckowski
seth grayckowski

Reputation: 1

Oracle Insert Script Error

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);
`

Upvotes: 0

Views: 95

Answers (2)

neshkeev
neshkeev

Reputation: 6486

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'

Upvotes: 0

Bacs
Bacs

Reputation: 919

Your VARCHAR2 values need to be in single quotes:

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

etc...

Upvotes: 1

Related Questions