Table without PK. how to identify the lines?

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

Poston Tue, 05 Sep 2006 3:30 pm

Following ... have some tables where there is no column to identify the records. The problem is that I can not change the table to ascrecentar a column, and I intend to pick up some records every 10 minutes to play on another table, as have no column that does identify the record already selected before I wonder if anyone has any tricks that can help me.

Att, Ricardo
rnovais
Location: sao paulo - sp

Poston Tue, 05 Sep 2006 11:22 pm

create a new table with the same columns as the original + an identifier column.
Create a trigger AFTER INSERT in original table, inserting a record into the new table.

Now is just to always work with the new table with PK ... the only workaround I could think ...
hitchhiker
Location: poa

Poston Mon, 25 Sep 2006 9:36 am

Any record in the Oracle has a ROWID (pseudo column), which is a number in HEX format and unique for each record in the data of the Bank, this record will only be changed if the record is deleted and included again. UPDATE the procedures in its content does not change. DELETE procedures he disappears. In INSERT procedures it is created.
Note: this ID is generated by the Bank, you do not have control over the same.

I hope I helped.
NiNo
Location: Sao Paulo

NiNo
Developer

Poston Mon, 25 Sep 2006 9:44 am

As far as I know, is not reliable you reference by ROWID.
Some DBA could explain if my statement is true or false? :?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 14 Mar 2007 2:54 pm

Dr. Gori, I guess that's what you were looking for: Listing 2-query using the ROWID this search only happens if any of the columns accessed is not present in the index. If all columns accessed are present in the index, so access by ROWID does not become necessary.
Despite being the fastest way to access data, it is not recommended that developers use this form of access directly. The ROWID is an internal Oracle information can be modified through some operations of database administration, such as importing and exporting of tables.


Source: http://www.linhadecodigo.com.br/artigos.asp?id_ac=768
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Wed, 14 Mar 2007 3:00 pm

Way to go!
The ROWID can be used to reference a line when you use the information \"speedy\" (after fetch the rowid).

That's because in the meantime we know that there will be no change of basis, tablespace, etc. .. IE will not affect the rowid of the row. If you store a ROWID for future reference, maybe something changed on the base and you will be accessing another line.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 14 Mar 2007 3:28 pm

Thanks Dr Gori.

Due to this, left here an opinion for others here também opine: 1) Create the new table, with a column that will serve as a PK;
2) export the data for the new table;
3) Define the PK;
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Fri, 29 Jun 2007 10:11 am

Good people returning to the subject of ROWID.

I have a table with pk of 5 fields.
I need a trigger to fire when an insert in this table, give this trigger inserts in a Type a package temporarily the ROWID. and then (after segment) I work with this ROWID.

Well what I'm afraid is that at the time of pesquizer by this ROWID the hex changed, will have a chance to happen.

I will give a summary of codes
Code: Select all
CREATE OR REPLACE TRIGGER TG_AFTER_I_GISS_BOLETOS
  AFTER INSERT
  ON GISSONLINE.TB_INTER_BOLETOS_GISS 
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW 
BEGIN
  PK_GISSONLINE.adicionaRegistro(:NEW.ROWID,
                                 'TB_INTER_BOLETOS_GISS',
                                 'INSERT'); 
END TG_AFTER_I_GISS_BOLETOS;
Code: Select all
CREATE OR REPLACE TRIGGER TG_AFTER_I_GISS_BOLETOS
  AFTER INSERT
  ON GISSONLINE.TB_INTER_BOLETOS_GISS 
BEGIN
  PK_GISSONLINE.processaRegistros(); 
