Create a course for Update in some fields

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
Eduardo Fernandes
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Wed, 08 Dec 2010 10:36 pm
Location: Rio de Janeiro

Personally, before you had created this topic to create an update .. http://en.glufke.net/oracle/viewtopic.php?t=7262

Now I need to do a cursor because I will want to do the update in more than 1 field .. only I still have difficulties in the creation of cursor ...

I have to replace the select

Select all

 
 
  BEGIN 
   
   select password, 
   into v_coleta_user 
   from sec_membershipusers 
   where username = 'WSTSCC'; 
   
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN '1 - ERRO AO TENTAR TROCAR SENHA' || SQLERRM; 
     
  END; 
 
  BEGIN 
    UPDATE sec_membershipusers 
       SET password = v_coleta_user 
     where username = p_NOME_USER; 
   
  EXCEPTION 
    WHEN OTHERS THEN 
      RETURN 'ERRO - Erro na troca' || SQLERRM; 
  END; 
 
The scheme is the same precise The User Fields 'Westsscc' and make an update in the same user fields that enters the p_nome_user parameter ... only now not only with the password field but it has the Islockedout, CreationDate, LastLogindate fields ... you could give a help???
nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Thu, 23 Dec 2010 2:06 pm
Location: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

Eae dude ...

I just answered your other topic ... I do not know if I hit ... but for what I understood ... to make several ways ... I already I had a more or less mounted ... so I followed this line ...
See if this solves you ... I used a cursor, but as it is only the data of a record, it could be with a select into. .but ... let's see what the ..
going wrong or right, a return..ok?!

Select all

 
CREATE OR REPLACE FUNCTION SC_FNC_TROCA_SENHA(p_NAME_USER in VARCHAR2)  
  return varchar2 IS  
 
 cursor c_user is  
  select PASSWORD,ISLOCKEDOUT, CREATIONDATE, LASTLOGINDATE  
   from sec_membershipusers  
   where username = 'WSTSCC'; 
 
BEGIN  
 
 For rs in C_USER Loop 
  UPDATE sec_membershipusers 
     SET PASSWORD      = rs.password, 
         ISLOCKEDOUT   = rs.ISLOCKEDOUT 
         CREATIONDATE  = rs.CREATIONDATE 
         LASTLOGINDATE = rs.LASTLOGINDATE 
   WHERE USERNAME = p_NAME_USER; 
    
    
   IF SQL%ROWCOUNT = 0 THEN 
      RETURN 'NENHUMA LINHA ALTERADA';  
   ELSE  
   COMMIT; 
   RETURN 'SENHA ALTERADA COM SUCESSO!';  
   END IF; 
 
END SC_FNC_TROCA_SENHA;  

Hugs!;)
: -O
paulochagas
Moderador
Moderador
Posts: 86
Joined: Wed, 15 Mar 2006 2:46 pm
Location: São Paulo - SP
Paulo Chagas Filho
__________________

Analista Funcional / Desenvolvedor Oracle EBS
MSN - paulochagas@hotmail.com
Gtalk - pachafi@gmail.com
Skype - paulochagas

Select all

 
BEGIN 
   FOR r1 IN (SELECT * 
                FROM sec_membershipusers 
               WHERE username = 'WSTSCC') 
   LOOP 
      UPDATE sec_membershipusers 
         SET password = r1.password 
       WHERE username = r1.username; 
   END LOOP; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RETURN 'ERRO - Erro na troca' || SQLERRM; 
END; 
nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Thu, 23 Dec 2010 2:06 pm
Location: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

Malz aí ... eskeci to put a treatment case of some shit ...
type, I can not compile aqui to run with tranquilade because I do not have grant to create tables ... bag

I added the treatment to any other exception ...

Select all

 
CREATE OR REPLACE FUNCTION SC_FNC_TROCA_SENHA(p_NAME_USER in VARCHAR2)  
  return varchar2 IS  
 
 cursor c_user is  
  select PASSWORD,ISLOCKEDOUT, CREATIONDATE, LASTLOGINDATE  
   from sec_membershipusers  
   where username = 'WSTSCC'; 
 
BEGIN  
 
 For rs in C_USER Loop 
  UPDATE sec_membershipusers 
     SET PASSWORD      = rs.password, 
         ISLOCKEDOUT   = rs.ISLOCKEDOUT 
         CREATIONDATE  = rs.CREATIONDATE 
         LASTLOGINDATE = rs.LASTLOGINDATE 
   WHERE USERNAME = p_NAME_USER; 
    
    
   IF SQL%ROWCOUNT = 0 THEN 
      RETURN 'NENHUMA LINHA ALTERADA';  
   ELSE  
   COMMIT; 
   RETURN 'SENHA ALTERADA COM SUCESSO!';  
   END IF; 
    
   Exception 
   When other then 
   return 'Erro: '||sqlerrm; 
 
