Scribd
Upload
7 views6 pages

Oracle SQL Table Management Errors

The document details a series of SQL commands executed in Oracle SQL*Plus, including creating, modifying, and dropping tables, specifically focusing on a table named 'library'. It highlights various errors encountered during the execution, such as missing parentheses, invalid identifiers, and issues with data types and constraints. The document concludes with successful data insertion and manipulation commands, demonstrating the process of managing a database table.

Uploaded by

marulasiddeshks
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
7 views6 pages

Oracle SQL Table Management Errors

The document details a series of SQL commands executed in Oracle SQL*Plus, including creating, modifying, and dropping tables, specifically focusing on a table named 'library'. It highlights various errors encountered during the execution, such as missing parentheses, invalid identifiers, and issues with data types and constraints. The document concludes with successful data insertion and manipulation commands, demonstrating the process of managing a database table.

Uploaded by

marulasiddeshks
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SQL*Plus: Release [Link].

0 - Production on Wed Dec 14 20:32:20 2022

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release [Link].0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table deparment(id number(4) primary key,name varchar(10) not null
2 ,email varchar(15)
3 ,phone number(10) unique not null
4 ;

*
ERROR at line 4:
ORA-00907: missing right parenthesis

SQL> create table deparment(id number(4) primary key,name varchar(10) not null
2 ,email varchar(15)
3 ,phone number(10) unique not null check(length(phone)=10)
4 address varchar(20) not null);
address varchar(20) not null)
*
ERROR at line 4:
ORA-00907: missing right parenthesis

SQL> create table deparment(id number(4) primary key,name varchar(10) not null
2 ,email varchar(15)
3 ,phone number(10) unique not null check(length(phone)=10)
4 ,address varchar(20) not null);

Table created.

SQL> desc department;


ERROR:
ORA-04043: object department does not exist

SQL> desc department


ERROR:
ORA-04043: object department does not exist

SQL> desc deparment;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(15)
PHONE NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)

SQL> rename deparment to library;

Table renamed.
SQL> desc deparment;
ERROR:
ORA-04043: object deparment does not exist

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(15)
PHONE NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)

SQL> alter table library


2 add age number(2) not null;

Table altered.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(15)
PHONE NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(2)

SQL> alter table library


2 rename column phone to mob;

Table altered.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(15)
MOB NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(2)

SQL> alter table library


2 modify email not null;

Table altered.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL NOT NULL VARCHAR2(15)
MOB NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(2)
SQL> alter table library
2 modify email char(10);

Table altered.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL NOT NULL CHAR(10)
MOB NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(2)

SQL> alter table libray


2 drop column address;
drop column address
*
ERROR at line 2:
ORA-00904: "ADDRESS": invalid identifier

SQL> alter table library


2 drop column address;

Table altered.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL NOT NULL CHAR(10)
MOB NOT NULL NUMBER(10)
AGE NOT NULL NUMBER(2)

SQL> drop table library


2 ;

Table dropped.

SQL> drop table avengers;

Table dropped.

SQL> desc avengers;


ERROR:
ORA-04043: object avengers does not exist

SQL> flashback table library to before drop;

Flashback complete.

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL NOT NULL CHAR(10)
MOB NOT NULL NUMBER(10)
AGE NOT NULL NUMBER(2)

SQL> flashback table avengers to before drop;

Flashback complete.

SQL> desc avengers;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL VARCHAR2(15)
PHONE NOT NULL NUMBER(10)
ADDRESS NOT NULL VARCHAR2(20)

SQL> select * from avengers;

no rows selected

SQL> insert into library values(1,'ranju','r@gmail',8758574847,22);

1 row created.

SQL> insert all


2 into library values(2,'moni','m@gmail',8574647438,66)
3 into library values(3,'aishu',a@gmail',6474849484,22)
4 into library values(4,'monu','h@gmail',7584763524,33)
5 select* from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert all


2 into library values(2,'moni','m@gmail',8574647438,66)
3 into library values(3,'aishu','a@gmail',6474849484,22)
4 into library values(4,'monu','h@gmail',7584763524,33)
5 select* from dual;

3 rows created.

SQL> select*from library;

ID NAME EMAIL MOB AGE


---------- ---------- ---------- ---------- ----------
1 ranju r@gmail 8758574847 22
2 moni m@gmail 8574647438 66
3 aishu a@gmail 6474849484 22
4 monu h@gmail 7584763524 33

SQL> drop table avengers;

Table dropped.

SQL> purge table avengers;

Table purged.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
RANJU TABLE
LIBRARY TABLE
LIBRAY TABLE
CLASSROMS TABLE
COMPANIES TABLE
OFFICES TABLE
CONSULATMT TABLE

11 rows selected.

SQL>

SQL> tr

SQL> truncate table library;

Table truncated.

SQL> select* from library;

no rows selected

SQL> desc library;


Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(4)
NAME NOT NULL VARCHAR2(10)
EMAIL NOT NULL CHAR(10)
MOB NOT NULL NUMBER(10)
AGE NOT NULL NUMBER(2)

SQL> insert into display values (420,'jocky',null 1234567899,19);


insert into display values (420,'jocky',null 1234567899,19)
*
ERROR at line 1:
ORA-00917: missing comma

SQL> into library values(4,'monu','h@gmail',7584763524,33)


SP2-0734: unknown command beginning "into libra..." - rest of line ignored.
SQL> select* from dual;insert into display values (420,'jocky',null 1234567899,19)
2

SQL> insert into display values (420,'jocky',null,3545454545,45);


insert into display values (420,'jocky',null,3545454545,45)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into library(420,'jocky',null, 3567890654,45);
insert into library(420,'jocky',null, 3567890654,45)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into library(420,'jocky',null,1234567891,34);


insert into library(420,'jocky',null,1234567891,34)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into library values(420,'jocky',null,1234567891,34);


insert into library values(420,'jocky',null,1234567891,34)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."LIBRARY"."EMAIL")

SQL> insert into library values(420,'jocky',null,2345678938,22)


2 ;
insert into library values(420,'jocky',null,2345678938,22)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."LIBRARY"."EMAIL")

SQL> insert all


2 into library values(2,'moni','m@gmail',8574647438,66)
3 into library values(3,'aishu',a@gmail',6474849484,22)
4 select*from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> insert into library values('sudha',237,6789543212,22,null);


insert into library values('sudha',237,6789543212,22,null)
*
ERROR at line 1:
ORA-01722: invalid number

SQL> insert into library values(1901,'sudha','sudha@gmail',2345678934,23);


insert into library values(1901,'sudha','sudha@gmail',2345678934,23)
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."LIBRARY"."EMAIL" (actual: 11,
maximum: 10)

You might also like