A left correlation join occurs when you pass as an argument to your table function a column value from a table or view referenced to the left in the table clause. This technique is used with XMLTABLE and JSON_TABLE built-in functions, but also applies to your own table functions.

Here's the thing to remember:

The following code demonstrates this behavior, for both pipelined and non-pipelined functions.

Here's the thing to remember:

The table function will be calledClearly, this could cause some performance issues, so be sure that is what you want and need to do.for each rowin the table/view that is providing the column to the function.

The following code demonstrates this behavior, for both pipelined and non-pipelined functions.

```
CREATE TABLE things
(
thing_id NUMBER,
thing_name VARCHAR2 (100)
)
/
BEGIN
INSERT INTO things VALUES (1, 'Thing 1');
INSERT INTO things VALUES (2, 'Thing 2');
COMMIT;
END;
/
CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER)
RETURN numbers_t
IS
l_numbers numbers_t := numbers_t ();
BEGIN
l_numbers.EXTEND (id_in * 5);
FOR indx IN 1 .. id_in * 5
LOOP
l_numbers (indx) := indx;
END LOOP;
DBMS_OUTPUT.put_line ('more numbers');
RETURN l_numbers;
END;
/
BEGIN
FOR rec IN (SELECT th.thing_name, t.COLUMN_VALUE thing_number
FROM things th, TABLE (more_numbers (th.thing_id)) t)
LOOP
DBMS_OUTPUT.put_line ('more numbers ' || rec.thing_number);
END LOOP;
END;
/
more numbers
more numbers
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 6
more numbers 7
more numbers 8
more numbers 9
more numbers 10
CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER)
RETURN numbers_t PIPELINED
IS
BEGIN
DBMS_OUTPUT.put_line ('more numbers');
FOR indx IN 1 .. id_in * 5
LOOP
PIPE ROW (indx);
END LOOP;
RETURN;
END;
/
BEGIN
FOR rec IN (SELECT th.thing_name, t.COLUMN_VALUE thing_number
FROM things th, TABLE (more_numbers (th.thing_id)) t)
LOOP
DBMS_OUTPUT.put_line ('more numbers ' || rec.thing_number);
END LOOP;
END;
/
more numbers
more numbers
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 6
more numbers 7
more numbers 8
more numbers 9
more numbers 10
```

## Comments

## Post a Comment