END SC_FNC_TROCA_SENHA;  
ABS: -O
Eduardo Fernandes
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Wed, 08 Dec 2010 10:36 pm
Location: Rio de Janeiro

Beauty face ...

I gave a scrambled here in the code and was like this ...

Select all

 
CREATE OR REPLACE FUNCTION SC_FNC_TROCA_SENHA(p_NOME_USER in VARCHAR2) 
  return varchar2 IS 
  v_coleta_user varchar2(300); 
 
BEGIN 
 
  BEGIN 
    select password 
      into v_coleta_user 
      from sec_membershipusers 
     where username = p_NOME_USER; 
   
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      return 'ERRO - Usuário não existe na base'; 
     
  END; 
   
    DECLARE 
     
      CURSOR C_COLETA IS 
        select LOCKEDREASONID, 
               PASSWORD, 
               ISLOCKEDOUT, 
               CREATIONDATE, 
               LASTLOGINDATE, 
               LASTACTIVITYDATE, 
               LASTPASSWORDCHANGEDDATE, 
               LASTLOCKOUTDATE, 
               FAILEDPASSATTCOUNT, 
               FAILEDPASSATTWINDOWSTART, 
               LASTCHANGE, 
               LASTCHANGEUSER, 
               MEMBERSHIPUSERSTATUSID 
          from sec_membershipusers 
         where username = 'WSTSCC'; 
 
     
    BEGIN 
     
      for r1 in C_COLETA loop 
       
        UPDATE sec_membershipusers 
           SET PASSWORD                 = r1.PASSWORD, 
               LOCKEDREASONID           = r1.LOCKEDREASONID, 
               ISLOCKEDOUT              = r1.ISLOCKEDOUT, 
               CREATIONDATE             = r1.CREATIONDATE, 
               LASTLOGINDATE            = r1.LASTLOGINDATE, 
               LASTACTIVITYDATE         = r1.LASTACTIVITYDATE, 
               LASTPASSWORDCHANGEDDATE  = r1.LASTPASSWORDCHANGEDDATE, 
               LASTLOCKOUTDATE          = r1.LASTLOCKOUTDATE, 
               FAILEDPASSATTCOUNT       = r1.FAILEDPASSATTCOUNT, 
               FAILEDPASSATTWINDOWSTART = r1.FAILEDPASSATTWINDOWSTART, 
               LASTCHANGE               = r1.LASTCHANGE, 
               LASTCHANGEUSER           = r1.LASTCHANGEUSER, 
               MEMBERSHIPUSERSTATUSID   = r1.MEMBERSHIPUSERSTATUSID 
         WHERE USERNAME = p_NOME_USER; 
       
      end loop; 
     
    EXCEPTION 
      WHEN OTHERS THEN 
        RETURN '1 - ERRO AO TENTAR TROCAR SENHA' || SQLERRM; 
       
    END; 
   
    RETURN 'SENHA MODIFICADA'; 
   
  END SC_FNC_TROCA_SENHA; 
 
..

If you have any idea that I can improve .. it will be welcome
nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Thu, 23 Dec 2010 2:06 pm
Location: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

Good face ... if you're running and amanah is Christmas, so ta beleza!
AUEHAUHEHUAE

I only understood the need for this search:

Select all

 
BEGIN  
    select password  
      into v_coleta_user  
      from sec_membershipusers  
     where username = p_NOME_USER;  
   
  EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
      return 'ERRO - Usuário não existe na base';  
     
  END;  
In my view, it seems unnecessary, because you seek the data in variable and not It uses more ....

No more, a Merry Christmas and ABS!
: -O
Eduardo Fernandes
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Wed, 08 Dec 2010 10:36 pm
Location: Rio de Janeiro

hauah ... because it's very good guy like that !!


Then, I did this search to treat separately if the user is not found ...

Ex: He typed 'Shauahsua'
will return = 'Error - User does not exist at the base' ...
Eduardo Fernandes
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Wed, 08 Dec 2010 10:36 pm
Location: Rio de Janeiro

Guys, I just forgot to thank everyone !!!

Thank you very much !!! And have a great Christmas !!!!!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest