How to create a function in PL/SQL and return output

Question

I am try to create a function that will table input to function and return me output

My code :

create or replace function details(keyword varchar2 , depid varchar2 )
return clob 

AS
     v_lst CLOB :='';

BEGIN 

select regexp_replace(
         xmlcast(xmlagg(xmlelement(e, 
           'select ''' || table_name || ''' as tbl, count(*) as cnt from '
           || table_name, ' union all ' || chr(10))) as clob),
       ' union all ' || chr(10) ||'$', ';')
       as sql_string
from   ( SELECT table_name from my_tables where DPNAME := keyword and DEPD := depid);

return v_lst;

EXCEPTION 
    WHEN NO_DATA_FOUND THEN RETURN '';

END;

Trying to call my function

 select details('XYZ' , 24 , : RESULT) as RES from dual;

Some compile error getting

Expected output :

The function will return the dynamic query for me 

Answer 1

Well, this can't work as ALL_TABLES don't contain DPNAME nor DPED columns. What are they? And what are function's parameters?

---

What exactly are you trying to do? Count number of rows in all tables available to you?

If so, then

• you should gather statistics
• query ALL_TABLES' NUM_ROWS column

For example:

SQL> exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
EMP                                    14
DEPT                                    4
TUSER                                   4
TRANSACTION                            11
TABLE_DATE                             38
<snip>