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:
- Those calls to
UPPER()
andTRIM()
in the calls toGET_HASH()
are misplaced. Just use them in the body ofGET_HASH()
itself. - The procedure
GET_HASH()
has a signature of(P_PASSWORD IN VARCHAR2,P_USERNAME IN VARCHAR2)
but you always invoke it asget_hash (p_username, p_password)
. Of course, being consistently wrong means you will get the "right" result but it's still an error. - 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?
精彩评论