Procedure Compilation Problem

[Friend33]

Hi Friends,

Would appreciate if you could spare a moment and have a look at the procedure.

The procedure is not compiling successfully and it throws up an error asto the 'Begin' and 'End' keywords.

Could you please have a look and let me know the problem ?

Thank You,

Friend33

Create or replace procedure proc_name IS
Pwd varchar2(8);
user varchar2(8);
v_email varchar2(40);
FUNCTION new_password RETURN VARCHAR IS
only_alpha_characters CONSTANT VARCHAR2 (1) := 'A';

BEGIN

--Provide hard-coded password for test purposes
IF c_test_mode THEN
RETURN c_test_mode_password;
ELSE
--Get a random alphanumeric string 10 characters long
DBMS_RANDOM.SEED (TO_CHAR (SYSDATE, 'MM-DD-YYYY
HH24:MI:SS'));
RETURN DBMS_RANDOM.STRING (only_alpha_characters, 8);
--N.B. no error handling, so any errors propagate
--out, and
--fails safe.
END IF;
END new_password;

Begin
pwd := DBMS_RANDOM.STRING (only_alpha_characters, 10);
select email into v_email from cisoe_users;
DBMS_MAIL.SEND
(NULL,V_email,NULL,NULL,'New_Password',NULL,pwd);

Begin
Select logon_id into user from
cisoe_user_password_history;
Update cisoe_user_password_history
Set password = pwd
Where login_id = user;

Exception
When no_data_found
then DBMS_OUTPUT.PUT_LINE('Please enter
Logon_id');
When too_many_rows
then DBMS_OUTPUT.PUT_LINE('Please enter a
single logon_id');
When OTHERS
then DBMS_OUTPUT.PUT_LINE('There is an error.Pl.
check !');
End;
Exception
When no_data_found
then DBMS_OUTPUT.PUT_LINE('Please enter
Logon_id');
When too_many_rows
then DBMS_OUTPUT.PUT_LINE('Please enter a
single logon_id');
When OTHERS
then DBMS_OUTPUT.PUT_LINE('There is an
error.Pl. check !');
End;
End proc_name;

 

[Dagon]

What exactly is the error you are getting (the ORA- code and text) ?

 

[Dagon]

I think the penultimate "END" may be redundant:

End; <----
End proc_name;

 

[Thargy]

Friend,

your code will not compile for many reasons.

For example, it is not normal to declare a function within a procedure - this makes code difficult to follow at best, and impossible to debug at worst.

The function you are using uses a constant called c_test_mode which is defined in the package specification which I wrote when I first devised the function. Taking a function out of its parent package almost guarantees failure.

You have to design, structure and build code properly for it to have a chance of working. All the best with your modifications to my code.

Regards

Tharg

Grinding away at things Oracular

 

[sem]

I don't agree with you about placing a piece of code into a function within a procedure. Of course, the whole function may be moved outside but the main reason for this should be reusability. When you don't plan to call that snippet from other places but want to structure your code, it's naturally to declare local functions.

But in general I also find that code ugly. It kills any desire to help.


Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Dima,

thank you.

I don't much like what friend33 has done to my code either.

Regards

Tharg

Grinding away at things Oracular