Re: Problem Trying to Create Procedure

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 20 Sep 2006 21:01:43 +0200
Message-ID: <ees36p$jfj$03$1@news.t-online.com>

Mark Smith schrieb:

> "Mark Smith" <marksmit_at_nortelnetworks.com> wrote in message 
> news:eepd4d$al5$1_at_zcars129.ca.nortel.com...

>> I am a newbie, so this may be a simple question. I have a sql script
>> called p_chp_proc.sql, which creates a procedure called p_chp:
>>
>> CREATE OR REPLACE PROCEDURE p_chp( encoded IN VARCHAR )
>> IS
>> len int := length(encoded);
>> i int;
>> new_password varchar(30);
>> BEGIN
>> -- decode password
>> for i IN 1..len loop
>> new_password := substr(encoded, i*2, 1);
>> end loop;
>>
>> -- change password
>> EXECUTE IMMEDIATE 'ALTER USER db_audit IDENTIFIED BY ' || new_password;
>>
>> END p_chp;
>>
>>
>> When I try and run the script I get the error "SP2-0042: unknown command
>> "cr_p_chp" - rest of line ignored." Does anyone know what I am missing?
>> Thanks a lot.

> Typo...the error actually reads "SP2-0734: unknown command beginning 
> "p_chp_proc..." - rest of line ignored." 
> 
> 

The error message you posted has nothing to do with the code above - it caused most probably with some other statements. Procedure compiles without any errors. However, your code is not working. Beginning from the second half of iterations in your loop, you assign consequently NULL to new_password. As result, execute immediate will fail, because an identifier with not NULL length is expected after IDENTIFIED BY. In addition, user executing this stored procedure should have an explicit ( not via role ) granted ALTER USER privilege.

Best regards

Maxim Received on Wed Sep 20 2006 - 14:01:43 CDT