Boolean return value of some function inside SELECT

Question

I am working on a package in PL/SQL.

This is my spec:

TYPE outrec IS RECORD(
    tw_m_id                         NUMBER,
    tw_m_dealer_id                  NUMBER,
    tw_number                       NUMBER,
    check_uid                       NUMBER);
  TYPE outrecset IS TABLE OF outrec;

  FUNCTION report
  (
    p_watermark           IN NUMBER,
    p_param               IN NUMBER,
    p_index               IN NUMBER
  ) RETURN outrecset
    PIPELINED;

This is my body:

FUNCTION func
  (
    p_watermark           => p_watermark,
    p_param               => p_param,
    p_index               => p_index
  )
  
   RETURN outrecset
    PIPELINED IS
    temp outrec;
  
  BEGIN
    before_report(p_watermark           => p_watermark,
                  p_param               => p_param,
                  p_index               => p_index);
  
    FOR c_rec IN (SELECT tw_m_id,
                         tw_m_dealer_id,
                         tw_number,
                         package_name.somefunction(tw_number) AS check_uid

                    FROM table1
                    JOIN table2 rk ON id1 = rk.id2
                    WHERE 1 = 1
                    AND id1 = rk.id2
                    AND id1 = p_param)
    
    LOOP
      temp.tw_m_tw_rechnungskopf_id       := c_rec.tw_m_tw_rechnungskopf_id;
      temp.tw_m_haendler_id_rechnung      := c_rec.tw_m_haendler_id_rechnung;
      temp.check_uid                      := c_rec.check_uid;
      PIPE ROW(temp);
    END LOOP;
  END;

I am trying to get value from package_name.somefunction(tw_number) AS check_uid. The problem is that somefunction returns BOOLEAN value. When I set check_uid to BOOLEAN I get Error: PLS-00382: expression is of the wrong type because of course SQL doesn't support BOOLEAN. I tried :

CASE 
WHEN package_name.somefunction(tw_number) THEN true 
else false
END as check_uid

inside SELECT then I get Error: PL/SQL: ORA-00920: invalid relational operator.

Can someone tell me how to do this PL/SQL is not my strongest side :(

• EDIT: I can't change somefunction to return for an example varchar2 it needs to stay the way it is

Answer 1

Write a simple wrapper function to convert the PL/SQL BOOLEAN to a NUMBER data type that is valid in SQL and expected by your record type and you can move the function call outside the SQL.

CREATE FUNCTION MAP_BOOLEAN( truthy IN BOOLEAN ) RETURN NUMBER DETERMINISTIC
IS
BEGIN
  RETURN CASE truthy
         WHEN TRUE  THEN 1
         WHEN FALSE THEN 0
         ELSE NULL
         END;
END map_boolean;
/

So your specification would be:

CREATE PACKAGE package_name IS
  TYPE outrec IS RECORD(
    tw_m_id        NUMBER,
    tw_m_dealer_id NUMBER,
    tw_number      NUMBER,
    check_uid      NUMBER
  );

  TYPE outrecset IS TABLE OF outrec;

  -- Note: This may be in another package but is here for convenience.
  FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN;

  FUNCTION report
  (
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  ) RETURN outrecset PIPELINED;
END;
/

and the corresponding body would be:

CREATE PACKAGE BODY package_name IS
  FUNCTION somefunction(value IN NUMBER) RETURN BOOLEAN
  IS
  BEGIN
    RETURN TRUE;
  END;

  PROCEDURE before_report(
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  )
  IS
  BEGIN
    NULL;
  END;


  FUNCTION report(
    p_watermark IN NUMBER,
    p_param     IN NUMBER,
    p_index     IN NUMBER
  ) RETURN outrecset PIPELINED
  IS
    temp outrec;
  BEGIN
    before_report(
      p_watermark => p_watermark,
      p_param     => p_param,
      p_index     => p_index
    );

    FOR c_rec IN (
      SELECT tw_m_id,
             tw_m_dealer_id,
             tw_number
      FROM   table1
             JOIN table2 rk ON id1 = rk.id2
      WHERE  id1 = p_param
    )
    LOOP
      temp.tw_m_id        := c_rec.tw_m_id;
      temp.tw_m_dealer_id := c_rec.tw_m_dealer_id;
      temp.check_uid      := MAP_BOOLEAN(
                               PACKAGE_NAME.SOMEFUNCTION( c_rec.tw_number )
                             );
      PIPE ROW(temp);
    END LOOP;
  END;
END;
/

(Note: you also need to update the cursor loop as the values you were selecting did not match the fields of the record.)

db<>fiddle here

Answer 2

Typically you would create an overloaded function in your package that returns 1/0 or Y/N. But since you have no access to the package you can use an inline function in your sql query to do this for you.

create or replace function func (parameter_i VARCHAR2) RETURN BOOLEAN
AS
BEGIN
  return true;
END;
/

WITH
  FUNCTION func_yn(parameter_i VARCHAR2)
  RETURN NUMBER
  IS
    l_return_value BOOLEAN;
  BEGIN
    l_return_value :=func(parameter_i => parameter_i);
    RETURN CASE l_return_value WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;
  END func_yn;
SELECT 
  func_yn('test')
FROM dual;