Oracle SQL Developer, using dynamic SQL in function

Question

I have the following script which contains a function named 'myFunction'. (declaration of types named rowValueTmp and rowValueTable are also attached for your information) Basically, I need to use a table name as an input parameter for myFunction. I found that I need to use dynamic SQL in order to use the table name as a parameter (Please correct me if there are alternative ways to do this). So the following code is what I have tried so far.

create or replace type rowValueTmp as object (
    month number,
    year number
);
/    
create or replace type rowValueTable as table of rowValueTmp;
/        
create or replace FUNCTION myFunction (TABLENAME in VARCHAR2) 
    return rowValueTable as
      v_ret   rowValueTable;
    begin
      execute immediate '
      select rowValueTmp(month, year)
      bulk collect into v_ret
      from '||TABLENAME;    
      return v_ret;
    end myFunction;
/
select * from table(myFunction('SCHEMA.TEST'));

But, this code gives me an error, and I assumed that this error is occurred because of using 'bulk collect' in execute immediate block.

ORA-03001: unimplemented feature

If I replace the content of execute immediate as the following, the above script is working..

select rowValueTmp(month, year)
bulk collect into v_ret
from SCHEMA.TEST;

Question
1] Is there any way(rather than Dynamic SQL) that I can use a table name as an input parameter for myFunction?
2] If I am not allowed to use bulk collect in execute immediate block, what do you suggest?

EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_test' BULK COLLECT INTO l_tab;, dba-oracle.com/plsql/t_plsql_dynamic.htm — Sebas
– Sebas, Commented Oct 8, 2014 at 21:36

Allan · Accepted Answer · 2014-10-08 21:37:19Z

4

You can return values from execute immediately into a bulk collect:

CREATE OR REPLACE FUNCTION myfunction (tablename IN VARCHAR2)
   RETURN rowvaluetable AS
   v_ret rowvaluetable;
   v_table VARCHAR2 (61) := DBMS_ASSERT.sql_object_name (tablename);
BEGIN
   EXECUTE IMMEDIATE '
      select rowValueTmp(month, year)
      from ' || v_table
      BULK COLLECT INTO v_ret;

   RETURN v_ret;
END myfunction;
/

In the interest of an abundance of caution, I'd recommend using DBMS_ASSERT to validate the table parameter as well (as shown).

answered Oct 8, 2014 at 21:37

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Developer, using dynamic SQL in 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