CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex-5
Abdirizak Obsiye
Abdirizak Obsiye

Reputation: 285

Oracle APEX 5 Create Function ERROR, what could it be?

Oracle Apex 5 SQL commands gives this error when I try to run the below function code : ‘ORA-24344: success with compilation error’

I've tried writing it in all caps and still the issues appears. What do you think I did wrong? Help would be really appreciated.

CREATE OR REPLACE FUNCTION al_auth_authentication (p_username IN VARCHAR2, p_password IN VARCHAR2)
    RETURN boolean
    IS
    1_user AL_USER_LOGIN.USER_NAME%type := UPPER(p_username);
    1_pwd  AL_USER_LOGIN.USER_PASSWORD%type;
    1_id   AL_USER_LOGIN.USER_ID%type;
    BEGIN
    SELECT USER_ID, USER_PASSWORD
    INTO 1_id, 1_pwd
    FROM AL_USER_LOGIN
    WHERE UPPER(USER_NAME) = 1_user;
    RETURN 1_pwd = dbms_crypto.hash (UTL_I18N.string_to_raw(p_password || 1_id || UPPER(p_username), 'AL32UTF8'), dbms_crypto.hash_sh1);
    EXCEPTION
    WHEN no_data_found then return false;
    END al_auth_authentication;

Upvotes: 0

Views: 226

Answers (1)

William Robertson
William Robertson

Reputation: 16001

PL/SQL variables (and Oracle identifiers in general) can't begin with a number, so 1_user, 1_pwd and 1_id are not valid. Perhaps you meant l_user, l_pwd and l_id?

You should include the full compilation error in your question, though. You can either compile it in a development tool that highlights compilation errors, such as SQL Developer or PL/SQL Developer, or in SQL*Plus and use the show errors command, or else just query user_errors (or all_errors if you are not connected as the owner).

There is no need to code in block capitals unless you are working in COBOL, so please don't.

Upvotes: 0

Related Questions