Oracle Database 21c Enterprise Edition Release 21.0.0.0.

0 - Production

Version 21.3.0.0.0

SQL> create user c##Hiru identified by 2122;

User created.

SQL> grant create session to c##Hiru;

Grant succeeded.

SQL> grant privilege to c##Hiru;

grant privilege to c##Hiru

ERROR at line 1:

ORA-01919: role 'PRIVILEGE' does not exist

SQL> grant all privilege to c##Hiru;

grant all privilege to c##Hiru

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant all privileges to c##Hiru;

Grant succeeded.

SQL>

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jan 25 12:01:15 2024

Version 21.3.0.0.0

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

Enter user-name: c##Hiru

Enter password:

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0
creating table

SQL> CREATE TYPE AddressType1 AS OBJECT (

2 Street VARCHAR2(100),

3 City VARCHAR2(50),

4 State VARCHAR2(50),

5 ZipCode VARCHAR2(10)

6 );

7 /

Type created.

SQL> CREATE TABLE Customers3 (

2 CustomerID NUMBER PRIMARY KEY,

3 CustomerName VARCHAR2(50) NOT NULL,

4 ShippingAddress AddressType1,

5 BillingAddress AddressType1
 6 );

Table created.

SQL> CREATE TABLE Suppliers3 (

2 SupplierID NUMBER PRIMARY KEY,

3 SupplierName VARCHAR2(50) NOT NULL,

4 ContactName VARCHAR2(50),

5 ContactEmail VARCHAR2(100),

6 Address AddressType1,

7 CONSTRAINT CK_Suppliers3_ContactName CHECK (ContactName IS

NOT NULL)

8 );

Table created.

SQL> CREATE TABLE Products3 (

2 ProductID NUMBER PRIMARY KEY,

3 ProductName VARCHAR2(50) NOT NULL,

4 Quantity NUMBER,

5 Price NUMBER(10,2),

6 SupplierID NUMBER,

7 CONSTRAINT FK_Products3_Suppliers FOREIGN KEY (SupplierID) REFERENCES Suppliers3 (SupplierID),

8 CONSTRAINT CK_Products3_Quantity CHECK (Quantity >= 0)

9 );
Table created.

SQL> CREATE TABLE Orders3 (

2 OrderID NUMBER PRIMARY KEY,

3 CustomerID NUMBER,

4 OrderDate DATE,

5 CONSTRAINT FK_Orders3_Customers FOREIGN KEY (CustomerID) R

EFERENCES Customers3 (CustomerID),

SQL> CREATE TABLE Orders3 (

2 OrderID NUMBER PRIMARY KEY,

3 CustomerID NUMBER,

4 OrderDate DATE,

5 CONSTRAINT FK_Orders3_Customers FOREIGN KEY (CustomerID) REFERENCES Customers3

(CustomerID)

6 );

Table created.

SQL> CREATE TABLE OrderItems3 (

2 OrderItemID NUMBER PRIMARY KEY,

3 OrderID NUMBER,

4 ProductID NUMBER,

5 Quantity NUMBER,

6 CONSTRAINT FK_OrderItems3_Orders FOREIGN KEY (OrderID) REFE

RENCES Orders3 (OrderID),

7 CONSTRAINT FK_OrderItems3_Products FOREIGN KEY (ProductID)

REFERENCES Products3 (ProductID),

8 CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity >= 0)

9 );

Table created.

SQL> CREATE TABLE Inventory3 (

2 InventoryID NUMBER PRIMARY KEY,

3 ProductID NUMBER,

4 Quantity NUMBER,

5 CONSTRAINT FK_Inventory3_Products FOREIGN KEY (ProductID)

REFERENCES Products3 (ProductID),

6 CONSTRAINT CK_Inventory_Quantity CHECK (Quantity >= 0)

7 );

Table created.

SQL>

creating view
SQL> CREATE VIEW Customers3View AS

2 SELECT * FROM Customers3;

View created.

SQL> CREATE VIEW Suppliers3View AS

2 3 CR

SQL> CREATE VIEW Suppliers3View AS

2 SELECT * FROM Suppliers3;

View created.

SQL> CREATE VIEW Products3View AS

2 SELECT * FROM Products3;

View created.

SQL> CREATE VIEW Orders3View AS

2 SELECT * FROM Orders3;

View created.

SQL> CREATE VIEW OrderItems3View AS

