Topic: SQL*Plus and PL/SQL >> Oracle Function taking ‘n’ parameter performing
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Oracle Function taking ‘n’ parameter performing |
| Aditya |
Posted: Jan 12, 2005 06:35:07 PM |
Total Post: 32
Joined: Jan, 2003
|
Hi All,
Problem : Oracle Function taking ‘n’ parameter performing set Operation on Oracle object types.
Description:I have current function Operate which takes 3 Parameter.
1: Operator : ‘U’ for Union
2: inUniv1 : Table type Object of Scalar type (can have array or list of ids..etc.)
3: inUniv2: Table type Object of Scalar type (can have array or list of ids..etc.)
This function performs SQL set Operation based on the Operator passed.(for ex: I have used Union only),
To have this function except 3 parameters (Universes) I have overloaded this function as
Shown in example2:
What I am actually looking for is to pass any number (n) of universes,
So as to can avoid this explicit overloading.
Any help Would be greatly appreciated.
Thanks
Adi
---------------------------------------------------------------------------------------------------------------------------------
-- TYPE camx.Typ_Elements
CREATE OR REPLACE TYPE Typ_Elements AS TABLE OF NUMBER
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
--example:1) Function for multiple (2) universe.
---------------------------------------------------------------------------------------------------------------------------------
FUNCTION operate (inoperator VARCHAR2,
inuniv1 typ_elements,
inuniv2 typ_elements)
RETURN typ_elements
AS
theunivtbl typ_elements;
BEGIN
CASE inoperator
WHEN 'U'
THEN
-- Union
SELECT CAST (MULTISET (SELECT *
FROM (SELECT *
FROM TABLE (CAST (inuniv1 AS typ_elements)) a
UNION
SELECT *
FROM TABLE (CAST (inuniv2 AS typ_elements)) b)
) AS typ_elements
)
INTO theunivtbl
FROM DUAL;
END CASE;
RETURN theunivtbl;
END;
-------------------------------------------------------------------------------------------------------------------------
-- example:2) Function for multiple (3) universe.
---------------------------------------------------------------------------------------------------------------------------------
FUNCTION operate (inoperator VARCHAR2,
inuniv1 typ_elements,
inuniv2 typ_elements,
inuniv3 typ_elements)
RETURN typ_elements
AS
BEGIN
RETURN operate (inoperator, inuniv1, operate (inoperator, inuniv2, inuniv3));
END;
--------------------------------------------------------------------------
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 333 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
