Procedure Error

pts.
Tags:
Database
IBM DB2
Oracle
Hi Friends, would appreciate if someone could let me know the error in the Stored Procedure. It doesn't compile successfully right now and feel there is a mismatch between the 'Begin' and 'End' keywords. Pl. confirm. 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; Many thanks for having a look :)

Answer Wiki

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

look for ***** modifications or comments

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
****** the variable or I imagine constants below are
****** not declared anywhere!!
****** (c_test_mode,c_test_mode_password )
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
***** the variable only_alpha_characters below
***** is local to the function and cannot be used here
*****
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 !’);
****REMOVE THIS-> end;
end proc_name;

Discuss This Question: 2  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
  • Setirobert
    I also forgot to add that you should prefix your variables with something I usually use the letter v because in your code you got the variable "user" which is an Oracle keyword (function) which returns the current username and that can get very confusing
    30 pointsBadges:
    report
  • Friend12
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/procedure-error/ (0) Comments Read [...]
    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