Oracle Database Discussions

I have two databases (9.2.0.4.0) and made a database link to update tables from one to another. If I execute in SQL Plus a command, it runs fine and get the work done. But that same script, running in a Stored procedure, gets me that ORA-0285.....

I've go this with Alter session set global_names = true in the procedure

If I make it with Alter session set global_names = false then I get ORA-02069: global_names parameter must be set to TRUE for this operation

You can see this script, every single sentence can be executed in SQL Plus with no problem, but with the stored procedure I've got problem:

CREATE OR REPLACE PROCEDURE Actualiza_Arimen_WEB IS
--
vtstamp DATE;
vnewtstamp DATE;
--
CURSOR C_Fecha_Tabla IS
SELECT tstamp
FROM rep_web
WHERE UPPER(tabla) = 'ARIMEN';
--
CURSOR C_Arimen IS
SELECT a.NO_CIA,
a.TIPO_ENCUADERNACION,
a.DESC_ENCUADERNACION,
a.TSTAMP
FROM arimen a
WHERE a.no_cia = '01'
AND NVL(a.tstamp, SYSDATE) > vtstamp;
--
BEGIN
-- EXECUTE IMMEDIATE 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
OPEN C_Fecha_Tabla;
FETCH C_Fecha_Tabla INTO vtstamp;
CLOSE C_Fecha_Tabla;
vnewtstamp := NVL(vtstamp, SYSDATE);

IF vtstamp IS NOT NULL THEN
FOR i IN C_Arimen LOOP
BEGIN
IF NVL(i.tstamp, vnewtstamp) > vnewtstamp THEN
vnewtstamp := vtstamp;
END IF;
BEGIN
UPDATE arimen@adn
SET desc_encuadernacion = i.desc_encuadernacion,
tstamp = i.tstamp
WHERE no_cia = i.no_cia
AND tipo_encuadernacion = i.tipo_encuadernacion;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
INSERT INTO arimen@adn (
no_cia,
tipo_encuadernacion,
desc_encuadernacion,
tstamp)
VALUES (
i.no_cia,
i.tipo_encuadernacion,
i.desc_encuadernacion,
i.tstamp);
EXCEPTION
WHEN OTHERS THEN
BEGIN
Registra_Errores('1W', 'Procedure: Actualiza_Arimen_WEB - INSERT', SQLCODE, SQLERRM);
RETURN;
END;
END;
WHEN OTHERS THEN
BEGIN
Registra_Errores('1W', 'Procedure: Actualiza_Arimen_WEB - UPDATE', SQLCODE, SQLERRM);
RETURN;
END;
END;
COMMIT;
END;
END LOOP;
IF vnewtstamp IS NOT NULL THEN
UPDATE REP_WEB
SET fecha_rep = SYSDATE,
tstamp = vnewtstamp
WHERE UPPER(tabla) = 'ARIMEN';
COMMIT;
END IF;
ELSE
Registra_Errores('1W', 'NO SE ENCONTRÓ LA FECHA DE REPLICAR ARIMEN EN REP_WEB', NULL, NULL);
RETURN;
END IF;
COMMIT;
END;
/

Edited by: user10872107 on 29-ene-2009 11:15