I’m working on an application that uses an external Oracle database. In this database, a TYPE COLLECTION has been created as follows:
CREATE OR REPLACE TYPE "TON_TYPE" AS TABLE OF NUMBER;
This TON_TYPE is used as an input parameter in a procedure also created in the database:
PROCEDURE SP_EXAMPLE(
abcId NUMBER,
defId NUMBER,
ghiIds TON_TYPE
) AS ...
I need to call this procedure from my application and pass a value for the TON_TYPE parameter. However, I’m struggling to define a variable of this collection type in an advanced SQL query to make the procedure call.
Could someone guide me on how to define and pass a value for this type collection in SQL? Any examples or best practices would be greatly appreciated!
Thank you in advance!
Dear Gustavo Resende
1. Initialize the Collection: Use TON_TYPE() to initialize an empty collection.
2. Populate the Collection: Use EXTEND to allocate space and assign values to its elements.
3. Pass to Procedure: Simply pass the collection variable as a parameter.
Here’s an example of how to do this in SQL:
DECLARE
-- Declare a variable of the collection type
v_ghiIds TON_TYPE := TON_TYPE();
BEGIN
-- Populate the collection
v_ghiIds.EXTEND(3); -- Extend the collection to hold 3 elements
v_ghiIds(1) := 101; -- Assign values
v_ghiIds(2) := 102;
v_ghiIds(3) := 103;
-- Call the procedure
SP_EXAMPLE(
abcId => 1,
defId => 2,
ghiIds => v_ghiIds
);
END;
Dear ndy,
Thank you for your response, but I was not successful. When I run it through Service Studio, I encounter the following error:
Database returned the following error: Error in advanced query TestProc: 'TON_TYPE ' is not a recognized CURSOR option.
**I believe it might not work as expected through Studio.
When running it through the application, I get the following error captured via Service Center:
ORA-06550: line 2, column 14: PLS-00201: identifier 'TON_TYPE' must be declared ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated I am unable to share the OML due to the organization's security policies. However, as a workaround, I have requested the creation of a new procedure that will transform the value into the required type using string split.
Call the Procedure:
:abcId,
:defId,
Populate_TON_TYPE(:ghiIds) -- `Populate_TON_TYPE` is name of the procedure