2 SELECT * FROM OrderItems3;

View created.

SQL> CREATE VIEW Inventory3View AS

2 SELECT * FROM Inventory3;

View created.

SQL>

stored procedure
SQL> CREATE OR REPLACE PROCEDURE GetCustomers3

2 AS

3 v_CustomerInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_CustomerInfo FOR

6 SELECT * FROM Customers3;

7 END;

8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GetSuppliers3

2 AS

3 BEGIN

4 OPEN v_SupplierInfo FOR

5 BEGIN

8 /

Warning: Procedure created with compilation errors.

SQL> CREATE OR REPLACE PROCEDURE GetSuppliers3

2 AS

3 v_SupplierInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_SupplierInfo FOR

6 SELECT * FROM Suppliers3;

7 END;

8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GetProducts3

2 AS

3 v_ProductInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_ProductInfo FOR

6 SELECT * FROM Products3;

7 END;

8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GetOrders3

2 AS
 3 v_OrderInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_OrderInfo FOR

6 SELECT * FROM Orders3;

7 END;

8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GetOrderItems3

2 AS

3 v_OrderItemInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_OrderItemInfo FOR

6 SELECT * FROM OrderItems3;

7 END;

8 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE GetInventory3

2 AS

3 v_InventoryItemInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_InventoryItemInfo FOR

 6 SELECT * FROM InventoryItems3;

7 END;

8 /

Warning: Procedure created with compilation errors.

SQL> CREATE OR REPLACE PROCEDURE GetInventory3

2 AS

3 v_InventoryItemInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_InventoryItemInfo FOR

6 SELECT * FROM Inventory3Items;

7 END;

8 /

Warning: Procedure created with compilation errors.

SQL> CREATE OR REPLACE PROCEDURE GetInventory3

2 AS

3 v_InventoryInfo SYS_REFCURSOR;

4 BEGIN

5 OPEN v_InventoryInfo FOR

6 SELECT * FROM Inventory3;

7 END;

8 /
Procedure created.

SQL>

SQL> CREATE OR REPLACE TRIGGER trg_customers3

2 AFTER INSERT ON Customers3

3 FOR EACH ROW

4 BEGIN

5 END;

6 /

Warning: Trigger created with compilation errors.

SQL> SELECT* FROM customer3;

SELECT* FROM customer3

 *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> desc customer3;

ERROR:

ORA-04043: object customer3 does not exist

SQL> desc Customer3;

ERROR:

ORA-04043: object Customer3 does not exist

SQL> SELECT *

2 FROM all_tab_columns

3 WHERE table_name = 'Customer3'

4 AND owner = 'Hiru';

no rows selected

SQL> INSERT INTO Customers3 (CustomerID, CustomerName, ShippingAddress, BillingAddress)

