My Oracle Support Banner

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

Last updated on JULY 05, 2017

Applies to:

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


=== 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.


The original statement is

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

Would like to write:

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

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?


To view full details, 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 a vibrant support community of peers and Oracle experts.