Aprenda PL/SQL

Update with sysdate

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
  

Poston Mon, 29 Aug 2016 5:49 pm

Good afternoon.

A technical question people.
How do I update a column by inserting a sysdate?
I have the update code below:
Code: Select all
update dbamv.tabela set
obs1 = :ligar_t1.obs1,
dt_consulta = :ligar_t1.dt_consulta   ,
dt_ligacao1 = :ligar_t1.dt_ligacao1,
ligar_n1 = :ligar_t1.ligar_n1
where nm_nome = :ligar_t1.nm_nome;
commit;
the column is this dt_ligacao1 =: ligar_t1. dt_ligacao1.

Leo
leo_analista

Poston Tue, 30 Aug 2016 7:53 am

Good day, the answer seems very simple not grasped the problem ... update dbamv. set table = obs1: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
commit;
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 8:22 am

spernega wrote: Good day, the answer seems very simple not grasped the problem ... update dbamv. set table = obs1: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
commit;



Dude I também I thought was simple.
Had already done that and is not saving, very strange that.
I'vê tried to_char (sysdate, ' dd/mm/yyyy ') and nothing!

When does the update he leaves NULL this date column ... it saves other data but the data is not.

I'll put here all the code of the button.

Code: Select all
declare
   alerta number;
   
begin
   
   if :ligar_t1.obs1 is null or :ligar_t1.ligar_n1 is null then
      alerta := show_alert('campo_nulo');
   else
      alerta := show_alert('campo_nao_nulo');      
   
   update tabela_nome set
          obs1 = :ligar_t1.obs1,
          dt_consulta = :ligar_t1.dt_consulta   ,
          dt_ligacao1 = sysdate,
          ligar_n1 = :ligar_t1.ligar_n1
where nm_nome = :ligar_t1.nm_nome;
commit;

:ligar_t1.nm_nome         := null;
:ligar_t1.tel1            := null;
:ligar_t1.tel2            := null;
:ligar_t1.cd_cidade         := null;
:ligar_t1.sexo            := null;
:ligar_t1.dt_preenchimento:= null;
:ligar_t1.dt_cadastro      := null;
:ligar_t1.obs1            := null;
:ligar_t1.dt_consulta      := null;
:ligar_t1.ligar_n1         := null;
      
      end if;
   end;

thanks for your strength!

Leo
leo_analista

Poston Tue, 30 Aug 2016 8:35 am

Let's try another way, tries to put an exception in the procedure to see if it is giving an error.
and See if there's a table TRIGGER.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 8:56 am

spernega wrote: Let's try another way, tries to put an exception in the procedure to see if it is giving an error.
and See if there's a table TRIGGER.


Good morning!!!

Spernega I'm pretty new to forms, you give me a hand to make this exception.

Is something like this?

Code: Select all
exception
     when :ligar_t1.dt_ligacao1 is null then
      message ('TESTANDO ERRO.') ;
     raise form_trigger_failure;
Implementing after the end if and before the end?

Thanks man!!!

Leo
leo_analista

Poston Tue, 30 Aug 2016 9:11 am

That's right, just before the END;

Have you checked if there is any TRIGGER of this table?
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 9:40 am

spernega wrote: That's right, just before the END;

Have you checked if there is any TRIGGER of this table?


Put it like this:
Code: Select all
exception
when others then
message ('TESTANDO ERRO.') ;
raise form_trigger_failure;
gave nothing.
Went straight through, did the update but the date field empty.

Has no trigger in this table.
leo_analista

Poston Tue, 30 Aug 2016 9:51 am

Try to run like this: it may be that the UPDATE is not finding any record.


declare alert number;
begin if: ligar_t1. obs1 is null or: ligar_t1. ligar_n1 is null then alert: = show_alert (' campo_nulo ');
else alert: = show_alert (' campo_nao_nulo ');
--update tabela_nome set obs1 =: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
--if sql% notfound then message (' no record located ');
pause;
else message (sql% rowcount | | ' located ' records);
pause;
end if;
--commit;
--: ligar_t1. nm_nome: = null;
: ligar_t1. on: = null;
: ligar_t1. is: = null;
: ligar_t1.cd _ City: = null;
: ligar_t1 sex: = null;
: ligar_t1. dt_preenchimento: = null;
: ligar_t1. dt_cadastro: = null;
: ligar_t1. obs1: = null;
: ligar_t1. dt_consulta: = null;
: ligar_t1. ligar_n1: = null;
end if;
exception when others then message (' TESTING ERROR. ');
raise form_trigger_failure;
end;
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 10:04 am

