Update with sysdate

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Post Reply
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

Good afternoon.

a technical doubt people.
How do I update a column by inserting a sysdate?
I have the update code below:

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

Select all

dt_ligacao1 = :ligar_t1.dt_ligacao1 
Thanks to all.

Léo
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning,

The answer seems very simple or I did not get the problem ...

Select all

update dbamv.tabela 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;
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

SperNega wrote: Good morning,

The answer seems very simple or I did not capture the problem ...

Select all

update dbamv.tabela set  
obs1 = :ligar_t1.obs1, 
dt_consulta = :ligar_t1.dt_consulta   , 
 
dt_ligacao1 = sysdate, -----<<--------- HERE 
 
ligar_n1 = :ligar_t1.ligar_n1 
where nm_nome = :ligar_t1.nm_nome; 
commit;
[ / quote]


Dude, I thought it was simple ...
I had already done this and is not saving, very strange.
I'vê already tried

Select all

to_char(sysdate,'dd/mm/yyyy')
and nothing !!!

When the update does it leaves null this date column ... it saves the other data but the date does not ...

I will put here all 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_não_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 the force !!!

Léo
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Let's try otherwise,

tries to put an exception in the procedure to see if you're giving some mistake.
and
see if there is any base trigger in the table.
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: Let's try otherwise,

tries to put an exception in the procedure to see if you are giving some error.
and
see if there is any base trigger in the table.
Good morning !!!

Spernega I am very new in forms, you can give me a hand to do this exception.

Is it something like that?

Select all

 
exception  
     when :ligar_t1.dt_ligacao1 is null then 
      message ('TESTANDO ERRO.') ; 
     raise form_trigger_failure;  
Implementing after End IF and before End?

Thanks guys !!!

Léo
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

That's right, just before the end;

Did you check if there is any base trigger in this table?
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: That's right before End;

Have you checked if there is any base trigger in this table?
I put:

Select all

 
exception  
when others then 
message ('TESTANDO ERRO.') ; 
raise form_trigger_failure;  
99]] did not give anything ...
went straight, made the update but the empty date field ...

has no trigger in this table.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Try to execute this:

It may be that the update is not locating any registry.

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_não_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; 
      -- 
      if sql%notfound then 
         message ('Nenhum registro localizado') ; 
         pause; 
      else 
         message (sql%rowcount||' registros localizados'); 
         pause; 
      end if; 
      -- 
      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; 
exception  
 when others then 
  message ('TESTANDO ERRO.') ; 
  raise form_trigger_failure; 
end;
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

msg01.JPG
Quote = "SperNega"] Try to execute Thus:

It may be that the update is not locating any registry.

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_não_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; 
      -- 
      if sql%notfound then 
         message ('Nenhum registro localizado') ; 
         pause; 
      else 
         message (sql%rowcount||' registros localizados'); 
         pause; 
      end if; 
      -- 
      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; 
exception  
 when others then 
  message ('TESTANDO ERRO.') ; 
  raise form_trigger_failure; 
end;
/ quote]


I made here ...
Look at the prints what gave. ..
appeared a msg in the second print of please accept, where did this go from ??? ... kkkk
to get crazy !!! ... kkk
Attachments
msg02.JPG
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

kkkkkkkk The message is because of the pause.

I will change the script a little and I already send you again
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Try now:
to follow the steps

Select all

declare  
 alerta number; 
 v_data_atualizada date; 
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_não_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; 
    -- 
    if sql%notfound then 
       message ('Nenhum registro localizado') ; 
       pause; 
    else 
       message (sql%rowcount||' registros localizados'); 
       pause; 
       standard.commit; 
       -- 
       If Form_Success Then 
          message ('Update realizado'); 
          pause;  
          -- 
          begin 
           select dt_ligacao1 into v_data_atualizada  
           from   tabela_nome 
           where nm_nome = :ligar_t1.nm_nome; 
           -- 
           message ('Data atualizada para '||to_char(v_data_atualizada ,'dd/mm/yyyy hh24:mi:ss')); 
           pause;  
          exception 
            when others then 
             message ('Erro ao buscar date '||sqlerrm(sqlcode)); 
             pause;  
          end; 
       else 
          message ('erro no commit '||dbms_error_text); 
          pause;  
       end if; 
    end if; 
    -- 
    :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; 
exception  
when others then 
message ('TESTANDO ERRO.') ; 
raise form_trigger_failure; 
end;
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: Try so now:
To follow the steps

Select all

declare  
 alerta number; 
 v_data_atualizada date; 
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_não_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; 
    -- 
    if sql%notfound then 
       message ('Nenhum registro localizado') ; 
       pause; 
    else 
       message (sql%rowcount||' registros localizados'); 
       pause; 
       standard.commit; 
       -- 
       If Form_Success Then 
          message ('Update realizado'); 
          pause;  
          -- 
          begin 
           select dt_ligacao1 into v_data_atualizada  
           from   tabela_nome 
           where nm_nome = :ligar_t1.nm_nome; 
           -- 
           message ('Data atualizada para '||to_char(v_data_atualizada ,'dd/mm/yyyy hh24:mi:ss')); 
           pause;  
          exception 
            when others then 
             message ('Erro ao buscar date '||sqlerrm(sqlcode)); 
             pause;  
          end; 
       else 
          message ('erro no commit '||dbms_error_text); 
          pause;  
       end if; 
    end if; 
    -- 
    :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; 
