Total Pageviews

October 10, 2015

10/10/2015 12:47:00 AM
Oracle Database 12c has extended the SQL syntax and functionality for joining with collections in such a way that the call to the PL/SQL function that creates the collection can take an input parameter taken from the records from the table to which it is joined. That is a little abstract, so let’s quickly look at an example:

Here we perform a join between table DEPT and a PL/SQL function that returns a collection. This function takes an input parameter – a department number – and returns in the collection the names of all employees in that department. 

create or replace type str_tbl as table of varchar2(200);
create or replace funtion xxcross_fun(p_dept in number)
 ) return str_tbl
 as
    l_enames str_tbl;
 begin
 select cast(collect(ename) as str_tbl) into
      l_enames from emp
                   where depnot=p_dept;
 return l_enames;
 end;
  Usage: 
SELECT * FROM DEPT d  CROSS APPLY  xxcross_fun(deptno)
Now we have seen how the CROSS APPLY will help us join with the collection result from function that takes a join-value as its input. However, in the process, we have lost department 40 (because it does not have any employees and results in an empty collection in the employees_in_department function).


Oracle 12c Features