msg01.JPG
spernega wrote: tries to run like this: it may be that the UPDATE is not finding any record.


declare alert number;
begin if: ligar_t1. obs1 is null or: ligar_t1. ligar_n1 is null then alert: = show_alert (' campo_nulo ');
else alert: = show_alert (' campo_nao_nulo ');
--update tabela_nome set obs1 =: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
--if sql% notfound then message (' no record located ');
pause;
else message (sql% rowcount | | ' located ' records);
pause;
end if;
--commit;
--: ligar_t1. nm_nome: = null;
: ligar_t1. on: = null;
: ligar_t1. is: = null;
: ligar_t1.cd _ City: = null;
: ligar_t1 sex: = null;
: ligar_t1. dt_preenchimento: = null;
: ligar_t1. dt_cadastro: = null;
: ligar_t1. obs1: = null;
: ligar_t1. dt_consulta: = null;
: ligar_t1. ligar_n1: = null;
end if;
exception when others then message (' TESTING ERROR. ');
raise form_trigger_failure;
end;



I did here.
Look at the prints which gave ...
Appeared a msg on the second print of PLEASE ACCEPT, where did that come from?? ... kkkk I'm going crazy!!! ... kkk
Attachments
msg02.JPG
leo_analista

Poston Tue, 30 Aug 2016 10:12 am

lol the message is about the pause.

I'm going to change a little the script send you again
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 10:21 am

