SQL> SQL> -- Simple non-pipelined table function to find all non-partitioned tables SQL> create or replace function tab_fun_a return c50_tab_type 2 as 3 x c50_tab_type; 4 y c50_tab_type; 5 icnt number; 6 begin 7 select table_name bulk collect into x from user_tables; 8 select object_name bulk collect into y from user_objects a, table(x) t 9 where t.column_value=a.object_name and a.subobject_name is null; 10 select count(distinct t.column_value) into icnt from table(y) t; 11 dbms_output.put_line('rows loaded: '||y.count); 12 dbms_output.put_line('distinct rows: '||icnt); 13 return y; 14 end; 15 / Function created. SQL> SQL> set serveroutput on size 10000 SQL> declare 2 x c50_tab_type; 3 begin 4 select * bulk collect into x from table(tab_fun_a); 5 for i in 1..x.count loop 6 dbms_output.put_line(i||' '||x(i)); 7 end loop; 8 end; 9 / rows loaded: 177 distinct rows: 161 1 A 2 A1 3 A2 4 A3 5 ADMIN_EXT_EMPLOYEES 6 ADMIN_EXT_EMPLOYEES2 7 ADMIN_EXT_EMPLOYEES3 8 B 9 BANCHE 10 C 11 CAT_PARTDESC 12 CDC_TEST 13 CLAIMS_ 14 COLLECTION_TEST1 15 COUNTRY 16 CUP_PARTUPDATEDESC 17 CUSTOMERS 18 D 19 DEPARTMENTS 20 DEPT_TAB 21 DR$CAT_PARTDESC_FTIDX_1$I 22 DR$CAT_PARTDESC_FTIDX_1$K 23 DR$CAT_PARTDESC_FTIDX_1$N 24 DR$CAT_PARTDESC_FTIDX_1$R 25 DR$CAT_PARTDESC_FTIDX_2$I 26 DR$CAT_PARTDESC_FTIDX_2$K 27 DR$CAT_PARTDESC_FTIDX_2$N 28 DR$CAT_PARTDESC_FTIDX_2$R 29 DR$CAT_PARTDESC_SHORTDESC$I 30 DR$CAT_PARTDESC_SHORTDESC$K 31 DR$CAT_PARTDESC_SHORTDESC$N 32 DR$CAT_PARTDESC_SHORTDESC$R 33 DRUGS_ 34 E 35 EMP 36 EMPLOYEES 37 EMP_EXT 38 EMP_TAB 39 EMP_TMP 40 GLEN_TEST 41 GRID 42 GTS 43 H 44 HIER_TABLE 45 HQ_TAB 46 HQ_TABLE 47 HQ_TAB_NAME 48 IDENTITY 49 IOT_ROWID 50 JOB_HISTORY 51 LMV 52 LOB_FGAC_TEST 53 LOB_UPDATES_LOG 54 LONGTAB 55 LTS 56 MEMBERS_ 57 MLOG$_MVA1 58 MLOG$_MVQR1 59 MLOG$_MVQR2 60 MLOG$_MVTEST 61 MLOG$_MVTEST2 62 MLOG$_MVTEST3 63 MLOG$_MVTEST4 64 MLOG$_MV_JOIN 65 MLOG$_MV_TEST 66 MLOG$_OBJ_TEST 67 MLOG$_PART 68 MLOG$_SAMPLE 69 MLOG$_SNAP_TEST 70 MLOG$_T3_A 71 MMV 72 MVA1 73 MVA1 74 MVA2 75 MVA2 76 MVQR1 77 MVQR2 78 MVTEST 79 MVTEST2 80 MVTEST3 81 MVTEST4 82 MV_CAPABILITIES_TABLE 83 MV_JOIN 84 MV_JOIN 85 MV_MVQR1 86 MV_MVQR1 87 MV_MVQR2 88 MV_MVQR2 89 MV_MVQR3 90 MV_MVQR3 91 MV_MVTEST2 92 MV_MVTEST2 93 MV_TEST 94 MV_TEST_SUMMARY 95 MV_TEST_SUMMARY 96 MV_UNION 97 MV_UNION 98 O 99 OBJ_TEST 100 OBJ_TEST_MV 101 OBJ_TEST_MV 102 PARSUB1 103 PART 104 PARTEST 105 PARTEST_HASH 106 PARTS 107 PARTS 108 PART_RANGE_HASH_TEMP 109 PART_RANGE_IOT 110 PART_RANGE_LOOKUP 111 PAR_TEST 112 PLAN_TABLE 113 PLAY 114 RUPD$_MVQR1 115 RUPD$_MVQR2 116 RUPD$_MVTEST 117 RUPD$_MVTEST2 118 RUPD$_MVTEST3 119 RUPD$_MVTEST4 120 RUPD$_MV_TEST 121 RUPD$_OBJ_TEST 122 RUPD$_PART 123 RUPD$_SNAP_TEST 124 RUPD$_T3_A 125 SAMPLE 126 SAMPLE_SNP 127 SAMPLE_SNP 128 SCHEDULE 129 SNAP_TEST 130 STOCKTABLE 131 T 132 T1 133 T1_A_MV 134 T1_A_MV 135 T2 136 T2_ 137 T2_A_LOG 138 T3_A 139 T5 140 T5_LOG 141 TAB1 142 TABLE1 143 TABLE2 144 TABLE_A 145 TABLE_B 146 TABLE_C 147 TAB_UNION 148 TAB_UNION 149 TBLSHAPES 150 TEMP 151 TEMP_TEST 152 TES 153 TEST1 154 TEST2 155 TEST3 156 TEST4 157 TEST5 158 TEST6 159 TESTMV 160 TESTMV 161 TIME_TEST 162 TMP_TABS 163 TMP_TREE 164 TREE_TABLE 165 TT2 166 TT3 167 TTT 168 TYU4 169 TYU_A 170 TYU_MV 171 TYU_MV 172 UNICODE_TEST 173 USERGRP 174 Y1 175 Y2 176 Y3 177 YYY PL/SQL procedure successfully completed. SQL> SQL> SQL> drop function tab_fun_a; Function dropped. SQL> SQL> spool off