Oracle Package Script
Hello everyone...
Software/Hardware used:
Oracle Application Express 3.1.2.00.02
I'm trying to create a package for an application on oracle APEX!!
Here's the code I used:
create or replace package customer_authentication
as
function customer_exists (p_email in varchar2) return boolean;
procedure insert_new_customer (
p_fullname in varchar2,
p_email in varchar2,
p_password in varchar2);
function customer_authentication_f (
p_username in varchar2 default null,
p_password in varchar2 default null)
return boolean;
end customer_authentication; --end of package specification
create or replace package body customer_authentication
as
function customer_exists (p_email in varchar2)
return boolean;
as
l_aux number;
begin
select id
into l_aux
from customer
where upper(email) = upper (p_email);
return true;
exception when no_data_found then return false;
end customer_exists;
function password_hashing (
p_username in varchar2,
p_password in varchar2)
return varchar2
as
l_password varchar2(4000);
l_salt varchar2(4000) := 'AA8V33PFKQHFZJ4T54HOATVML024FM';
begin
l_password := utl_raw.cast_to_raw(
dbms_obfuscation_toolkit.md5(
input_string => p_password ||
substr(l_sault, 10, 13) ||
p_username ||
substr(l_sault, 4, 10)
)
);
return l_password;
end password_hashing;
procedure insert_new_customer (
p_fullname in varchar2,
p_email in varchar2,
p_password in varchar2)
as
l_encrypted_password varchar(32);
begin
l_encrypted_password := password_hashing (upper(p_email), p_password);
insert into customer (fullname, email, encrypted_password)
values (p_fullname, lower(p_email), l_encrypted_password);
commit;
end insert_new_customer;
function customer_authentication_f (
p_username in varchar2,
p_password in varchar2)
return boolean
as
l_aux number; --boithitiki metablhth
l_encrypted_password varchar2(32)
begin
/* kryptografish toy password */
l_encrypted_password := password_hashing (
p_username => upper(p_username),
p_password => p_password );
select id
into l_aux
from customer
where upper(email)=upper(p_username)
and encrypted_password=l_encrypted_password;
return true;
exception
when no_data_found then return false;
end customer_authentication_f;
end customer_authentication; -- end of package body
I tried running it through the SQL Workshop (commands) but after pressing the "run" button, a pop up window came up with only a Submit button!! (?)
After pressing it, it gave me the message
"Not found
The requested URL /apex/www_flow.show was not found on this server"
So, my question is: Is everything alright with the script? The error is just because I pushed submit without any given data??? :P
Thanx for your time...
Peter
Software/Hardware used:
Oracle Application Express 3.1.2.00.02
ASKED:
June 2, 2011 4:11 PM
UPDATED:
June 3, 2011 8:13 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
There seem to be a couple of errors in the package body, but I’m not familiar with Apex or the SQL Worksheet, so I don’t know if that was the reason for that poupup.
There is an extra semicolon in the customer_exists function header:
And, a semicolon is missing here:
Unfortunatelly this didn’t help…
I ended up re-coding the package but I finally made it work!!!
Thanx anyway!!!