Try it now: to follow steps declare alert number;
v_data_atualizada date;
begin if: ligar_t1. obs1 is null or: ligar_t1. ligar_n1 is null then alert: = show_alert (' campo_nulo ');
else alert: = show_alert (' campo_nao_nulo ');
--update tabela_nome set obs1 =: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
--if sql% notfound then message (' no record located ');
pause;
else message (sql% rowcount | | ' located ' records);
pause;
standard commit;
--If Form_Success Then message (' Update done ');
pause;
--begin select dt_ligacao1 into v_data_atualizada from tabela_nome where nm_nome =: ligar_t1. nm_nome;
--message (updated date for ' || TO_CHAR (v_data_atualizada, ' dd/mm/yyyy hh24: mi: ss '));
pause;
exception when others then get error message (' date ' || sqlerrm (sqlcode));
pause;
end;
else message (' error on commit ' || dbms_error_text);
pause;
end if;
end if;
--: ligar_t1. nm_nome: = null;
: ligar_t1. on: = null;
: ligar_t1. is: = null;
: ligar_t1.cd _ City: = null;
: ligar_t1 sex: = null;
: ligar_t1. dt_preenchimento: = null;
: ligar_t1. dt_cadastro: = null;
: ligar_t1. obs1: = null;
: ligar_t1. dt_consulta: = null;
: ligar_t1. ligar_n1: = null;
end if;
exception when others then message (' TESTING ERROR. ');
raise form_trigger_failure;
end;
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 10:38 am

spernega wrote: Try it now: to follow steps declare alert number;
v_data_atualizada date;
begin if: ligar_t1. obs1 is null or: ligar_t1. ligar_n1 is null then alert: = show_alert (' campo_nulo ');
else alert: = show_alert (' campo_nao_nulo ');
--update tabela_nome set obs1 =: ligar_t1. dt_consulta, obs1 =: ligar_t1. dt_consulta, dt_ligacao1 = sysdate, ligar_n1 =: ligar_t1. ligar_n1 ligar_t1. nm_nome = where: nm_nome;
--if sql% notfound then message (' no record located ');
pause;
else message (sql% rowcount | | ' located ' records);
pause;
standard commit;
--If Form_Success Then message (' Update done ');
pause;
--begin select dt_ligacao1 into v_data_atualizada from tabela_nome where nm_nome =: ligar_t1. nm_nome;
--message (updated date for ' || TO_CHAR (v_data_atualizada, ' dd/mm/yyyy hh24: mi: ss '));
pause;
exception when others then get error message (' date ' || sqlerrm (sqlcode));
pause;
end;
else message (' error on commit ' || dbms_error_text);
pause;
end if;
end if;
--: ligar_t1. nm_nome: = null;
: ligar_t1. on: = null;
: ligar_t1. is: = null;
: ligar_t1.cd _ City: = null;
: ligar_t1 sex: = null;
: ligar_t1. dt_preenchimento: = null;
: ligar_t1. dt_cadastro: = null;
: ligar_t1. obs1: = null;
: ligar_t1. dt_consulta: = null;
: ligar_t1. ligar_n1: = null;
end if;
exception when others then message (' TESTING ERROR. ');
raise form_trigger_failure;
end;




Man now was ... did the update the date ... sysdate!!!
A look at the prints please.
Attachments
msg03.JPG
msg02.JPG
msg01.JPG
leo_analista

Poston Tue, 30 Aug 2016 10:44 am

Well, they didn't all messages that put, but if done is what matters.

Maybe that's why we moved to standard. commit.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 10:53 am

spernega wrote: Well, they didn't all messages that put, but if done is what matters.

Maybe that's why we moved to standard. commit.



But what exactly did the sysdate update?
Still the dt_ligacao1 = sysdate?
Or it was a help that begin?

begin select dt_ligacao1 into v_data_atualizada from bariatrica_hevv where nm_paciente = dbamv?: ligar_t1. nm_paciente;
leo_analista

Poston Tue, 30 Aug 2016 11:09 am

the update recorded the sysdate (of that time) in the dt_ligacao1 field.

That begin was just to check if the standard commit was successfully held and the updated field in the table.

When you perform an insert, update, or delete a forms, that's your case, we use the standard commit that is, it executes the commit only these actions and but does not commit the updates made to the underlying table blocks of forms.

After you review the forms to see if there is the need for this update.

If your block is base table, you could just assign the sysdate to the field and give a commit_form.
And if the base table, could be conflict between the screen and the update.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 11:18 am

spernega wrote: update recorded the sysdate (of that time) in the dt_ligacao1 field.

That begin was just to check if the standard commit was successfully held and the updated field in the table.

When you perform an insert, update, or delete a forms, that's your case, we use the standard commit that is, it executes the commit only these actions and but does not commit the updates made to the underlying table blocks of forms.

After you review the forms to see if there is the need for this update.

If your block is base table, you could just assign the sysdate to the field and give a commit_form.
And if the base table, could be conflict between the screen and the update.




Dear spernega.

Thank you very much for your attention.

You helped a lot.

I will check what you said about the block.

One more time thank you!

Leo
leo_analista

Poston Tue, 30 Aug 2016 12:46 pm

spernega wrote: update recorded the sysdate (of that time) in the dt_ligacao1 field.

That begin was just to check if the standard commit was successfully held and the updated field in the table.

When you perform an insert, update, or delete a forms, that's your case, we use the standard commit that is, it executes the commit only these actions and but does not commit the updates made to the underlying table blocks of forms.

After you review the forms to see if there is the need for this update.

If your block is base table, you could just assign the sysdate to the field and give a commit_form.
And if the base table, could be conflict between the screen and the update.



Dear, I found something in the standard commit when I click Update the form makes the update, but when I click the exit button and come back again on this screen it asks to save, I believe that is because the standard. commit.

And when I click YES in update he deletes the line once updated.

Have any tips?

How do I get when I come back on the same screen it doesn't show me that question, WANT to UPDATE?

Thanks man.
leo_analista

Poston Tue, 30 Aug 2016 12:54 pm

That's why your block is the basis of table and you also doing the update directly in the table.
In other words, doing twice.

You'd better try to treat only the block in FORMS without the update.
spernega
Location: São Paulo - SP

Poston Tue, 30 Aug 2016 2:46 pm

spernega wrote: That's why your block is the basis of table and you also doing the update directly in the table.
In other words, doing twice.

You'd better try to treat only the block in FORMS without the update.


Excuse my ignorance man As would be done this treatment in forms without the update?
Being that I'm viewing the data of a record in a database table and need to update that caadastro with an update.
Again thank you.
Leo
leo_analista

Poston Tue, 30 Aug 2016 2:59 pm

When you created the ligar_t1 block, was with Data Block Wizard, referencing a table?

On the screen is there any other bolco base table that is suffering an alteration or inclusion.
So asks to save before leaving.
spernega
Location: São Paulo - SP


Next

Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests