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.

What does "not working" mean? Please always provide exact error messages. Why are you placing a count(*) into a VARCHAR2? It's a number so use one! I also don't understand what you mean when you say " i am confusing with that how can i extract multiple rows while calling this function". What exactly are you trying to achieve? — Ben
– Ben, Commented Dec 26, 2012 at 17:16
You're not going to get multiple rows from that query -- it just returns count(table_name), so you'll get one row with a non-null integer greater than or equal to zero. — David Aldridge
– David Aldridge, Commented Dec 27, 2012 at 8:16

APC · Accepted Answer · 2012-12-26 17:17:21Z

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.

Collectives™ on Stack Overflow

PL/SQL function: Extract multiple rows from function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related