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: 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Goal
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.