PLSQL: using subquery in if-statement error PLS-00405

Question

I'm getting error pls-00405 when I try to run this code:

BEGIN
  IF :P10_KAART_CODE IN (SELECT KAART_CODE FROM CADEAUKAART) THEN
     RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

There are some similar questions about this but couldn't find a solution for this simple code. Is there another way of writing this without facing a error?

score 3 · Answer 1 · answered Sep 15 '18 at 16:17

PL/SQL doesn't support embedded SQL in if statements. So you'll need to rewrite your code like this:

create or replace function validate_kaart_code 
    (P10_KAART_CODE in CADEAUKAART.KAART_CODE%type)
    return boolean
is
    rv boolean;
    l_code CADEAUKAART.KAART_CODE%type;
BEGIN
    begin
        SELECT KAART_CODE into l_code
        FROM CADEAUKAART
        where KAART_CODE =:P10_KAART_CODE 
        and rownum = 1 -- only necessary if KAART_CODE is not unique
        ;
        rv := TRUE;
    exception
        when no_data_found then
            rv := FALSE;
    end;
    RETURN rv;
END;

I have attempted to reconstruct your whole functionality from the snippet you posted. If this isn't what you intended and you can't convert it to fit your needs please provide more details.

score 1 · Answer 2 · answered Sep 15 '18 at 16:10

1

I'd move the condition to the query itself, and catch a NO_DATA_FOUND exception:

BEGIN
    SELECT * FROM CADEAUKAART WHERE kaart_code = :P10_KAART_CODE;
    RETURN TRUE;
EXCEPTION  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END;

answered Sep 15 '18 at 16:10

Mureinik

297,002
52
306
350

1

This is going to throw error. `INTO` clause is missing. – XING Sep 17 '18 at 12:34

score 1 · Answer 3 · answered Sep 15 '18 at 20:53

1

One more way:

CREATE OR REPLACE FUNCTION IS_KAART_CODE_VALID(pinKAART_CODE IN CADEAUKAART.KAART_CODE%TYPE)
   RETURN BOOLEAN
IS
  nCount  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nCount
    FROM CADEAUKAART
    WHERE KAART_CODE = pinKAART_CODE ;

  RETURN CASE
           WHEN nCount > 0 THEN
             TRUE
           ELSE
             FALSE
         END;
END IS_KAART_CODE_VALID;

answered Sep 15 '18 at 20:53

Bob Jarvis - Слава Україні

48,992
9
77
110

The `return` expression could be simplified to `return nCount > 0;` Also an `exists` subquery might be more efficient than a `count`, depending on the data model. – William Robertson Sep 16 '18 at 08:17

Barbaros Özhan · Accepted Answer · 2018-09-15T17:50:26.677

0

You may try a cursor, alternatively :

DECLARE
  v_flag  boolean := FALSE;  
BEGIN
 FOR c IN ( SELECT KAART_CODE FROM CADEAUKAART )
 LOOP
   IF :P10_KAART_CODE = c.KAART_CODE THEN
     v_flag := TRUE;
     EXIT;
   END IF;
  EXIT WHEN NO_DATA_FOUND;
 END LOOP;
     RETURN v_flag;
END;

By your way using a select statement is not allowed, you might list all the members for the returning values of KAART_CODE such as

IF :P10_KAART_CODE IN ('aAA','BBb','ccC'..) THEN

but which is not preferable and nice to list all matching values .

edited Sep 15 '18 at 17:50

answered Sep 15 '18 at 16:08

Barbaros Özhan

59,113
10
31
55

Sorry but this is not a good solution. The cursor will search the whole table. So the loop will return FALSE unless by chance the record which matches the required value is the first row of the result set. – APC Sep 15 '18 at 17:35
@APC yes, I realized that issue with `RETURN FALSE` for the former case. Thank you so much. Whole table logic is closely related to the OP's style. – Barbaros Özhan Sep 15 '18 at 17:52

PLSQL: using subquery in if-statement error PLS-00405

4 Answers4