Doubt Bank session.

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

Poston Tue, 23 Oct 2007 2:33 pm

Good afternoon guys, I was wondering if I have to do like this: insert into select * from movement movement;

and then in another Sql Plus with another session to see how many records have been inserted into the table?
meguelito
Location: Santa Catarina

Att.:
Alan Juliano Metzger
Programador Oracle
Inside System Informática
Msn/E-mail: alanjuliano@yahoo.com.br

Poston Tue, 23 Oct 2007 2:52 pm

Brother, since both have the same amount (and order) of columns, Yes, perfectly.

Code: Select all

INSERT INTO TESTE SELECT * FROM TAB_TESTE;

otherwise, fix the columns in place of the \"*\".

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

Poston Tue, 23 Oct 2007 2:55 pm

I think you misunderstood my question.

I want to know if I have to see how many records already form inserted by an insert without a commit.
meguelito
Location: Santa Catarina

Att.:
Alan Juliano Metzger
Programador Oracle
Inside System Informática
Msn/E-mail: alanjuliano@yahoo.com.br

Poston Tue, 23 Oct 2007 3:08 pm

And ai meguelito, beleza?

q I know not how to see the result of another session unless you commit and the data ...
well, look on the net about \"set auto commit\", not sure q will solve your problem.
you couldn't mount a block anonymous to do this? If you can not it's hard to do that you need ...

[] s
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Tue, 23 Oct 2007 3:18 pm

Sorry brother, but seeing in another session, without commit, unknown and, guess there's no way to do ...
There is no other way to solve your problem?
What do you really need?
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Mon, 29 Oct 2007 12:42 pm

I don't know if this will work, but try to do the following: declares a variable and each row she recbe \"+1\" then you have the PL stop when this variable reaches a value that you want (e.g. the value of \"x\" rows inserted), but you won't see the data that have been entered, it will only be able to see the data after the commit.

Also I have no idea.

Hug
Raphaelbi
Location: Rio de Janeiro - RJ

Raphael Porto

Poston Mon, 29 Oct 2007 5:50 pm

Depending on how much you need it, it would be possible to create a table to control the amount of inserted records, fueled by a second function that uses AUTONOMOUS_TRANSACTION, so there you can perform the commit the amount of records, without performing the commit in the main application.

Code: Select all
procedure p_atualiza_contagem (p_qtde_atual in number)
is
   pragma autonomous_transaction;
begin
   update tabela_controle set quantidade = p_qtde_atual;
   commit;
end p_atualiza_contagem;

procedure p_insere_movimento
is
   v_qtde number := 0;
begin
   for x in
   ( select rowid rid from movimento
   ) loop
      insert into movimento
      select * from movimento where rowid = x.rid
      ;

      v_qtde := v_qtde + 1;

      -- Só atualizar a tabela a cada n registros, para não afetar muito mais a performance...
      if (mod(v_qtde, 1000) = 0) then
         p_atualiza_contagem(v_qtde);
      end if;

   end loop;
end p_insere_movimento;
But it is very likely that the above solution is slower than the original solution ... but anyways is there to tip.
rogenaro
Location: Londrina - PR

Rafael O. Genaro



Return to SQL

Who is online

Users browsing this forum: No registered users and 1 guest