CODEWITHSUNDEEP
sqloracle-databasefunctiondynamic-sql
JavaQuest
JavaQuest

Reputation: 713

Oracle Function with table_name and date as input and Number as output

I am trying to test a function, where I am passing a table name and date as input and getting a number as return value. I am passing just the date as bind variable (cannot pass table_name )

CREATE OR REPLACE FUNCTION TABLE_TEST (reptable in varchar2, fltdate in varchar2)   RETURN NUMBER

        IS
        day_exists VARCHAR2(5 CHAR);
        BEGIN

    execute immediate
    'Select CASE WHEN max(day_i)  IS NULL THEN ''NO'' ELSE ''YES'' END FROM '|| reptable ||' WHERE 1 IN
                  (SELECT max(day_i)
                  FROM '|| reptable ||'
                  where day_i = 1 and flt_dptr_Date_d = :1)' into day_exists
                  using fltdate
          ;
            IF day_exists = 'YES' THEN
              return 1;
            END IF;

    END TABLE_TEST;

This is how I am testing it

Select TABLE_TEST('FDR_REP', '07/25/2015') from dual;

It works properly when I test them individually i.e. when I pass just the table_name or just the date. But for some reason it shows error not a valid month. I don't believe there is any problem with the date thing.

This part works when I just pass the table_name

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;

Upvotes: 0

Views: 66

Answers (1)

Vance
Vance

Reputation: 897

Try converting your fltdate first to date. Here's the revision of your code:

  CREATE OR REPLACE FUNCTION TABLE_TEST (reptable in varchar2, fltdate in varchar2)   RETURN NUMBER
  IS
     day_exists VARCHAR2(5 CHAR);
  BEGIN

     execute immediate
     'Select CASE WHEN max(day_i)  IS NULL THEN ''NO'' ELSE ''YES'' END FROM '|| reptable ||' WHERE 1 IN
     (SELECT max(day_i)
     FROM '|| reptable ||'
     where day_i = 1 and flt_dptr_Date_d = TO_DATE(:1,''MM/DD/YYYY''))' into day_exists
     using fltdate
     ;
     IF day_exists = 'YES' THEN
     return 1;
     END IF;

  END TABLE_TEST;

Upvotes: 1

Related Questions