Import Oracle function problem [BOOLEAN not suported?]

[former_member197150]

Hello,

I have found a problem while importing an Oracle function.

Te Oracle function is like this:

FUNCTION my_oracle_dummie_function (...) RETURN BOOLEAN

While importing in DATASTORES --> Import by name, the problem found was:

Function <MY_ORACLE_DUMMIE_FUNCTION> could not be imported. Possible causes: (1) The return value of the function is not a Data Services supported data type; (2) The function name is not specified or is not a valid database stored procedure or function name; (3) The function is invalid; it has either compilation or authorization errors; (4) It is not supported by the version of the current repository.

How can I import this function? Is it possible import a function returning BOOLEAN?

Thanks in advance.

Enrique Jim Ruiz

[former_member187605 · Accepted solution]

There is no Boolean data type in DS. I would write a wrapper function in Oracle to return 1/0 or 'TRUE'/'FALSE'.

[former_member197150 · Accepted solution]

Thanks Dirk

[former_member198401]

Hi Enrique,

Any update. Did you try to implement the logic

Regards

Arun Sasi

[former_member197150]

Hi Arun,

The original Oracle functions works, but it is not possible to import it into DS.

I have created other function that returns 'true' and 'false' like VARCHAR2. VARCHAR2 is admited when we try to import functions into DS.

Thanks

[former_member198401]

Excellent!!

Please close the thread!!

[former_member198401]

Hi Enrique,

Is your Function working @ the Oracle database level.

As per my understanding it should not allow you to compile the function and it should return error like ORA-00904: "FALSE": invalid identifier'. Oracle function returns 0 or 1 only

What you can do is add a conditional if then else logic in the function to return 0 or 1 and when you call the Function in Data services use something like below

CREATE OR REPLACE FUNCTION func_bool (p_empid IN EMP_DATA.EMP_ID%TYPE)

   RETURN NUMBER

AS

   l_var   NUMBER;

BEGIN

   SELECT count(*)

     INTO l_var

     FROM EMP_DATA

    WHERE EMP_ID = p_empid;

   IF l_var = 1

   THEN

      RETURN 1;

   ELSE

      RETURN 0;

   END IF;

END;

SELECT  CASE func_bool(345)

          WHEN 1

             THEN 'TRUE'

          WHEN 0

             THEN 'FALSE'

       END

  FROM DUAL;

SELECT  CASE func_bool(emp_num)

          WHEN 1

             THEN 'TRUE'

          WHEN 0

             THEN 'FALSE'

       END RETURN_CODE

  FROM DUAL;

Regards

Arun Sasi