END TG_AFTER_I_GISS_BOLETOS;
and PK_GISSONLINE (a package) [...] means that there is more code, more important to the question concerned.
Code: Select all
CREATE OR REPLACE PACKAGE BODY PK_GISSONLINE IS
[...]
  TYPE tp_change_rec IS RECORD(
     ChaveROWID     ROWID
    ,TableName VARCHAR2(30)
    ,Acao      VARCHAR2(30));

  TYPE tp_change_tab IS TABLE OF tp_change_rec;
  gChangeTab tp_change_tab := tp_change_tab();
  PROCEDURE adicionaRegistro(PChave NUMBER
                            ,PTable VARCHAR2
                            ,PAcao  VARCHAR2) IS
  BEGIN
    IF USER = 'SIATUBLU' THEN
      gChangeTab.EXTEND();
      gChangeTab(gChangeTab.LAST).Chave := PChave;
      gChangeTab(gChangeTab.LAST).TableName := PTable;
      gChangeTab(gChangeTab.LAST).Acao := PAcao;
    END IF;
  END adicionaRegistro;

  PROCEDURE processaRegistros IS
    i NUMBER;
  BEGIN
    IF USER = 'SIATUBLU' THEN
    -- procedure responsável apenas para chamada de outras procedures, conforme situação encrontada
    IF gChangeTab.COUNT > 0 THEN
      FOR i IN gChangeTab.FIRST .. gChangeTab.LAST LOOP
        IF gChangeTab(i).Acao = 'INSERT' THEN
       
          IF gChangeTab(i).TableName = 'TB_INTER_BOLETOS_GISS' THEN
            for rg in (select * from TB_INTER_BOLETOS_GISS' where rowid = gChangeTab(i).ChaveRowID) loop
              -- processamento das informações[...]
            end loop;
          END IF;
        END IF;
     END LOOP;
   END IF;
END processaRegistros ;
[...]
HenriqueMachado
Location: Blumenau - SC

Abraços,
Henrique Machado Muller

Poston Fri, 29 Jun 2007 10:34 am

Another personal thing.
How do I keep the ROWID in a variable that the code above didn't work.
HenriqueMachado
Location: Blumenau - SC

Abraços,
Henrique Machado Muller

Poston Fri, 29 Jun 2007 11:25 am

I'vê seen around the TO_ROWID , which let to a variable of type rowid type character.
Sometimes it takes a few mismatches. Attempts to use the TO_ROWID. And if it doesn't work, show us what is giving error.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 29 Jun 2007 11:40 am

Thanks dr_gori.

More just descrobindo what was the error in the package.
The name ROWID could not be used for a variable was just put ChaveROWID ROWID;

as in the example below
Code: Select all
declare
  TYPE tprec IS RECORD(
     AROWID rowid
    ,TableName VARCHAR2(30));

  TYPE tp_change_tab IS TABLE OF tprec INDEX BY BINARY_INTEGER;
  gChangeTab tp_change_tab;
      i integer;
begin
  i := 1;
for rg in (select rowid , descricao from logradouro where rownum <= 10 ) loop
     gChangeTab(i).AROWID := rg.rowid;
         dbms_output.put_line(i || ':' || rg.rowid ||':' || rg.descricao);
         i := 1 + i;
   end loop;
   dbms_output.put_line(LPAD('-',50,'-'));
   for x in gChangeTab.first .. gChangeTab.last loop
         for rg in (select * from logradouro where rowid = gChangeTab(x).AROWID) loop
           dbms_output.put_line(x || ':' || gChangeTab(x).AROWID ||':' || rg.descricao);
         end loop;
   end loop;
end;
HenriqueMachado
Location: Blumenau - SC

Abraços,
Henrique Machado Muller

Poston Thu, 20 Nov 2008 8:27 am

Or do it like this:
Code: Select all
DECLARE
   L_rowid rowid;
BEGIN
   INSERT INTO tabela(<colunas>)
   VALUES (<valores>) RETURN ROWID INTO L_rowid;
   
   ...
EXCEPTION
   ...
END;
sandkiller
Location: Poços de Caldas

Poston Mon, 29 Dec 2008 3:33 pm

Yes, I would create the new table with their own PK, but care must be taken, because there may be inconsistency: Situation 1: data from the mother:
Code: Select all
nome       idade    telefone
Alfredo       35    1234-5678
Ricardo       35    1234-5678
the new Data table:
Code: Select all
cod   nome      idade   telefone
1   Alfredo      35      1234-5678
2   Ricardo      35      1234-5678
now Imagine you, in the table, change the name of Ricardo, to Alfredo ... how to change the registry on the child table? You would have to do a scan before the change, which is a record in the child, make the change and then change the record in the table.

We can do it, but it's a lot of work.
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 2 guests