Oracle Live SQL - Script: SO 70416695

Statement 1

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 2

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL, 
                CONSTRAINT id_mfr_fk 
                           FOREIGN KEY(id_mfr) 
                           REFERENCES Manufacturer,            
                  )

ORA-00904: : invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 3

    CREATE SYNONYM sw for Software

Synonym created.

Statement 4

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 5

Unsupported Command

Statement 6

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 7

Unsupported Command

Statement 8

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 9

Unsupported Command

Statement 10

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 11

drop table users

Table dropped.

Statement 12

drop table software

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 13

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 14

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL, 
                CONSTRAINT id_mfr_fk 
                           FOREIGN KEY(id_mfr) 
                           REFERENCES Manufacturer,            
                  )

ORA-00904: : invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 15

    CREATE SYNONYM sw for Software

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 16

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 17

Unsupported Command

Statement 18

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 19

Unsupported Command

Statement 20

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 21

Unsupported Command

Statement 22

drop table users

Table dropped.

Statement 23

drop table software

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 24

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 25

drop table users

Table dropped.

Statement 26

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 27

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL, 
                CONSTRAINT id_mfr_fk 
                           FOREIGN KEY(id_mfr) 
                           REFERENCES Manufacturer,            
                  )

ORA-00904: : invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 28

    CREATE SYNONYM sw for Software

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 29

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 30

Unsupported Command

Statement 31

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 32

Unsupported Command

Statement 33

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 34

Unsupported Command

Statement 35

drop table users

Table dropped.

Statement 36

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 37

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL 
                  )

Table created.

Statement 38

    CREATE SYNONYM sw for Software

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 39

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

Table created.

Statement 40

Unsupported Command

Statement 41

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

View created.

Statement 42

Unsupported Command

Statement 43

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 44

Unsupported Command

Statement 45

drop table users

ORA-02449: unique/primary keys in table referenced by foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02449

Statement 46

drop table software

ORA-02449: unique/primary keys in table referenced by foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02449

Statement 47

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 48

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL 
                  )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 49

    CREATE or replace SYNONYM sw for Software

Synonym created.

Statement 50

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 51

Unsupported Command

Statement 52

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

View created.

Statement 53

Unsupported Command

Statement 54

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 55

Unsupported Command

Statement 56

drop table software

ORA-02449: unique/primary keys in table referenced by foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02449

Statement 57

drop table users

ORA-02449: unique/primary keys in table referenced by foreign keys

More Details: https://docs.oracle.com/error-help/db/ora-02449

Statement 58

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 59

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL 
                  )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 60

    CREATE or replace SYNONYM sw for Software

Synonym created.

Statement 61

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 62

Unsupported Command

Statement 63

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

View created.

Statement 64

Unsupported Command

Statement 65

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 66

Unsupported Command

Statement 67

drop table sales

Table dropped.

Statement 68

drop table software

Table dropped.

Statement 69

drop table users

Table dropped.

Statement 70

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 71

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL 
                  )

Table created.

Statement 72

    CREATE or replace SYNONYM sw for Software

Synonym created.

Statement 73

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

Table created.

Statement 74

Unsupported Command

Statement 75

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

View created.

Statement 76

Unsupported Command

Statement 77

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 78

Unsupported Command

Statement 79

drop table sales

Table dropped.

Statement 80

drop table software

Table dropped.

Statement 81

drop table users

Table dropped.

Statement 82

CREATE TABLE Users(id_user INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                surname VARCHAR2(30) NOT NULL, 
                name VARCHAR2(30) NOT NULL, 
                patronymic VARCHAR2(30) NULL, 
                tel_no VARCHAR2(17) NOT NULL, 
               
                CONSTRAINT ch_telno_users 
                           CHECK(REGEXP_LIKE(tel_no, '^\+375\(\d{2}\)\d{3}-\d{2}-\d{2}$')), 
                CONSTRAINT uni_telno_users 
                           UNIQUE(tel_no) 
                 )

Table created.

Statement 83

CREATE TABLE Software(id_sw INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1 NOCACHE) PRIMARY KEY, 
                  name_sw VARCHAR2(40) NOT NULL, 
                  version VARCHAR2(10) NULL, 
                  license_period VARCHAR2(10) NOT NULL, 
                  id_mfr INTEGER NOT NULL, 
                  release_date DATE NOT NULL, 
                  price NUMBER(9,2), 
                  total_amount INTEGER NOT NULL 
                  )

