How to write PL/SQL function which returns the result of a select statement having as pararmeters the function's parameters?

Question

I tried to write a PL/SQL function having as parameters a tablename and a column name, which returns the result of the query as a table. Here's what I tried:

CREATE TYPE TABLE_RES_OBJ AS OBJECT (
    employee_id number(30) ,
    person_id number(30),
    joined_year number(4), 
    salary number(20,2),
    qualification varchar2(45),
    department varchar2(45)
);
 /
create  TYPE table_ret as TABLE OF  table_res_obj;
 /
create or replace function select_custom(p_tablename varchar2, p_colname varchar2 ) return table_ret
is
    ret table_ret;
    query_txt varchar2(100) := 'SELECT :a from :b';
begin
    execute immediate query_txt bulk collect into ret using  p_colname, p_tablename;
        return ret;
end select_custom;

As you can see, this is not that general as wanted, but still not working, it says the table doesn't exist, even when I try to run it with an existing table.

If the table and and the columns are based on parameters, how to you know the result has **always** the structure of `TABLE_RES_OBJ`? — Wernfried Domscheit, May 31 '21 at 20:02
(What advantages do you see over direct use of a view or table?) — greybeard, Jun 01 '21 at 09:43

score 1 · Accepted Answer · answered May 31 '21 at 17:49

Exactly, it won't work that way. You'll have to concatenate table and column name into the select statement. For (simple) example:

SQL> create or replace type table_res_obj as object
  2    (ename varchar2(20));
  3  /

Type created.

SQL> create or replace type table_ret as table of table_res_obj;
  2  /

Type created.

SQL> create or replace function select_custom
  2    (p_tablename varchar2, p_colname varchar2 )
  3  return table_ret
  4  is
  5    ret table_ret;
  6    query_txt varchar2(100);
  7  begin
  8    query_txt := 'select table_res_obj(' || dbms_assert.simple_sql_name(p_colname) ||') from ' ||
  9                  dbms_assert.sql_object_name(p_tablename);
 10    execute immediate query_txt bulk collect into ret;
 11    return ret;
 12  end select_custom;
 13  /

Function created.

Does it work?

SQL> select select_custom('dept', 'deptno') from dual;

SELECT_CUSTOM('DEPT','DEPTNO')(ENAME)
--------------------------------------------------------------------------------
TABLE_RET(TABLE_RES_OBJ('10'), TABLE_RES_OBJ('20'), TABLE_RES_OBJ('30'), TABLE_R
ES_OBJ('40'))


SQL>

Thank you a lot! I didn't knew that i have to use the dbms_assert here ! also mistaken the type, it has to contain only one element if I query just that. It works good now! — Moldovan Adrienn, May 31 '21 at 18:17
Dbms_assert is to prevent SQL injection. You don't HAVE TO use it, but it is good if you do. — Littlefoot, May 31 '21 at 18:29

How to write PL/SQL function which returns the result of a select statement having as pararmeters the function's parameters?

1 Answers1