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???
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?!
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;
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;
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
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;
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
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 ....