Table created.

Statement 84

    CREATE or replace SYNONYM sw for Software

Synonym created.

Statement 85

CREATE TABLE Sales(id_sale INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
               id_user INTEGER NOT NULL, 
               id_sw INTEGER NOT NULL, 
               quantity INTEGER NOT NULL, 
               total_cost NUMBER(9,2), 
               order_date DATE NOT NULL CHECK (order_date >= to_date('01/01/2019', 'dd/mm/yyyy')), 
               expiration_date DATE NOT NULL, 
             CONSTRAINT id_user_fk 
                        FOREIGN KEY(id_user) 
                        REFERENCES Users, 
             CONSTRAINT id_sw_fk 
                        FOREIGN KEY(id_sw) 
                        REFERENCES Software 
            )

Table created.

Statement 86

    CREATE OR REPLACE VIEW SalesView AS 
    SELECT Sales.id_sale,sw.name_sw||' '||sw.version sw_full_name,         
    Users.surname||' '||Users.name||' '||Users.patronymic user_full_name,   
    sales.quantity,         sales.order_date 
    FROM sw INNER JOIN (Users INNER JOIN Sales ON Sales.id_user = Users.id_user)  ON sw.id_sw = sales.id_sw

View created.

Statement 87

        CREATE OR REPLACE TRIGGER sales_view_instead_of_trig INSTEAD OF 
    UPDATE OR INSERT ON salesview 
    FOR EACH ROW 
      DECLARE 
      sw_new        sw.id_sw%TYPE; 
      user_new      Users2.id_user%TYPE; 
      check_excep_sw   VARCHAR2(51); 
      check_excep_user VARCHAR2(92); 
    BEGIN 
        IF updating THEN --* update 
    IF :new.sw_full_name != :old.sw_full_name THEN 
        SELECT 
            sw.name_sw||' '||sw.version 
        INTO check_excep_sw 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :new.sw_full_name; 
      ELSE 
        SELECT 
            id_sw 
        INTO sw_new 
        FROM 
            sw 
        WHERE 
            sw.name_sw||' '||sw.version = :old.sw_full_name; 
       END IF; 
       IF :new.user_full_name != :old.user_full_name THEN 
        SELECT 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic 
        INTO check_excep_user 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
       ELSE 
        SELECT 
            id_user 
        INTO user_new 
        FROM 
            Users2 
        WHERE 
            Users2.surname||' '||Users2.name||' '||Users2.patronymic = :old.user_full_name; 
        END IF; 
       UPDATE sales 
       SET 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
    WHERE 
        id_sale = :old.id_sale; 
   END IF; 
    IF inserting THEN --* insert 
   SELECT sw.name_sw||' '||sw.version INTO check_excep_sw 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
        SELECT id_sw 
        INTO sw_new 
        FROM sw 
        WHERE sw.name_sw||' '||sw.version = :new.sw_full_name; 
    SELECT 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic 
    INTO check_excep_user 
    FROM 
        Users2 
    WHERE 
        Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
     SELECT id_user 
        INTO user_new 
        FROM Users2 
        WHERE Users2.surname||' '||Users2.name||' '||Users2.patronymic = :new.user_full_name; 
    INSERT INTO sales( 
        id_user, 
        id_sw, 
        quantity, 
        order_date, 
        expiration_date, 
        total_cost 
     ) VALUES ( 
        id_user = user_new, 
        id_sw = sw_new, 
        quantity = :new.quantity, 
        order_date = :new.order_date, 
        expiration_date = :new.expiration_date, 
        total_cost = :new.total_cost 
     ); 
        END IF; 
    EXCEPTION 
        WHEN no_data_found THEN 
    dbms_output.put_line('-------------------------------------------'); 
    dbms_output.put_line('| ERROR!   |'); 
    dbms_output.put_line('-------------------------------------------'); 
    END;

Errors: TRIGGER SALES_VIEW_INSTEAD_OF_TRIG
Line/Col: 62/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 63/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'
Line/Col: 86/5 PL/SQL: SQL Statement ignored
Line/Col: 94/17 PL/SQL: ORA-00917: missing comma
Line/Col: 98/27 PLS-00049: bad bind variable 'NEW.EXPIRATION_DATE'
Line/Col: 99/22 PLS-00049: bad bind variable 'NEW.TOTAL_COST'

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 88

Unsupported Command

SO 70416695

Additional Information