SQL*Plus: Release 21.0.0.0.
0 - Production on Mon Oct 23 15:15:21 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Last Successful login time: Sat Oct 21 2023 21:42:35 +05:30
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> create table depositor(acc_no number,
2 name varchar2(25),
3 PAN number,
4 balance float);
Table created.
SQL> create view view_depositor(acc_no number, PAN number);
create view view_depositor(acc_no number, PAN number)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> CREATE VIEW view_name AS
2 SELECT column1, column2, ...
3 FROM table_name
4 /
SELECT column1, column2, ...
*
ERROR at line 2:
ORA-00936: missing expression
SQL> create or replace view view_depositor
2 as
3 select acc_no, PAN from employee
4 where balance > 10000
5 with read only;
where balance > 10000
*
ERROR at line 4:
ORA-00904: "BALANCE": invalid identifier
SQL> select * from employee;
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
101 Jeevika Mumbai F 19 9876543210
41000 10
102 Kirti Mumbai F 19 8765432109
41000 20
103 Hemant Pune M 20 7654321098
42000 30
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
104 Jatin Kolkatta M 21 6543210987
43000 40
105 Harshita Delhi F 18 5432109876
42000 101 10
106 Kritika Banglore F 19 4321098765
43000 102 20
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
107 Sahil Ahemdabad M 20 3210987654
41000 104 40
108 Muskan Pune F 21 9876543211
44000 101 10
109 Priyanka Delhi F 20 8765432112
43000 102 20
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
110 Pooja Mumbai F 21 8765432113
44000 104 40
111 Daksh Mumbai M 19 9876554433
41000 101 10
112 Dhruwal Kolkatta M 20 8765443322
42000 102 20
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
113 Aastha Ahemdabad F 20 7654322111
43000 103 30
114 Kush Chennai M 23 6543211444
41000 104 40
115 Hiral Banglore F 22 9876665544
42000 103 30
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
116 Mohit Delhi M 22 8765656565
43000 102 20
117 Jiya Delhi F 19 6565654545
41000 104 40
118 Jovita Mumbai F 19 9876543222
41000 101 10
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
119 Vansh Mumbai M 19 8765430909
41000 102 20
120 Devesh Pune M 21 7654321218
42000 103 30
121 Gaurav Kolkatta M 22 6543210909
43000 104 40
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
122 Hardik Delhi M 20 5432109898
42000 101 10
123 Harshit Banglore M 21 4321098787
43000 102 20
124 Hirday Ahemdabad M 22 3210987676
41000 104 40
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
125 Tisha Pune F 21 9876543232
44000 101 10
126 Krishna Delhi M 20 8765432222
43000 102 20
127 Diksha Mumbai F 21 8765433333
44000 104 40
ENO ENAME CITY G AGE PHONE
---------- -------------------- --------------- - ---------- ----------
SALARY MNO DNO
---------- ---------- ----------
128 Karan Mumbai M 21 8765444333
44000
28 rows selected.
SQL> select * from depositor;
no rows selected
SQL> insert into depositor values(101, 'ABC', 1345678, '12000');
1 row created.
SQL> insert into depositor values(102, 'DEF', 1345679, '14000');
1 row created.
SQL> insert into depositor values(103, 'GHF', 13452379, '15000');
1 row created.
SQL> insert into depositor values(104, 'XYZ', 13412345, '9000');;
insert into depositor values(104, 'XYZ', 13412345, '9000');
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> insert into depositor values(104, 'XYZ', 13412345, '9000');
1 row created.
SQL> insert into depositor values(105, 'EFG', 13412245, '19000');
1 row created.
SQL> select * from depositor;
ACC_NO NAME PAN BALANCE
---------- ------------------------- ---------- ----------
101 ABC 1345678 12000
102 DEF 1345679 14000
103 GHF 13452379 15000
104 XYZ 13412345 9000
105 EFG 13412245 19000
SQL> create or replace view view_depositor
2 as
3 select(acc_no, PAN) from depositor
4 where balance > 10000
5 with read only
6 ;
select(acc_no, PAN) from depositor
*
ERROR at line 3:
ORA-00907: missing right parenthesis
SQL> create or replace view view_depositor
2 as
3 select(acc_no, PAN) from depositor
4 where balance > 10000
5 with read only;
select(acc_no, PAN) from depositor
*
ERROR at line 3:
ORA-00907: missing right parenthesis
SQL> create or replace view view_depositor
2 as
3 select acc_no, PAN from depositor
4 where balance > 10000
5 with read only
6 with read only;
with read only
*
ERROR at line 6:
ORA-00933: SQL command not properly ended
SQL> create or replace view view_depositor
2 as
3 select acc_no, PAN from depositor
4 where balance > 10000
5 with read only;
View created.
SQL> create sequence seq_test1
2 start with 1
3 increment by 3
4 minvalue -11
5 maxvalue 11
6 cycle
7 cache;
cache
*
ERROR at line 7:
ORA-01722: invalid number
SQL> CREATE SEQUENCE customers_seq
2 START WITH 1000
3 INCREMENT BY 1
4 NOCACHE
5 NOCYCLE;
Sequence created.
SQL> create sequence seq_test1
2 start with 1
3 increment by 3
4 minvalue -11
5 maxvalue 11
6 ;
Sequence created.
SQL> create table test1(rollNO number, marks number);
Table created.
SQL> insert into test1 values(seq_test1.currvalue, 15);
insert into test1 values(seq_test1.currvalue, 15)
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> insert into test1 values(seq_test1.currvalue, 16);
insert into test1 values(seq_test1.currvalue, 16)
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> insert into test1 values(seq_test1.nextval, 16);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 15);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 12);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 10);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 19);
insert into test1 values(seq_test1.nextval, 19)
*
ERROR at line 1:
ORA-08004: sequence SEQ_TEST1.NEXTVAL exceeds MAXVALUE and cannot be
instantiated
SQL> select * from seq_test1;
select * from seq_test1
*
ERROR at line 1:
ORA-02201: sequence not allowed here
SQL> desc seq_test1;
SP2-0381: DESCRIBE sequence is not available
SQL> alter sequence seq_test1
2 increment by -3;
Sequence altered.
SQL> insert into test1 values(seq_test1.nextval, 10);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 20);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 13);
1 row created.
SQL> insert into test1 values(seq_test1.nextval, 17);
1 row created.
SQL> select * from test1;
ROLLNO MARKS
---------- ----------
1 16
4 15
7 12
10 10
7 10
4 20
1 13
-2 17
8 rows selected.
SQL> declare
2 no1 number := &no1;
3 begin
4 if no1%2 = 0
5 dbms_ouput.put_line('No is odd');
6 else
7 dbms_ouput.put_line('No is even');
8 end;
9 /
Enter value for no1: 12
old 2: no1 number := &no1;
new 2: no1 number := 12;
if no1%2 = 0
*
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "2" when expecting one of the following:
type <an identifier> <a double-quoted delimited-identifier>
SQL> declare
2 no number := &no1;
3 begin
4 if no1%2 = 0
5 dbms_ouput.put_line('No is even');
6 else
7 dbms_ouput.put_line('No is odd');
8 end;
9 /
Enter value for no1: 12
old 2: no number := &no1;
new 2: no number := 12;
if no1%2 = 0
*
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "2" when expecting one of the following:
type <an identifier> <a double-quoted delimited-identifier>
SQL> declare
no1 number := &no1;
no2 number := &no2;
begin
if no1 > no2
dbms_ouput.put_line(no1 || 'is greater than' || no2);
else
dbms_output.put_line(no2 || 'is greater than' || no1);
end;
/
Enter value for no1: 10
old 2: no1 number := &no1;
new 2: no1 number := 10;
Enter value for no2: 100
old 3: no2 number := &no2;
new 3: no2 number := 100;
dbms_ouput.put_line(no1 || 'is greater than' || no2);
*
ERROR at line 6:
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "DBMS_OUPUT" when expecting one of the
following:
. ( * @ % & - + / at mod remainder rem then
<an exponent (**)> and or || multiset
The symbol "." was substituted for "DBMS_OUPUT" to continue.
ORA-06550: line 6, column 53:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & - + / at mod remainder rem then <an exponent (**)>
and or || multiset
SQL> declare
2 no1 number := 1;
3 begin
4 Loop
5 dbms_output.put_line(no1);
6 no1 = no1+1;
7 Exit when (no1 = 10)
8 End loop;
9 end;
10 /
no1 = no1+1;
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.
ORA-06550: line 8, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between overlaps || multiset year day member
submultiset
The symbol ";" was substituted for "END" to continue.
SQL> declare
2 no1 number := 1;
3 begin
4 Loop
5 dbms_ouput.put_line(no1);
6 no1 := no1+1;
7 End loop;
8 end;
9 /
dbms_ouput.put_line(no1);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00201: identifier 'DBMS_OUPUT.PUT_LINE' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
SQL> declare
2 no1 number:= 1;
3 begin
4 Loop
5 dbms_ouput.put_line(no1);
6 no1:=no1+1;
7 /
no1:=no1+1;
*
ERROR at line 6:
ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
( begin case declare end exit for goto if loop mod null
pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge standard pipe
purge json_object
declare
no1 number := 1;
begin
Loop
dbms_output.put_line(no1);
no1:=no1+1;
End loop;
end;
/
declare
no1 number := &no1;
no2 number := &no2;
begin
if no1 = no2 then
dbms_output.put_line(no1 || 'and' || no2 || 'are equal');
elsif no1 > no2 then
dbms_output.put_line(no1 || 'is greater than' || no2);
else
dbms_output.put_line(no2 || 'is greater than' || no1);
end if;
end;
/
create or replace function percentage
(subject1 in number, subject2 in number, subject3 in number)
return float
is
percent float;
begin
percent := ((subject1 + subject2 + subject3) / 300 * 100);
return percent;
end;
/
