Function with Table_name as input and returns a number in Oracle

Question

I have a function that takes table_name as input and returns a number. The function gets compiled properly but for some reason when I try test the function it throws error - missing keyword.

CREATE OR replace FUNCTION TEST_FUNCTION (name_table IN VARCHAR2) RETURN NUMBER

        IS         

          rday NUMBER;                
  BEGIN 

  execute immediate 
  'select day_i into rday
                FROM ' || name_table || '
                WHERE day_i = 1 and rownum = 1';

   return rday;

  END TEST_FUNCTION;

This is how I am testing it Select TEST_FUNCTION ('FDR_REP') from dual;

kevinskio · Accepted Answer · 2015-10-26 19:02:45Z

2

The syntax for execute immediate is different for an into clause try

CREATE OR replace FUNCTION TEST_FUNCTION (name_table IN VARCHAR2) RETURN NUMBER

        IS         

          rday NUMBER;                
  BEGIN 
   execute immediate  'select day_i 
                FROM ' || name_table || '
                WHERE day_i = 1 and rownum = 1' into rday;


   return rday;

  END TEST_FUNCTION;

answered Oct 26, 2015 at 19:02

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Function with Table_name as input and returns a number in Oracle

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