Oracle (PL/SQL) fuction won’t work

220 pts.
Tags:
APEX
Oracle
Oracle Apex
PL/SQL
I'm trying to run the following script:
function create_new_password (p_email in varchar2)
return varchar2
as
l_password varchar2(12);
l_encrypted_password varchar2(32);
begin
select dbms_random.string('A', 8)
into l_password
from dual;
l_encrypted_password := password_hashing(p_username => upper(p_email), p_password => l_password);
update customer
set encrypted_password=l_encrypted_password
where upper(customer.email)=upper(p_email);
return l_password;
end;
and i'm getting the following error:
Error at line 10: PL/SQL: Statement ignored [table style="empty-cells: show;" cellspacing="0" cellpadding="0" border="0"> [tbody] [tr] [td style="padding: 0px;"> 1. create function create_new_password (p_email in varchar2) 2. return varchar2 3. as[/pre] [/td] [/tr] [/tbody] [/table]
What's wrong?!?!? :(
Note that the password_hashing is a function from a custom
package(customer_authentication)... Does oracle have an import feature that could
help? I tried
l_encrypted_password := customer_authentication.password_hashing(p_username => upper(p_email), p_password => l_password); but still nothing!! :(
Thanx in advance
Peter


Software/Hardware used:
Oracle APEX 3.1.2
ASKED: June 3, 2011  8:53 PM
UPDATED: June 6, 2011  8:55 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Problem solved!!!
All I needed to do was to (using the Oracle sqlDeveloper) stick my code to the left margin of the window that had the code!!!
I really don’t understand why, maybe a compiler error??? For all I care, it works now….

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
    Chances are that the password_hashing function has errors. Make sure it is compiled and 'valid', and make sure you have the proper permissions to see it and execute it. And yes, you have to use the <package>.<function> syntax.
    65,110 pointsBadges:
    report
  • pforos
    I used that syntax and it still didn't work!! I don't understand why... I also tried to insert the function and the procedure that follows into the package!! I created this package:
    create or replace package create_email
    as
    
    function create_new_password (p_email in varchar2)
    return varchar2;
    
    procedure email_new_password (
    p_email in varchar2,
    p_customer_service_email in varchar2,
    p_password in varchar2);
    
    end create_and_email_new_password;
    /
    
    create or replace package body create_email
    as
    
    function create_new_password (p_email in varchar2)
    return varchar2
    as
    l_password varchar2(12);
    l_encrypted_password varchar2(32);
    begin
    select dbms_random.string('A', 8)
    into l_password
    from dual;
    l_encrypted_password := password_hashing(p_username => upper(p_email), p_password => l_password);
    update customer
    set encrypted_password=l_encrypted_password
    where upper(customer.email)=upper(p_email);
    return l_password;
    end create_new_password;
    
    procedure email_new_password (
    p_email in varchar2,
    p_customer_service_email in varchar2,
    p_password in varchar2)
    as
    email_subject varchar(50);
    email_body varchar(200);
    begin
    email_subject := 'Your new password for Online Store';
    email_body := 'Dear Customer, '|| chr(10) || 
    'Your new password for Online Store is '||
    p_password||chr(10)||
    'Sincerely, '||chr(10)||' Online Store';
    APEX_MAIL.SEND (              <==== LINE 53
    p_to => p_email,
    p_from => p_customer_service_mail,
    p_body => email_body,
    p_subj => email_subject
    );
    commit; 
    end create_new_password;
    
    end create_email;
    /
    
    but i had an error at line 53: PL/SQL: Statement ignored !! What's wrong this time??? The syntax seems to be correct!! I start to get the feeling that Oracle doesn't like me..... :P
    220 pointsBadges:
    report
  • pforos
    Sry this is the code I last used, there's same silly mistakes into the previous post....
    
    create or replace package body create_email
    as
    
    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_salt, 10, 13) || 
    p_username || 
    substr(l_salt, 4, 10)
    )
    );
    return l_password;
    end password_hashing;
    
    function create_new_password (p_email in varchar2)
    return varchar2
    as
    l_password varchar2(12);
    l_encrypted_password varchar2(32);
    begin
    select dbms_random.string('A', 8)
    into l_password
    from dual;
    l_encrypted_password := password_hashing(p_username => upper(p_email), p_password => l_password);
    update customer
    set encrypted_password=l_encrypted_password
    where upper(customer.email)=upper(p_email);
    return l_password;
    end create_new_password;
    
    
    procedure email_new_password (
    p_email in varchar2,
    p_customer_service_email in varchar2,
    p_password in varchar2)
    as
    email_subject varchar(50);
    email_body varchar(200);
    begin
    email_subject := 'Your new password for Online Store';
    email_body := 'Dear Customer, '|| chr(10) || 
    'Your new password for Online Store is '||
    p_password||chr(10)||
    'Sincerely, '||chr(10)||' Online Store';
    APEX_MAIL.SEND (
    p_to => p_email,
    p_from => p_customer_service_mail,
    p_body => email_body,
    p_subj => email_subject
    );
    commit; 
    end email_new_password;
    
    end create_email;
    /
    
    
    220 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