开发者

Compilation error in Apex package

I have created one package in oracle apex. While compiling i got an error msg like this..

please help me.

create or replace package body "PKG_APP_SECURITY" is
procedure ADD_USER(P_PASSWORD IN VARCHAR2
      ,P_USERNAME IN VARCHAR2
)
as
begin
 INSERT INTO P_USERS(username, password)
    VALUES (UPPER (p_username),get_hash(TRIM(p_username), p_password));

COMMIT; 
EXCEPTION
   WHEN OTHERS THEN ROLLBACK; RAISE;
end ADD_USER;

function VALID_USER(P_PASSWORD IN VARCHAR2
      ,P_USERNAME IN VARCHAR2
) return BOOLEAN

as
begin
 VALID_USER2(UPPER(p_username),p_password);
 RETURN TRUE;
EXCEPTION 
 WHEN OTHERS THEN RETURN FALSE;
end VALID_USER;

function GET_HASH(P_PASSWORD IN VARCHAR2
      ,P_USERNAME IN VARCHAR2
) RETURN VARCHAR2 AS 
    BEGIN 
    RETURN 
       DBMS_OBFUSCATION_TOOLKIT.md5(input_string => UPPER (p_username) || '/' || UPPER (p_password));
end GET_HASH;

procedure LOGIN(P_FLOW_PAGE IN VAR开发者_开发知识库CHAR2
      ,P_PASSWORD IN VARCHAR2
      ,P_SESSION_ID IN VARCHAR2
      ,P_USERNAME IN VARCHAR2
)
is
begin
-- THIS PROVIDES AUTHENTICATION
     wwv_flow_custom_auth_std.login 
       (p_uname => p_uname
          ,p_password => p_password
            ,p_session_id => p_session_id
           ,p_flow_page => p_flow_page || ':' || 1);
end LOGIN;

procedure VALID_USER2(       P_PASSWORD IN VARCHAR2
      ,P_USERNAME IN VARCHAR2
)
as
begin
 SELECT '1'
 INTO v_dummy 
 FROM P_USERS
 WHERE UPPER(username) = UPPER (p_username)
 AND password= get_hash (p_username, p_password);

EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
   raise_application_error(-20000, 'Invalid username / password.');
end VALID_USER2;

end "PKG_APP_SECURITY";​


An educated guess. The most likely source of a compilation error is the call to VALID_USER2() in the VALID_USER() procedure. If, as I suspect, VALID_USER2() is not declared in the package spec this will hurl a PLS-00313 exception, "not declared in this scope".

Private functions must be declared before they are invoked. The alternate is forward declaration, but that has always struck me as unnecessary duplication.


There are a number of things which are troubling about your implementation. In ascending order of severity:

  1. Those calls to UPPER() and TRIM() in the calls to GET_HASH() are misplaced. Just use them in the body of GET_HASH() itself.
  2. The procedure GET_HASH() has a signature of (P_PASSWORD IN VARCHAR2,P_USERNAME IN VARCHAR2) but you always invoke it as get_hash (p_username, p_password). Of course, being consistently wrong means you will get the "right" result but it's still an error.
  3. The most worrisome thing is that you appear to be hand-rolling an authentication scheme instead of using the built-in Oracle Application Express Account Credentials. Why are you doing that?
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