pass values from a table into oracle table funcion in SQL

Question

I have

create or replace TYPE t_process_teilenummer_tab AS TABLE OF VARCHAR2(4000);

create or replace FUNCTION process_teilenummer (
Teilenummer IN CLOB 
 )
RETURN t_process_teilenummer_tab 
...some processing which returns a table of strings

so I can now do

SELECT column_value
FROM   TABLE(
         process_teilenummer('abc , xyz' ) )

and I get a table with one column with 2 rows abc and xyz. of course there is another process involved for example to generate more combination of the inserted value or values as comma separated string.

using the CLOB as IN parameter isn't necessary but before I thought it could take a string literal with more than 4000 characters which us not true because it doesn't matter what is your IN parameter if varchar2(4000) or varchar2(30000) or CLOB you can not exceed 4000 as string literal as far as I know.

So I can use the function as stated above but I would like to insert values from a table as well so instead of 'abc,xyz' etc. it would be fine to take an output from another table with 1 column an 5000 rows an pass it to my table function, it will process what it needs and I can again 1 column with x-rows

Answer 1

You may use Polymorphic Table Function (PTF) introduced in 18c that does exactly what you want: transforms arbitrary input of type result set.

1. Create row transformation function.

create function f_apply_transform(p in varchar2)
  return varchar2
is
begin
  dbms_output.put(p);
  return p || '_converted';
end;/

2. Create PTF implementation package.

create package pkg_transform as
  
  function describe(tab in out DBMS_TF.TABLE_T, col in DBMS_TF.COLUMNS_T)
    return DBMS_TF.DESCRIBE_T
  ;

  procedure fetch_rows;

end pkg_transform;
/

create package body pkg_transform as


  function describe(tab in out DBMS_TF.TABLE_T, col in DBMS_TF.COLUMNS_T)
    return DBMS_TF.DESCRIBE_T
  is
    new_cols DBMS_TF.COLUMNS_NEW_T;
    new_id pls_integer := 1;
  begin
    for i in 1..tab.column.count loop

      for j in 1..col.count loop
        if tab.column(i).description.name = col(j) then
          /*Add column to modification list and remove from pass through*/
          tab.column(i).for_read := true;
          tab.column(i).pass_through := false;
          new_cols(new_id) := tab.column(i).description;
          new_cols(new_id).type := DBMS_TF.TYPE_VARCHAR2;
          new_cols(new_id).max_len := 1000;
          new_id := new_id + 1;
          exit;
        end if;
      end loop;
    end loop;

    return DBMS_TF.DESCRIBE_T(new_columns => new_cols);
  end;

  procedure fetch_rows
  is
    env DBMS_TF.ENV_T := DBMS_TF.get_env();
    rowset DBMS_TF.ROW_SET_T;
    rowcount pls_integer;
    converted_col DBMS_TF.TAB_VARCHAR2_T;
  begin
    /*Get input*/
    DBMS_TF.get_row_set(rowset => rowset, row_count => rowcount);
    /*Process each column*/
    for i in 1..rowset.count loop
      /*Process each row in column vector*/
      for j in 1..rowcount loop
         /*Modify a value*/
         converted_col(j) := f_apply_transform(rowset(i).tab_varchar2(j));
      end loop;
      /*Put modified vector in place of old column*/
      DBMS_TF.put_col(i, converted_col);
    end loop;
  end;

end pkg_transform;
/

3. Declare polymorphic function using that package.

create function f_convert(tab table, col columns)
  return table
pipelined row polymorphic
using pkg_transform;
/

4. Apply transformation to the specified columns.

with sample(a,b,c) as (
  select dbms_random.string('U',15), dummy, lpad(level, 3, '0')
  from dual
  connect by level <= 10
)
select *
from f_convert(sample, columns(a, b))

C	A	B
001	ACNQONWIMSNBHHU_converted	X_converted
002	OOJBAXPGQKKIKMR_converted	X_converted
003	YLXBSKURCTWERXY_converted	X_converted
004	IZPZTVUAYHKNWBA_converted	X_converted
005	FUTKTGMCYXIKOTY_converted	X_converted
006	WYFFKEEFNQFWKXU_converted	X_converted
007	LZLWDPKEZXGHZNN_converted	X_converted
008	TQYEMVRAGOWAQUQ_converted	X_converted
009	AYCKTLAZXYTEMEY_converted	X_converted
010	SXWMUSRCXBZVLMY_converted	X_converted

fiddle

Answer 2

You appear to want to split a delimited CLOB on the delimiter and put it into a collection.

You can adapt this answer:

CREATE TYPE string_list AS TABLE OF VARCHAR2(4000);

then:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN string_list DETERMINISTIC PIPELINED
AS
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      PIPE ROW (SUBSTR( i_str, p_start, p_end - p_start ));
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      PIPE ROW (SUBSTR( i_str, p_start, c_len - p_start + 1 ));
    END IF;
  END IF;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    NULL;
END;
/

Then:

SELECT column_value
FROM   TABLE(split_string('abc , xyz', ' , ') );

Outputs:

COLUMN_VALUE
abc
xyz

---

However, this is all probably an XY-problem as you state:

using the CLOB as IN parameter isn't necessary but before I thought it could take a string literal with more than 4000 characters which us not true because it doesn't matter what is your IN parameter if varchar2(4000) or varchar2(30000) or CLOB you can not exceed 4000 as string literal as far as I know.

A string literal cannot exceed 4000 bytes; however, you can concatenate multiple string literals together with a CLOB to exceed 4000 bytes:

SELECT column_value
FROM   TABLE(
         split_string(
           EMPTY_CLOB()
           || 'First 4000 bytes...'
           || 'Second 4000 bytes...'
           || 'Third 4000 bytes...'
           , ' , '
         )
       );

or:

SELECT column_value
FROM   TABLE(
         split_string(
           TO_CLOB('First 4000 bytes...')
           || 'Second 4000 bytes...'
           || 'Third 4000 bytes...'
           , ' , '
         )
       );

or, if you are using a client application that supports bind variables then you can use:

SELECT column_value
FROM   TABLE(split_string(:your_clob, ' , '));

and pass the CLOB via the bind variable.

fiddle

Answer 3

Assuming that your my_oracle_table_fn has the signature:

FUNCTION my_oracle_table_fn(
  i_list IN string_list
) RETURN string_list

which uses the type:

CREATE TYPE string_list AS TABLE OF VARCHAR2(15);

Then you can use CAST and COLLECT:

SELECT column_value
FROM   TABLE(
         my_oracle_table_fn(
           (
             SELECT CAST(
                      COLLECT(
                        CAST(dbms_random.string('U',15) AS VARCHAR2(15))
                      )
                      AS string_list
                    )
             FROM   DUAL
             CONNECT BY LEVEL <= 10
           )
         )
       );

fiddle