Oracle Package Script

220 pts.
Tags:
Creating Oracle packages
Oracle
Oracle Application Express
Oracle Package
Hello everyone... 
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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:
    function customer_exists (p_email in varchar2)
    return boolean; -- <===as
    And, a semicolon is missing here:
    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
    68,495 pointsBadges:
    report
  • pforos
    Unfortunatelly this didn't help... I ended up re-coding the package but I finally made it work!!! Thanx anyway!!! ;)
    220 pointsBadges:
    report
  • pforos
    [...] Carlosdl gave one member’s Oracle package script his best [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following