CODEWITHSUNDEEP
oracle-databaseoracle-apex
user641812
user641812

Reputation: 335

How to execute oracle function in oracle apex online

I have function in oracle apex that returns true or false. When i use sqlcommand function of oracle apex I'm not able to execute the function

function authenticateUser(p_username in varchar2 ,p_password in varchar2 )
        return boolean 
        is 
         p_user Users.USERNAME%type;
        begin
             select USERNAME into p_user from Users  where   upper(USERNAME)=upper(p_username) and  upper(PASSWORD) = upper(p_password);
                 return true;
             exception
                when NO_DATA_FOUND then 
                return false;
        end authenticateUser;

When i run

begin

PKG_AUTHORIZATION.authenticateUser(:p_username,:p_password);
end;

It display error as

ORA-06550: line 3, column 1:
PLS-00221: 'AUTHENTICATEUSER' is not a procedure or is undefined
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SQL", line 1721
1. begin
2. PKG_AUTHORIZATION.authenticateUser(:p_username,:p_password);
3. end;

and When I write

select PKG_AUTHORIZATION.authenticateUser(:p_username,:p_password) from dual;

It throws error as

ORA-00902: invalid datatype

Upvotes: 0

Views: 1625

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

I suppose that function exists within the package, right?

As it returns Boolean, you can't use it directly from SQL, but PL/SQL, such as:

declare
  l_val varchar2(20);
begin
  l_val := case when PKG_AUTHORIZATION.authenticateUser(:p_username,:p_password) then 'true'
                else 'false'
           end;
end;

But, you don't have to worry about it. As you tagged the question with the Oracle Apex tag, I presume you use this function for your own authentication (instead of built-in one). If that's so, you just have to name the function which is supposed to do the job and put its name into the "Authentication function name" item.

Upvotes: 2

Related Questions