You should take a look at the example at the bottom of this page: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
I think you are making it more difficult than it needs to be. After you've looked it over, your question might change. Regards, Mike On Wed, Jun 6, 2012 at 12:03 PM, charly <[email protected]> wrote: > > > Hello friends, I am currently starting in the area of pl / sql. I wonder > if anyone could give me a hand with a situation. I' am creating a procedure > that updates TARGET TABLE. In case it finds new records in the BASE TABLE. > If there is something new in the BASE TABLE, i have to insert the new > records in the TARGET TABLE. > > To acomplish this I have done the following: > > > > PROCEDURE automatizacion_celdas > ( > ) > > > IS > > -----VARIABLES DEL PROCEDIMIENTO----- > > > > v_id it_bi.cnf_cellsite_optima.id%**type; > v_nombre it_bi.cnf_cellsite_optima.**nombre%type; > v_latitude it_bi.cnf_cellsite_optima.**latitude%type; > v_longitud it_bi.cnf_cellsite_optima.**longitude%type; > v_opid it_bi.cnf_cellsite_optima.**opid%type; > v_opnombre it_bi.cnf_cellsite_optima.**opnombre%type; > v_tecnologia it_bi.cnf_tr_celdas_optima.**tecnologia%type; > v_provincia it_bi.cnf_tr_celdas_optima.**cod_provincia%type; > v_ciudad it_bi.cnf_tr_celdas_optima.**cod_ciudad%type; > v_descripcion it_bi.cnf_tr_celdas_optima.** > cell_description%type; > v_count number; > v_etapa it_brain.flw_proceso_log.**etapa%TYPE; > v_cod_etapa it_brain.flw_proceso_log.cod_**etapa%TYPE; > > > > BEGIN > > > > > po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS: Error > al actualizar datos > > UPDATE celdas c > Set > nombre = (SELECT nombre From > Tabla_base ct where ct.id = c.id AND ct.nombre <> c.nombre ), > latitude = (SELECT latitude From > Tabla_base ct where ct.id = c.id AND ct.latitude <> c.latitude ), > longitude = (SELECT longitude From > Tabla_base ct where ct.id = c.id AND ct.nombre <> c.nombre ), > opid = (SELECT opid From Tabla_base > ct where ct.id = c.id AND ct.nombre <> c.nombre ), > opnombre = (SELECT opnombre From > Tabla_base ct where ct.id = c.id AND ct.opid <> c.opid ), > tecnologia = (SELECT tecnologia From > Tabla_base ct where ct.id = c.id AND ct.tecnologia <> c.tecnologia ), > cod_provincia = (SELECT cod_provincia > From Tabla_base ct where ct.id = c.id AND ct.cod_provincia <> > c.cod_provincia ), > cod_ciudad = (SELECT cod_ciudad From > Tabla_base ct where ct.id = c.id AND ct.cod_ciudad <> c.cod_ciudad ), > cell_description = (SELECT cell_description > From Tabla_base ct where ct.id = c.id AND ct.cell_description <> > c.cell_description ); > > > po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS: > Error al iniciar iteración > > FOR x in (select id from it_bi.cnf_tr_celdas_optima) > LOOP > > > ---------- Inicializacion de Variables------------ > > v_id := 0; > v_nombre := 0; > v_latitude := 0; > v_longitud := 0; > v_opid := 0; > v_opnombre := 0; > v_tecnologia := 0; > v_provincia := 0; > v_ciudad := 0; > v_descripcion := 0; > v_count := 0; > > Begin > > -----------Busco nuevos registro en la tabla----------------- > > select id, nombre, latitude, longitude, opid, > opnombre,'Cell '|| opid ||' - '||substr(nombre, 1,11)||' -'||' GSM ' > into v_id, > v_nombre,v_latitude,v_**longitud,v_opid,v_opnombre, v_descripcion > from Tabla_base o > where not EXISTS (select 1 from celdas t where t.id = > x.id); > > > > po_cod_err := 'DWH-00000'; --.AUTOMATIZACION_CELDAS: > Error al insertar datos > > -----------inserto registros en caso de haber > nuevos----------------- > > v_count := sql%rowcount; > > IF v_count >= 1 then > insert into celdas > (id, > nombre, > latitude, > longitude, > opid, > opnombre, > tecnologia, > cod_provincia, > cod_ciudad, > cell_description, > fch_inicio, > fch_fin, > estado_actual > ) > VALUES (v_id, > v_nombre, > v_latitude, > v_longitud, > v_opid, > v_opnombre, > 'GSM', > null, > null, > v_descripcion, > null, > null, > null > ); > > commit; > > END IF; > > end; > > end loop; > > > > > > END; > > > > > > What I want is to find some function to tell me if the select bring > records (if there are records), and if it is >= 1 then to insert the > records in the TARGET TABLE. I'm using the sql%rowcount and does not do > what I seek . I will appreciate if someone can give me a guide or suggest > me something. > > Thank you very much! I'll wait for your answers. > > Greetings!! > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
