PL/SQL function: Extract multiple rows from function

Question

I have multiple tables in my database. I have to find out the names of those tables. So I have written the following program:

CREATE OR REPLACE FUNCTION fun_tablefinder( keyword VARCHAR2 )
RETURN NUMBER
IS
 v_query VARCHAR2(200);tablename VARCHAR2(20);
 tablename NUMBER;

 BEGIN

v_query:='SELECT count(TABLE_NAME) FROM USER_TAB_COLUMNS WHERE TABLE_NAME LIKE ''%'||upper(keyword)||'%''';
EXECUTE IMMEDIATE v_query INTO tablename;
RETURN tablename;
END;

But there is error in my query which I can't fix:

tablename:= fun_tablefinder('ubl'); is not working

And also I am confused about how can I extract multiple rows while calling this function. How can I use loop to handle those results?

Thanks in advanced.

Answer 1

You need a function which returns a nested table. In my version of your function I use the dbms_debug.vc2coll datatype, an Oracle built-in collection type.

Note the use of BULK COLLECT to populate a collection with multiple rows.

create or replace function     fun_tablefinder( keyword VARCHAR2 )
    RETURN dbms_debug.vc2coll
IS
   tablenames dbms_debug.vc2coll;

BEGIN

    SELECT TABLE_NAME
    bulk collect into tablenames
    FROM USER_TABLES 
    WHERE TABLE_NAME LIKE upper(keyword)||'%';
    RETURN tablenames;
END;

Not sure why you're using dynamic SQL, it's utterly unnecessary. Also, you probably want to query USER_TABLES which returns one row per table, rather than the multiple hits you'll get from USER_TAB_COULMNS.

To use this query is a simple matter of deploying the TABLE() function like this:

select * 
from table ( fun_tablefinder('UBL') );

---

Obviously this is a trivial use of a SQL function, which is complete over-engineering for a scenario where a straight SQL query will suffice.