Can you use Object Types in Procedures in Oracle PL/SQL?

Question

Hello fellow programmers. Im currently working on a webshop database for my studying program. Currently im trying to make a procedure which creates an order in the orders table for a customer when he/she/it is beeing created. I am also thinking of putting this into a constructor but since i want to use this functionality twice once when the order reaches a certain status and after creation i want to bundle this functionality in a procedure. I have spend nearly 8 hours of research and testing on this but since the feedback from oracle db on my code is 0 to nothing i cant figure out what is wrong. When i create the procedure it is not flagged as valid and i cant even see the parameters in the parameters tab when i click on the procedure. I hope the code formatting works this is my first post..

This are the types order and Customer which hold a REF to each other

CREATE TYPE ORDER_TYPE AS OBJECT(
Order_Id NUMBER,
Date_of_Creation DATE,
Items ITEM_LIST,
Status REF STATUS_TYPE,
Customer REF CUSTOMER_TYPE
);

CREATE TYPE CUSTOMER_TYPE AS OBJECT(
Customer_Id NUMBER,
Email VARCHAR2(254),
User_Name VARCHAR2(50),
Password VARCHAR2(20),
First_Name VARCHAR2(50),
Last_Name VARCHAR2(50),
Address ADDRESS_TYPE,
Shopping_Cart REF ORDER_TYPE
);

CREATE TABLE Orders OF ORDER_TYPE(Status SCOPE IS Order_Status NOT NULL,        Customer NOT NULL)
NESTED TABLE Items STORE AS ORDER_ITEMS_NT_TAB;
ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY(Order_Id);
CREATE TABLE Customers OF CUSTOMER_TYPE(Customer_Id PRIMARY KEY,
                                    Email NOT NULL,
                                    User_Name NOT NULL,
                                    Password NOT NULL,
                                    First_Name NOT NULL,
                                    Last_Name NOT NULL,
                                    Address NOT NULL);

This is the procedure code. The input should be the customer created or updated. Then i want to insert a new order, i still have to change the id field to guid or uuid so every order will be unique but for testing purpose i just used 1. The item list should be empty at first and the status of the order should be status 1 which stands for "Shopping_Cart" this means that the order is still beeing created and should be displayed as shopping cart in the browser later on. After the insert i want to return the inserted row with the returning into statement so i cant update the customer and set the ref of his shopping cart to the new inserted order. I cant figure out whats wrong im still working on it but i would be greatful for any help.

CREATE PROCEDURE create_customer_order(customer IN CUSTOMER_TYPE) AS
DECLARE 
    shopping_c NUMBER;
BEGIN
    INSERT INTO ORDERS 
    VALUES(1,CURRENT_DATE ,NEW ITEM_LIST(),(SELECT REF(os) FROM ORDER_STATUS os WHERE VALUE(os).STATUS_ID = 1),REF(customer)) 
    RETURNING Order_Id INTO shopping_c;
    
    UPDATE CUSTOMERS c  
    SET c.SHOPPING_CART = (SELECT REF(o) FROM ORDERS o WHERE o.ORDER_ID = shopping_c) 
    WHERE c.CUSTOMER_ID = customer.CUSTOMER_ID;
END;

Feel free to ask questions if something is not clear. Cheers!

Answer 1

I will include a working example. But first, you did not provide all types. So i assumed them. I see you have types that refer to eachother. This is basically not a good idea. For storing data, you could use object types, but you could also use normal data types like number/varchar2. You would need tables orders/order_items/customers. If you want to make changes to the customer_type and your table is already populated with data, changing the type is difficult (what to do with the old data?).

But to come back at your question, here is a working example.

drop type customer_type force;
drop type order_type force;
drop type address_Type force;
drop type status_type force;
drop type item_list force;

create type STATUS_TYPE as object (
status number
);

create type ADDRESS_TYPE as object (
street varchar2(100)
);

create type ITEM_LIST as object (
itemname varchar2(100)
);

CREATE TYPE ORDER_TYPE AS OBJECT(
Order_Id NUMBER,
Date_of_Creation DATE,
Items ITEM_LIST,
Status REF STATUS_TYPE,
Customer REF CUSTOMER_TYPE
);

CREATE TYPE CUSTOMER_TYPE AS OBJECT(
Customer_Id NUMBER,
Email VARCHAR2(254),
User_Name VARCHAR2(50),
Password VARCHAR2(20),
First_Name VARCHAR2(50),
Last_Name VARCHAR2(50),
Address ADDRESS_TYPE,
Shopping_Cart REF ORDER_TYPE
);

alter type order_type compile;

drop table orders;

CREATE TABLE Orders (id number, Status status_type ,  Customer CUSTOMER_TYPE);

CREATE OR REPLACE PROCEDURE create_customer_order(p_customer IN CUSTOMER_TYPE) AS
    shopping_c NUMBER;
BEGIN
  dbms_output.enable(null);
    INSERT INTO ORDERS (id, status, customer)
    VALUES (1, null, p_customer)
    RETURNING id INTO shopping_c;

    commit;
  dbms_output.put_line('id='||shopping_c);
END;
/

--Test
declare
  l_customer customer_type;
begin
  l_customer := customer_type (Customer_Id => 1
                             , email=>'[email protected]'
                             , user_name=>'test'
                             , password=>'DoyouReallyWantThis'
                             , first_name=>'first'
                             , last_name =>'last'
                             , address=>null --for simplicity
                             , shopping_cart=>null --for simplicity
                             );
  create_customer_order(p_customer => l_customer);

end;
/

Answer 2

thanks for your answer.. It did not work for me and you also missed the update part in the procedure but still thank you for the effort. i managed to get the procedure working by running every code piece step by step and watching if it fails. Feels like javascript to me lol. I discovered for some reason i cant explain that you cant declare variables under the declare statement and you cant use defined object types as parameters. i could not find anything about it in the documentation. to everyone still interested how i solved the problem this is the code.

CREATE OR REPLACE PROCEDURE create_customer_order(p_customer_id IN NUMBER) 
AS
BEGIN
        INSERT INTO ORDERS 
        VALUES(1, CURRENT_DATE, NEW ITEM_LIST(),(SELECT REF(os) FROM ORDER_STATUS os WHERE os.STATUS_ID = 1),(SELECT REF(c) FROM CUSTOMERS c WHERE c.CUSTOMER_ID = p_customer_id));
        
        UPDATE CUSTOMERS c  
        SET c.SHOPPING_CART = (SELECT REF(o) FROM ORDERS o WHERE DEREF(o.Customer).Customer_Id = p_customer_id AND DEREF(o.STATUS).Status_Id = 1 ) 
        WHERE c.CUSTOMER_ID = p_customer_id;
END;