Slightly broad question here, but here goes
I’m trying to call an Oracle stored procedure, which returns a VARRAY which is of constructed from a ROWTYPE on one of my tables. For simplicity, lets say this table looks like the following :
MY_TABLE ID VALUE ----------- 1 John 2 Dave
so I will call a SP that returns the following VARRAY type :
CREATE OR REPLACE TYPE param_array is VARRAY(100) OF MY_TABLE%ROWTYPE;
According to the Oracle Documentation, you can extract this as an array, but my question is : what will the type of the array be, is it an array of Strings, name/value paired strings etc?
I’m creating some Java code that will take data out of this array, but I’m not sure which format it will be, such as
From the docs:
SQL>CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30) SQL>/
Then create the following function that returns a VARRAY.
CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY AS l_data EmpArray := EmpArray(); CURSOR c_emp IS SELECT ename FROM EMP; BEGIN FOR emp_rec IN c_emp LOOP l_data.extend; l_data(l_data.count) := emp_rec.ename; END LOOP; RETURN l_data; END;
It will return an array of strings (VARCHARs) max length 30
it returns emparray which is declared as a varray of varchars (strings)