How can I return a select-query that canges based on the parameters i use when calling a function?-CodePudding

I have been wrestling with this problem for a while now. I want to make a function that takes parameters. The function is supposed to, with user submitted parameters, change a select-statment and after return/run the select.

The function:

create or replace FUNCTION F_GET_TABLE(column1_in varchar2, column2_in varchar2)
    return query
is 
    base_query constant varchar2(5000 char) :=
    'select column1, column2 from CustomersTable;';
begin    
    replace(replace(base_query, 'column1', column2_in),'column2', column2_in );
    queryToReturn query := base_query;
    return queryToReturn;
end F_GET_TABLE;

In my head the end result should be that I call the function like this:

select F_GET_TABLE('f_name','e_mail') from dual;

And I should have the same result as if I wrote the select-statment as:

select f_name, e_mail from CustomersTable;

So I've tried in different ways to make the function return the query as I've described. However the best I managed to do was return a varchar2 with the select-statement - however then I have to remove "" from the start and end of the select-block and run it manually.. I couldn't seem to find any answers to my problem while searching the internet, please help me out here!

CodePudding user response：

Here's how:

SQL> CREATE OR REPLACE FUNCTION f_get_table (column1_in  VARCHAR2,
  2                                          column2_in  VARCHAR2)
  3     RETURN VARCHAR2
  4  IS
  5     base_query  VARCHAR2 (5000) := 'select column1, column2 from emp';
  6  BEGIN
  7     RETURN REPLACE (REPLACE (base_query, 'column1', column1_in),
  8                                          'column2', column2_in);
  9  END f_get_table;
 10  /

Function created.

SQL> select f_get_table('ename', 'job') from dual;

F_GET_TABLE('ENAME','JOB')
--------------------------------------------------------------------------------
select ename, job from emp

SQL>

If you want to return result, then return ref cursor:

SQL> CREATE OR REPLACE FUNCTION f_get_table (column1_in  VARCHAR2,
  2                                          column2_in  VARCHAR2)
  3     RETURN SYS_REFCURSOR
  4  IS
  5     base_query  VARCHAR2 (5000) := 'select column1, column2 from emp';
  6     l_rc        SYS_REFCURSOR;
  7  BEGIN
  8     OPEN l_rc FOR
  9        REPLACE (REPLACE (base_query, 'column1', column1_in),
 10                 'column2',
 11                 column2_in);
 12
 13     RETURN l_rc;
 14  END f_get_table;
 15  /

Function created.

Testing:

SQL> SELECT f_get_table ('ename', 'job') FROM DUAL;

F_GET_TABLE('ENAME',
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME      JOB
---------- ---------
KING       PRESIDENT
BLAKE      MANAGER
CLARK      MANAGER
JONES      MANAGE
<snip>