exception  
when others then 
message ('TESTANDO ERRO.') ; 
raise form_trigger_failure; 
end;
/ quote]


99] Dude now it was ... made the date update ... SYSDATE !!!
take a look at the prints please.
Attachments
msg03.JPG
msg02.JPG
msg01.JPG
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Well, they did not give all the messages I put, but if it did it is what matters.

may be why we moved to Standard.commit.
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

SperNega wrote: Well, they did not give all the messages I put, but it's done it's what matters.

may be why we move to standard.commit.
But what exactly did the sysdate update?
Continues being dt_ligacao1 = sysdate?
Or was it a begin help?

Select all

begin 
select dt_ligacao1 into v_data_atualizada  
from dbamv.bariatrica_hevv 
where nm_paciente = :ligar_t1.nm_paciente;
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

The update recorded the sysdate (from that moment) in the dt_ligacao1 field.

This Begin was just to verify that Standard.commit was successfully performed and the field updated in the table.

When we perform an INSERT, UPDATE, or DELETE in a forms, which is your case, we use STANDARD.COMMIT ie, it performs the Commit only these actions and but does not The Commit of updates made in Forms base base blocks.

Then it would be good for you to review the forms, to see if there is the need for this update.

If your Block is Base Table, you could only assign Sysdate to the field and give a Commit_Form.
and if it is base table, it could be conflict between the screen and the update.
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: The update recorded the sysdate (from that moment) in the dt_ligacao1 field.

This Begin was just to verify that Standard.commit was successfully performed and the field updated in the table.

When we perform an INSERT, UPDATE, or DELETE in a forms, which is your case, we use STANDARD.COMMIT ie, it performs the Commit only these actions and but does not The Commit of updates made in Forms base base blocks.

Then it would be good for you to review the forms, to see if there is the need for this update.

If your Block is Base Table, you could only assign Sysdate to the field and give a Commit_Form.
and if it is base table, it could be conflict between the screen and the update.


Dear Spernega.

Thank you for your attention.

You helped a lot.

I will check what you said about the block.

Thanks again !!!

Léo
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: The update recorded the sysdate (from that moment) in the dt_ligacao1 field.

This Begin was just to verify that Standard.commit was successfully performed and the field updated in the table.

When we perform an INSERT, UPDATE, or DELETE in a forms, which is your case, we use STANDARD.COMMIT ie, it performs the Commit only these actions and but does not The Commit of updates made in Forms base base blocks.

Then it would be good for you to review the forms, to see if there is the need for this update.

If your Block is Base Table, you could only assign Sysdate to the field and give a Commit_Form.
and if it is base table, it might be giving conflict between the screen and the update.
Dear, I found one thing in the Standard.commit

When I click to upgrade the Form upgrade, but when I click the Exit button and I return to this screen again it asks to save, I believe it is due to Standart.commit.

and when I click Yes in Update it deletes the up-to-date line.

Do you have any tips?

How do I get it back on the same screen he does not show me that question, do you want to update?

Thanks man.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

It's because your block is base table and you're also doing the update straight into the table.
That is, he is doing twice.

You better try handling only by block in forms without the update.
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

Spernega wrote: It's because your block is base table and you're also doing the update straight into the table.
That is, he is doing twice.

You better try to treat only the block in forms without the update.
Sorry for my face ignorance
made this treatment in forms without the update?
being that I am displaying the data of a registration contained in a bank table and need to update that fell by an update.
Thanks again.
Léo
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

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

On the screen there is some other base table bag that is suffering a change or inclusion.
So ask to save before leaving.
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

spernega wrote: When you created the bond block, it was with Data Block Wizard, referencing a table?

On the screen there is some other base table bag that is suffering a change or inclusion.
So ask to save before leaving.
This is the scope I currently have.

4 blocks
main,
Registration,
CONNECTION_T1,
CONNECTION_T2,
CONNECTION_T3

In the main one has nothing, only the buttons for the above blocks.

The registration I will do a registration of a person
in the call_t1 I will see this registration in order of dt_cadastro and to talk to the person ... after trying I will update 2 fields that of observation and the call again yes or no.

The other blocks not for the other attempts to call until the third time.

understood?

After I click Save in the Book Book, click Back ... and the screen returns to the main, which has no update function, and if you click on bind it, it asks for Update, what I had already updated ...

When entering the block does not have any update, the update only activates when you click the button.

está tensoooooooo ... kkkkkkkkkkkk

Thanks !!!
Attachments
tela.JPG
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Leo,

Want to send this forms so I can take a look?
It gets easier to understand.

tries to attach to the message
leo_analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 23 Mar 2016 9:45 am

Spernega wrote: Leo,

Want to send this forms to take a look?
It gets easier to understand.

attempts to attach to the message

The site does not accept extension FMB as an attachment.
If you do not mind, you pass your e-mail that I send you ...
Thanks !!!
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

spernega
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Tue, 19 Jun 2007 2:12 pm
Location: São Paulo - SP

Good morning Leo,

Just for us to close this topic, follow the conclusions:

In Forms, the blocks were base table.
The update that was running was redundant and caused conflict.

Deleted Update The records were saved.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 8 guests