plsql - 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!

question from:https://stackoverflow.com/questions/66045898/can-you-use-object-types-in-procedures-in-oracle-pl-sql

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;
/