Returning a table from a PL/SQL function

Oracle, PLSQL

With collections, it is possible to return a table from a pl/sql function. First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object ( i number, n varchar2(30) );

Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;

Now, we’re ready to actually create the function:

create or replace function
return_table return t_nested_table as v_ret
t_nested_table;
begin
v_ret  := t_nested_table();
v_ret.extend;
v_ret(v_ret.count) := t_col(1, 'one');
v_ret.extend;
v_ret(v_ret.count) := t_col(2, 'two');
v_ret.extend;
v_ret(v_ret.count) := t_col(3, 'three');
return v_ret;
end;

Here’s how the function is used:

select * from table(return_table);

Comments

Dynamic search of all user tables, PL/SQL, Oracle

Oracle, PLSQL

This script returns the column and table names for a value input by a user. For example, if we know that there is a character value of ‘US-15017094′ with no other details available, this routine will search the user schema and list both table and column name for that value.

Declare searchtxt VARCHAR2(30):='US-15017094';
sqltxt VARCHAR2(255);
resultcount pls_integer:=0;
Begin
    for c in (select table_name, column_name from USER_tab_columns
        where table_name in ('ES_ALERT','AM_REGION','ES_ALERT_CACHE_PO')
    and data_type in ('CHAR','VARCHAR2'))
    loop
    resultcount:=0;
    sqltxt := 'select count(1) from '|| c.table_name||'  where '|| c.column_name||' ='||chr(39)||searchtxt||chr(39);
    execute immediate sqltxt into resultcount;
    IF resultcount=1 THEN
        dbms_output.put_line(c.table_name || '.' || c.column_name);
    END IF;
end loop;
End;

Example from Oracle Tech Network

Comments