How To Return A Select Result In A Function? (Doc ID 1399434.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Goal

=== ODM Question ===
How to specify the functions return value?

For example:

'Select' statement using the 'Union' of two other 'Select' statements in its 'from' clause.
Those select statements could be interpreted as a parametrized function.  This would shorten the over all statement length very much.

Sample:

The original statement is

select * from ( (select * from anytable where aParameter1 ... )
union
(select * from anytable where aParameter2 ... )
where ...

Would like to write:

function DoSubSelect(aWithAParam) return ??? is
begin
return select * from anytable where aWithAParam ...;
end;

And write:
select * from( DoSubSelect(aParameter1) union DoSubSelect(aParameter2)) where ...

Is this possible with Oracle SQL syntax?

In other words; How is the result of a select specified in Oracle syntax?


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms