without PK Table. How to identify the lines?

[rnovais]

Next ... I have some tables where there is no column that identifies the records. The problem is that I can not change the table to ascire a column, and I intend to pick up some records every 10 minutes
to play on another table, as I have no column that Identicate the record Already selected before I would like
of knowing if anyone has some trick that can help me.

ATT,

Ricardo

[hitchhiker]

Create a new table with the same columns as the original + an identifier column.
Create a Trigger After Insert on the original table, inserting a record to the new table.

Now just work always with the new table, which has PK ... was the only gambiarra in which I thought ...

[NiNo]

Any registration in Oracle has a rowid (pseudo column), which is a hexa and single number for each bank data record, this record will only change if the record is deleted and included again. In update procedures your content is not changed. In delete procedures it disappears. In insert procedures it is created.
Note: This identification is generated by the bank, you have no control over it.

I hope I have helped.

[dr_gori]

From what I know, you are not reliable for you to refer by Rowid.
Any DBA could explain if my statement is true or false?

[Trevisolli]

Dr. Gori,

I think this is what he was looking for:


Listing 2 - Consultation Using Rowid
] [/b]

This search only happens if any of the columns accessed is not present in the index. If all the columns accessed are present in the index, then access by Rowid does not become necessary.
Despite being the fastest access to data, it is not recommended that developers use this form of access directly. ROWID is an internal Oracle information and can be modified through some database administration operations such as import and export from tables.


Source: http://www.linhadecodigo.com.br/artigos.asp?id_ac=768

[dr_gori]

Nice job!
Rowid can be used to refer to a line when it will use "quickly" information (shortly after fetching the rowid).

This is because in the meantime we know that there will be no change of base, tablespace, etc ... that is, it will not affect the rowid of the line. If you store a Rowid for future queries, maybe something has changed at the base and you will be accessing another line.

[Trevisolli]

Thanks Dr Gori.

Because of this, he left an opinion here for the others here Tb Opinem:

1) Create the new table, with a column that will serve as a Pk;
2) Export data to new table;
3) Define the PK;

[HenriqueMachado]

Good people returning to Rowid's assundo.

I have a table with PK of 5 fields.
I need to make a trigger to shoot when one insert on this table, then this trigger inserts a Type from a package temporarily the rowid. And then (after sarely) I work with this rowid.

Well what I'm afraid is that at the time of researching by this rowid the hexa has changed, is it a chance to happen.

I will summarize the codes

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; 

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 not important to the question in question. [[two]]

[HenriqueMachado]

Another personal thing.
As I keep the rowid in a variable that the above code did not work.

[dr_gori]

I have seen around the to_rowid , which leaves a character variable for the ROWID type.
Sometimes it gives some kind of kinds. Try to use to_rowid. And if it does not work, show us what error is giving.

[HenriqueMachado]

Thank you Dr_Gori.

MORE I ended up displacing which was the error in the package.
The rowid name could not be used for a variable
was only

KeyRowid Rowid;

as in the example below

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; 

[sandkiller]

Or do this:

Select all

 
DECLARE 
   L_rowid rowid; 
BEGIN 
   INSERT INTO tabela(<colunas>) 
   VALUES (<valores>) RETURN ROWID INTO L_rowid; 
    
   ... 
EXCEPTION 
   ... 
END; 

[Toad]

Yeah, I would create the new table with your own PK, but care should be taken as inconsistency may occur:

Situation 1:

Table data Mother:

Select all

 
nome       idade    telefone 
Alfredo       35    1234-5678 
Ricardo       35    1234-5678 

Data from the new table:

Select all

 
cod	nome		idade	telefone 
1	Alfredo		35   	1234-5678 
2	Ricardo		35   	1234-5678 

Imagine now that You, on the motherboard, change Ricardo's name, to Alfredo ... how to change the record on the daughter table? You would have to do a check before the change, what is the record in the daughter, make the change and only then change the record in the mother table ...

can do, but it will work .