Changing User through Procedure

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 12 May 2008 9:53 am

Good morning.

I'm creating a procedure for it to be changed the password for all users to a default. But precisely in line with the ALTER, is giving error at the time of creation of the procedure, because it says that the expression is not an allowed value.

below is the script code
Code: Select all
/****************************************************************************************
   Sera gerado um outro script "importa_usuarios.sql" que devera
   ser executado no banco destino.
*****************************************************************************************/


CREATE OR REPLACE PROCEDURE prc_update_senha_usuario
IS
BEGIN
  FOR item IN (SELECT username,password FROM dba_users du,dbasgu.usuarios su where du.username = su.cd_usuario and du.username not in ('DBAMV','DBAPS','DBASGU','MVINTEGRA') order by du.username)
  LOOP
    DECLARE
    n_qtde_usuarios   NUMBER;
    v_clausula_grant  VARCHAR(200);
   
    BEGIN
      select count(*) into n_qtde_usuarios from dba_users where dba_users.username = item.username;
      IF (n_qtde_usuarios > 0)
      THEN
        execute immediate alter user "item.username" identified by values item.password;
        execute immediate v_clausula_grant;
      END IF;
    END;   
  END LOOP;
END;

could someone give a suggestion of how to create this procedure?

Stay with God
rickab7
Location: campo grande

stephany henrique de almeida batista

Poston Mon, 12 May 2008 9:58 am

Good day, try changing this line:
Code: Select all
execute immediate ' alter user "item.username" identified by values item.password; ';
Putting single quotes after the execute immediate.
qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 7 guests