Full Outer Join in PL/SQL Table

[james777]

Hello all
I got 2 pl/sql tables with the following data as below and i want to do a full outer join on the result.. ,which..Pl/sql Tables are populated with lot of business logic which is not achievable by SQL queries.

PL/SQL Table1:
----------------------
Code D-Code
1 D1
2 D2
3

PL/SQL Table2:
-----------------
D-code D-Name
D1 SAS
D2 ROS
D3 CAS

I want to do an full outer join on these two pl/sql tables to get a result like this

--------------
Code-- D-Name
1 SAS
2 ROS
3 {null}
{null} CAS


I can get this on a straight tables in oracle,, but with pl/sql table ,, can i get the full outer join...
any suggestions is appreciated..
And more over i get weird questions...,bear with me..

Thanks A lot..
(Novice) -- expermenting ....

 

[james777]

Any takers, If not PL/SQL table any other collection to do a full-outer join on non-database tables in oracle..

 

[SantaMufasa]

James,

First, why can you not use SQL tables? Second, what syntax do you propose using in order to do a "pl/sql table...full outer join..."?

I can propose a hybrid solution (SQL tables that you outer join, then obtain the PL/SQL table "with lot of business logic which is not achievable by SQL queries" via use of a function.

Please answer my first two questions, then let me know if you want my hybrid solution. For the hybrid solution, you must post samples of the "business logic..." that resides in the PL/SQL table.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:24 (14Jul04) UTC (aka "GMT" and "Zulu"), 20:24 (13Jul04) Mountain Time)

 

[james777]

Well for your first question..
The result set of the first pl/sql table was populated
with 5 stored procedures which does complex queries
from 14 tables.

2) I dont know the syntax to do full outer join on pl/sql table and sql table.

What i am getting right now : Let me put in a simple way instead of
all columns data.

Table1:

Tab1_PK col1 col2
1 X Y
1 Y Y
1 Z A

Table2:

Tab2_PK col1 col2

1 X Z
1 B Z
1 Q A

Lets assume i got the above result set one in pl/sql table ( Tab1 data)
and second in sql table ( tab2 data )

I have to get the data in final table from tab1 data and tab2 data which exists in one table and not exists in both the tables on tab_pk .

OutPut :

Out_table:

Tab1Pk Tab1.Col1/Tab2.Col2

1 X ---> Exists in Tab1/Tab2 ( Exists in both tables )
1 Y ---> Exists in Tab1 not in tab2
1 Z ---> Exists in Tab1 not in tab2
1 B ---> Exists in Tab2 not in tab1
1 Q ---> Exists in Tab2 not in tab1

If i have both the tab1 and tab2 data in sql tables i can get the result set by doing
a full outer join. In this case one is pl/sql table and other is sql table.

 

[SantaMufasa]

James,

Unfortunately, the only similarity between PL/SQL tables and SQL tables is the word "table". You cannot use any SQL verb with a PL/SQL table. Therefore, there is no built-in code to do any type of join between a PL/SQL table and a SQL table, not to mention an "outer join".

Therefore, if you want the equivalent of an "outer join" between the two heterogeneous tables in your example, you will need to contrive the code "by hand".

As an alternative to writing "outer join" code between the PL/SQL and SQL tables, there should be nothing preventing your INSERT-ing the contents of your PL/SQL table into a SQL table, thus allowing you to "...get the result set by doing a full outer join" as you have successfully done already.

Let us know what you choose to do.

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:06 (26Jul04) UTC (aka "GMT" and "Zulu"), 00:06 (26Jul04) Mountain Time)

 

[sem]

If your collection types are defined as "database wide", you may query such tables using table() consruct.

Regards, Dima

http://seminihin.narod.ru