Re: Problem Trying to Create Procedure

Mark Smith schreef:
> 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.
>
>

Try @p_chp_proc, not p_chp_proc...

BTW, your code would be "more Oracle" like this: CREATE OR REPLACE PROCEDURE p_chp( encoded IN VARCHAR2 )   IS
  len pls_integer := length(encoded);
  i pls_integer;
  new_password varchar2(30);
BEGIN

• decode password for i IN 1..len loop new_password := new_password||substr(encoded, i*2, 1); end loop;
• change password EXECUTE IMMEDIATE 'ALTER USER db_audit IDENTIFIED BY ' || new_password;

END p_chp;
/

Though, I would look up encryption (several possibilities, based on your version, which I will not guess). You could also check on the mod_plsql entry on http://vanbortel.blogspot.com - it has a password generation and encryption procedure.

Also - the user owning this piece of code must have alter any user privilege...

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...