2 VALUES (1, 'Heran

Abebe',AddressType1('dc','washngton','a12'),AddressType1('dc','washngton','111'));

VALUES (1, 'Heran Abebe',AddressType1('dc','washngton','a12'),AddressType1('dc','washngton','111'))

 *

ERROR at line 2:

ORA-02315: incorrect number of arguments for default constructor

SQL> INSERT INTO Customers3

2 VALUES (1, 'Heran

Abebe',AddressType1('dc','washngton','a12'),AddressType1('dc','washngton','111'));

VALUES (1, 'Heran Abebe',AddressType1('dc','washngton','a12'),AddressType1('dc','washngton','111'))

ERROR at line 2:

ORA-02315: incorrect number of arguments for default constructor

SQL> INSERT INTO Customers3

2 VALUES (1, 'Heran

Abebe',AddressType1('texas','dc','washngton','a12'),AddressType1('texas','dc','washngton','111'));

INSERT INTO Customers3

ERROR at line 1:

ORA-04098: trigger 'C##HIRU.TRG_CUSTOMERS3' is invalid and failed re-validation

SQL> INSERT INTO Suppliers3 (SupplierID, SupplierName, ContactName, ContactEmail, Address)

2 VALUES (

3 1,
 4 'Supplier1',

5 'John Smith',

6 'john.smith@example.com',

7 AddressType1('123 Main St', 'City1', 'State1', '12345')

8 );

1 row created.

SQL> select*from Suppliers3;

SUPPLIERID SUPPLIERNAME

---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

1 Supplier1

John Smith

john.smith@example.com

ADDRESSTYPE1('123 Main St', 'City1', 'State1', '12345')

SQL> INSERT INTO Suppliers3 (SupplierID, SupplierName, ContactName, ContactEmail, Address)

2 VALUES (

3 2,

4 'Supplier2',

5 'Hana Abebe',

6 'hana.abebe@gmail.com',

7 AddressType1('kolfe', 'addis abeba', 'addis abeba', '123'));

1 row created.

SQL> INSERT INTO Suppliers3 (SupplierID, SupplierName, ContactName, ContactEmail, Address)

2 VALUES (

3 3,

4 'Supplier3',

5 'Abel Getahun',

6 'abel.gech@gmail.com',

7 AddressType1('gebriel', 'hawassa', 'hawassa', '321'));

1 row created.

SQL> INSERT INTO Products3 (ProductID, ProductName, Quantity, Price, SupplierID)

2 VALUES (

3 1,

4 'Product1',

5 10,
 6 99.99,

7 1

8 );

1 row created.

SQL> INSERT INTO Products3 (ProductID, ProductName, Quantity, Price, SupplierID)

2 'Product2',

3 11,

4 90.99,

5 2

6 );

'Product2',

ERROR at line 2:

ORA-00926: missing VALUES keyword

SQL> INSERT INTO Products3 (ProductID, ProductName, Quantity, Price, SupplierID)

2 VALUES(

3 2,

4 'product2',

5 11,

6 1000.00,

7 2
 8 );

1 row created.

SQL> INSERT INTO Products3 (ProductID, ProductName, Quantity, Price, SupplierID)

2 VALUES(

3 3,

4 'product3',

5 12,

6 8888.93,

7 3

8 );

1 row created.

SQL> INSERT INTO Customers3 (CustomerID, CustomerName, ShippingAddress, BillingAddress)

2 VALUES (

3 1,

4 'John Doe',

5 AddressType1('123 Main St', 'City1', 'State1', '12345'),

6 AddressType1('456 Elm St', 'City2', 'State2', '67890')

7 );

INSERT INTO Customers3 (CustomerID, CustomerName, ShippingAddress, BillingAddress)

ERROR at line 1:
ORA-04098: trigger 'C##HIRU.TRG_CUSTOMERS3' is invalid and failed re-validation

SQL> INSERT INTO Customers3 (CustomerID, CustomerName, ShippingAddress, BillingAddress)

2 VALUES (

3 1,

4 'John Doe',

5 AddressType1('123 Main St', 'City1', 'State1', '12345'),

6 AddressType1('456 Elm St', 'City2', 'State2', '67890')

7 );

INSERT INTO Customers3 (CustomerID, CustomerName, ShippingAddress, BillingAddress)

ERROR at line 1:

ORA-04098: trigger 'C##HIRU.TRG_CUSTOMERS3' is invalid and failed re-validation

SQL> CREATE VIEW ProductSupplierView AS

2 SELECT

3 p.ProductID,

4 p.ProductName,
 5 p.Quantity,

6 p.Price,

7 s.SupplierID,

8 s.SupplierName,

9 s.ContactName,

10 s.ContactEmail,

11 s.Address

12 FROM

13 Products3 p

14 JOIN Suppliers3 s ON p.SupplierID = s.SupplierID;

View created.

SQL> SELECT * FROM ProductSupplierView;

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------
 1 Product1

10

99.99 1 Supplier1

John Smith

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

john.smith@example.com

ADDRESSTYPE1('123 Main St', 'City1', 'State1', '12345')

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------
CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

2 product2

11

1000 2 Supplier2

Hana Abebe

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

hana.abebe@gmail.com

ADDRESSTYPE1('kolfe', 'addis abeba', 'addis abeba', '123')

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

 PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

3 product3

12

8888.93 3 Supplier3

Abel Getahun

PRODUCTID PRODUCTNAME QUANTITY

---------- -------------------------------------------------- ----------

PRICE SUPPLIERID SUPPLIERNAME

---------- ---------- --------------------------------------------------

CONTACTNAME

--------------------------------------------------

CONTACTEMAIL

--------------------------------------------------------------------------------

ADDRESS(STREET, CITY, STATE, ZIPCODE)

--------------------------------------------------------------------------------

abel.gech@gmail.com

ADDRESSTYPE1('gebriel', 'hawassa', 'hawassa', '321')

SQL>