CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Geralt
Geralt

Reputation: 180

How can I create a table type with multiple columns and use it in a stored procedure?

I created a table type to use it as input in a stored procedure, so that the procedure can receive multiple customer IDs at once. But now I also need to use other data related to the customers and I was wondering how I can use the type I created with multiple custom columns, and not only Customer ID.

This is how it is now:

CREATE TYPE T_CUSTOMERS IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE PR_SAMPLE (
    CUSTOMERS_LIST IN T_CUSTOMERS,
    C1 OUT SYS_REFCURSOR
)
IS
BEGIN
...
END;

Upvotes: 1

Views: 2273

Answers (1)

Popeye
Popeye

Reputation: 35900

You can create the OBJECT type and then create the table type containing the newly created objects.

CREATE TYPE T_CUSTOMERS_OBJ AS OBJECT (
    CUSTOMER_ID     NUMBER,
    CUSTOMER_NAME   VARCHAR2(300)
);
/

CREATE TYPE T_CUSTOMERS IS
    TABLE OF T_CUSTOMERS_OBJ;
/

Then you can use the T_CUSTOMERS type in your procedure.

Upvotes: 1

Related Questions