Oracle SQL. User defined function will not run in SELECT statement, but will run if wrapped in procedure

Question

I have an oracleSQL database over a voter register. To check if a voter has previously voted I created a function PreviouslyVoted(election_code, electorate_code, voter_id) seen below.

create or replace FUNCTION previouslyVoted(election_code_in IN NUMBER, electorate_id_in IN NUMBER, voter_id_in IN NUMBER)
RETURN BOOLEAN IS
voter_id_temp NUMBER;
BEGIN
    SELECT COUNT(*) INTO voter_id_temp
    FROM ballot_issuance bi
    WHERE bi.electorate_id = electorate_id_in
    AND bi.election_serial_number = election_code_in
    AND bi.voter_id = voter_id_in;

RETURN voter_id_temp > 0 ;
END;

Now I want to call it from a PHP server.

I tried running sending the following query:

Select previouslyVoted(" .ELECTION ."," .ELECTORATE .", $oracle_voter_id) "Result" FROM dual;

Which did not work. I get the error below ERROR:

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
Error at Line: 1 Column: 8

I tried going to sqldeveloper and ran

Select previouslyVoted(20190404,1,13321) "Result" FROM dual;

Again with the same error. I tried expanding the expression to

SELECT
    "S4026115"."PREVIOUSLYVOTED"(20190404, 1, 13321) "Result"
FROM
    "SYS"."DUAL" "A1";

But get the same error again again. Here comes the funny thing: If i wrap it in a procedure like below, It works completely fine.

create or replace PROCEDURE runprevvoted
AS
   has_voted BOOLEAN;
BEGIN
   has_voted := previouslyVoted(20190404, 1, 13321);
   IF has_voted THEN
      DBMS_OUTPUT.PUT_LINE('This voter has previously voted.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('This voter has not previously voted.');
   END IF;
END;

Hope someone can help me with some insight on what I am doing wrong! Thank you in advance

Answer 1

You call a function "previouslyVoted" in the beginning of your question but you refer "has_voted" in the wrapper. I'm assuming they're the same ? The issue is most probably that the function returns a boolean and SQL does not have a boolean datatype prior to 23c. pl/sql does have a boolean datatype. So what is happening is that the pl/sql function executes successfully but the SQL statement errors out since it doesn't know the data type.

Example:

koen19c>set serveroutput on size 999999
koen19c>CREATE OR REPLACE FUNCTION istrue RETURN BOOLEAN
  2  AS
  3  BEGIN
  4    RETURN true;
  5  END;
  6* /

Function ISTRUE compiled

koen19c>BEGIN 
  2    IF istrue() THEN
  3      dbms_output.put_line('it is true !');
  4    END IF;
  5  END;
  6* /
it is true !


PL/SQL procedure successfully completed.

koen19c>SELECT istrue() from dual;

Error starting at line : 1 in command -
SELECT istrue() from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"
*Cause:    
*Action:
koen19c>

Solution, modify the function so it returns a string ('Y'/'N', 'true'/'false') or a number (1/0)