CODEWITHSUNDEEP
sqloracle-database
Thanthla
Thanthla

Reputation: 586

How to return multiple values from a function without global types

In an application GUI-Elements can be initialized by using SQL statements.

SELECT name $GUIElement
FROM myTable
WHERE id = 1337;

Since the underlying database structure may change, I am prefering to hide the structure and use an Oracle Package as interface instead. For single values this approach works well with functions like:

SELECT myPackage.getNameByID(1337) $GUIElement
FROM DUAL;

Now I am facing the issue of initializing a list. Original Code:

SELECT name $GUIList
FROM myTable;

To return multiple values from a function I need to define a new SQL type. If the function returns a TABLE OF VARCHAR2 this code works:

SELECT COLUMN_VALUE $GUIList 
FROM TABLE(myPackage.getNames())

Unfortunately, I have no rights to create new types on the productive database. So, is there a way in Oracle to get a similar behaviour without defining a new data type? I have looked into SYS_REFCURSERbut it seems not to work properly.

Upvotes: 0

Views: 261

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6356

What about xmltype. The function always returns xml type. And for processing this object you are using xmltable.

Something like this. 

create or replace function return_params return xmltype is
 result xmltype;
begin 

select xmlelement(params,xmlagg(xmlelement(param,xmlforest(name,value,data_type)))) into result from (
select 'param_name1' name, 'param_value1' value , 'varchar2' data_type from dual
union all
select 'param_name2' name, 'param_value2' value , 'varchar2' data_type from dual
union all
select 'param_name3' name, 'param_value3' value , 'varchar2' data_type from dual
);  return result;
end;

select * from xmltable('/PARAMS/PARAM' passing return_params 
                 columns name varchar2(1000) path 'NAME'
                         ,value varchar2(1000) path 'VALUE'
                         ,data_type varchar2(1000) path 'DATA_TYPE'
                 )

Upvotes: 1

Related Questions