create a function compare

[victorlopes]

tanks for the help! I need to create a function that makes the following.
It compares 2 equal tables in different database. (the database has equal structures but different register.) I need to run the function and it rewrites the table “estados” and copies everything from “estados” do table “LOG”.
SQL> desc estado;
Nome Nulo? Tipo
----------------------------------------- -------- ------------
id_erro NOT NULL NUMBER(9)
componente NOT NULL VARCHAR2(255
tipo_erro NOT NULL VARCHAR2(255
data NOT NULL NUMBER(38)
estado NOT NULL NUMBER(1)

SQL> desc log;
Nome Nulo? Tipo
----------------------------------------- -------- ------------
id_erro NOT NULL NUMBER(9)
id_log NOT NULL NUMBER(9)
dataInicio NOT NULL NUMBER(38)
dataFim NOT NULL NUMBER(38)
componente NOT NULL VARCHAR2(255
eventoID NOT NULL NUMBER(38)
estado NOT NULL VARCHAR2(255
texto1 VARCHAR2(255
texto2 VARCHAR2(255
valor1 NOT NULL NUMBER(38)

Tanks and regards
Victor Lopes

 

[SantaMufasa]

Victor,

Before posting suggestions to help you with your need, I have a few questions:

I need to create a function that makes the following.

Do you really mean "function"? In a classical function, it returns a single value as a result of passing in zero, one, or many arguments. That doesn't sound like what you want from your expanded description.

It compares 2 equal tables in different database. (the database has equal structures but different register.)

1) What are you comparing between the 2 equal tables.
2) By "database", do you mean different Oracle database instances, or simply the tables reside in different Oracle user/schemas on the same Oracle database instance?
3) What do you mean by "register"?

I need to run the function and it rewrites the table “estados” and copies everythingfrom “estados” do table “LOG”.

If you need to copy everything from “estados” do table “LOG”, then why not simply either:

create table log as select * from <owner>.estados;

or

insert into log select * from <owner>.estados;

Please let us know your responses to these questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]