CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
David Neira
David Neira

Reputation: 145

use a validation function for an element in apex 5.0

I am trying to implement a validation on an application item in APEX 5.0. I am using a PL/SQL function returning a boolean

begin
  if exists (select id_referencia 
               from items
              where id_referencia = :P2_REFERENCIA) 
  then
    return true;
  else
    return false;
  end if;
end;

When I submit my page, I get the following error

ORA-06550: line 2, column 4: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only ORA-06550: line 2, column 1: PL/SQL: Statement ignored

Upvotes: 2

Views: 4529

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

if exists isn't valid PL/SQL syntax.

You could do a count(*) instead

declare
  l_cnt integer;
begin
  select count(*)
    into l_cnt
    from items
   where id_referencia = :P2_REFERENCIA;

  if( l_cnt >= 1 )
  then
    return true;
  else 
    return false;
  end if;
end;

If you really wanted to do an exists, you'd do something like this. I don't know why you'd want to do this assuming that id_referencia is the primary key of the table. But you could

declare
  l_exists integer;
begin
  begin
    select 1
      into l_exists
      from dual
     where exists( select 1
                     from items
                    where id_referencia = :P2_REFERENCIA );
  exception
    when no_data_found
    then
      l_exists := 0;
  end;

  if( l_exists = 1 )
  then 
    return true;
  else
    return false;
  end if;
end;

Upvotes: 2

brenners1302
brenners1302

Reputation: 1478

If you want to use EXISTS in a PL/SQL function returning BOOLEAN,try this

DECLARE
  v_exist NUMBER;
BEGIN

SELECT 1 
INTO v_exist
FROM DUAL WHERE EXISTS (select id_referencia 
                                 from items
                                 where id_referencia = :P2_REFERENCIA)

  IF v_exist IS NOT NULL
  THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;

Upvotes: 1

Related Questions