How to use remote database connection when declare variable in function of type %type?

I want to connect to oracle database 10g through oracle database 19c using database link and oracle apex and I have this function :

create or replace FUNCTION AUTHENTICATE_USER

  (p_username in varchar2, 

   p_password in varchar2)

return boolean

is

  l_user_name       sys_users.user_id%type    := upper(p_username);

  l_password        sys_users.user_password%type;

  l_hashed_password varchar2(1000);

  l_count           number;

begin

-- Returns from the AUTHENTICATE_USER function 

--    0    Normal, successful authentication

--    1    Unknown User Name

--    2    Account Locked

--    3    Account Expired

--    4    Incorrect Password

--    5    Password First Use

--    6    Maximum Login Attempts Exceeded

--    7    Unknown Internal Error

--

-- First, check to see if the user exists

    select count(*) 

      into l_count 

      from sys_users@kaash1

      where user_name = l_user_name;

      

     if l_count > 0 then

          -- Hash the password provided

          l_hashed_password := hash_password(l_user_name, p_password);

 

          -- Get the stored password

          select user_password 

            into l_password 

            from sys_users@kaash1

           where user_name = l_user_name;

  

          -- Compare the two, and if there is a match, return TRUE

          if l_hashed_password = l_password then

              -- Good result. 

              APEX_UTIL.SET_AUTHENTICATION_RESULT(0);

              return true;

          else

              -- The Passwords didn't match

              APEX_UTIL.SET_AUTHENTICATION_RESULT(4);

              return false;

          end if;

  

    else

          -- The username does not exist

          APEX_UTIL.SET_AUTHENTICATION_RESULT(1);

          return false;

    end if;

    -- If we get here then something weird happened. 

    APEX_UTIL.SET_AUTHENTICATION_RESULT(7);

    return false;

exception 

    when others then 

        -- We don't know what happened so log an unknown internal error

        APEX_UTIL.SET_AUTHENTICATION_RESULT(7);

        -- And save the SQL Error Message to the Auth Status.

        APEX_UTIL.SET_CUSTOM_AUTH_STATUS(sqlerrm);

        return false;

        

end authenticate_user;
/

Now I run SELECT statement only and its working

but the function not working

where I will add the remote database name kaash1 to the following variables :

 l_user_name       sys_users.user_name%type    := upper(p_username);
    
 l_password        sys_users.user_password%type;

I run also to check the function in SQL the select statements return value but when run the function I got error invalid datatype

Error at line 1/8: ORA-00902: invalid datatype

select AUTHENTICATE_USER('admin','2023') FROM dual;

how to solve this error and run the function please ?

Solution

Invalid datatype error is due to the fact that there's no Boolean datatype at SQL level - you can only use it in PL/SQL.

Simplified, this is what you have:

SQL> create or replace function f_Test
  2    return boolean is
  3  begin
  4    return true;
  5  end;
  6  /

Function created.

As you've already noticed, you can't use it at SQL level:

SQL> select f_test from dual;
select f_test from dual
       *
ERROR at line 1:
ORA-00902: invalid datatype

But, in PL/SQL, it works:

SQL> set serveroutput on
SQL> begin
  2    dbms_output.put_line(case when f_test then 'result is true'
  3                              else 'result is false'
  4                         end);
  5  end;
  6  /
result is true

PL/SQL procedure successfully completed.

SQL>

Alternatively, if you really need to use that function at SQL level, modify function so that it returns another datatype, e.g.

NUMBER, so you could return 1 for true and 0 for false, or
VARCHAR2, so you'd return Y for true and N for false
or any other option you find appropriate

As of declaring a variable that references column's datatype over a database link: that would be

l_user_name sys_users.user_name@kaash1%type;