A Function that return a table

=====================

A Function that return a table

=====================

--CREATE OR REPLACE TYPE myTableType   AS TABLE OF myObjectFormat

--Create the types to support the table function.

--DROP TYPE t_network_id_row;

--DROP TYPE t_network_id_tab;

--CREATE TYPE t_network_id_row AS OBJECT (

--  id           NUMBER

--);

--/

--CREATE TYPE t_network_id_tab IS TABLE OF t_network_id_row;

--/

--SELECT id FROM   TABLE(test_function('SGA_W')) ORDER BY id DESC;

CREATE OR REPLACE FUNCTION test_function (p_table_name         IN USER_TABLES.table_name%type) RETURN t_network_id_tab AS

  CURSOR get_tables_cur (cp_prefix IN VARCHAR2) IS

  SELECT table_name

   FROM USER_TABLES

  WHERE SUBSTR(table_name,1,LENGTH(cp_prefix)) = cp_prefix;

  v_ret_number    NUMBER;

  l_tab  t_network_id_tab := t_network_id_tab();

  

BEGIN

  v_ret_number := 0;

  FOR get_tables_rec IN get_tables_cur(p_table_name) LOOP

    v_ret_number := v_ret_number + 1;    

    l_tab.extend;

    l_tab(l_tab.last) := T_NETWORK_ID_ROW(1);

  END LOOP;

  RETURN l_tab;

END test_function;