Aprenda PL/SQL

Duplication of current record

Qualquer pergunta sobre o Forum e seu funcionamento. (Perguntas sobre Oracle devem ser colocadas no respectivo assunto)
  

Poston Wed, 15 Mar 2017 2:44 pm

Good afternoon guys.. I'vê been through this, request help from friends.
Need to duplicate the records of a table through a Procedure and have had difficulties.
This insertion must be done on a group of people identified by their license plates and grouped through a group.
That is I read this group as in the following example:
Code: Select all
SELECT E.NUMFUNC, E.NUMVINC
  FROM ELEITOS_EXT E, EVENTO_FUNC EV
   WHERE E.GRUPO = 13886 -->>dentro deste grupo tenho 25 pessoas
   AND E.EMP_CODIGO = 1--WEMP
   AND E.NUMFUNC = EV.NUMFUNC
   AND E.NUMVINC = EV.NUMVINC
   AND E.EMP_CODIGO = EV.EMP_CODIGO
   AND EV.TIPOEVENTO = 'DESIGNACAO';
and following must insert the parameters passed by the user in the call of the Procedure into the table I need duplicate records. Remembering that this duplication may or may not be the same information in which I have on the table. Procedure call example:
Code: Select all
CREATE OR REPLACE PROCEDURE DUPLICA_EVENTO_TESTE ( PGRUPO      IN NUMBER,                                         
                                                   PDTINI      IN DATE,
                                                   PDTFIM      IN DATE,
                                                   PTIPOEVENTO IN VARCHAR,
                                                   PCARGO      IN NUMBER,
                                                   PFORMAPROV  IN VARCHAR,
                                                   PSETOR      IN VARCHAR,
                                                   PREF        IN VARCHAR,
                                                   PJOR        IN VARCHAR,
                                                   PSFIXO      IN VARCHAR,
                                                   PESPEC      IN VARCHAR,
                                                   P_IDEXEC    NUMBER DEFAULT 0) IS
these values of the parameters may be the same as already exist in that record or table can use other values for these same parameters, but q at the end will be inserted into the table.
I did like that the insertion, however nothing happens:
Code: Select all
INSERT INTO EVENTO_FUNC(NUMEV,NUMFUNC, NUMVINC, TIPOEVENTO, FORMAPROV, DTINI, DTFIM, DTINIREM, DTFIMREM, CARGO, SETOR, REFERENCIA, JORNADA, OBS, EMP_CODIGO, FLEX_CAMPO_01, FLEX_CAMPO_02, FLEX_CAMPO_03,ID_REG)    

VALUES (WEVEMAX,WNUMFUNC, WNUMVINC, WTIPOEVENTO_OLD, WFORMAPROV_OLD, PDTINI, PDTFIM-1, PDTINI, PDTFIM, WCARGO_OLD, WSETOREV, WREFEREV, WJORNAEV, WOBS,WEMP, WFLEX_CAMPO_01, WFLEX_CAMPO_02, WFLEX_CAMPO_03,WID_REGMAX);
Compiles without errors but nothing happens.
If anyone can give me a light in that sense I appreciate. Strong hug to all.
Tony Joni Duran

Poston Thu, 16 Mar 2017 1:34 pm

Tony, you put the COMMIT statement in your procedure?
Would publish the procedure complete the forum to evaluate?
I understand (I imagine) that is an anonymous block that catches the SELECT mentioned as a CURSOR and calls the procedure, right?
You can also publish here?

At Sergio
stcoutinho
Location: Sao Paulo - SP

Poston Fri, 17 Mar 2017 10:50 am

Good morning.. look it aí:
Code: Select all
CREATE OR REPLACE PROCEDURE DUPLICA_EVENTO_TESTE ( PGRUPO      IN NUMBER,                                         
                                                   PDTINI      IN DATE,
                                                   PDTFIM      IN DATE,
                                                   PTIPOEVENTO IN VARCHAR,
                                                   PCARGO      IN NUMBER,
                                                   PFORMAPROV  IN VARCHAR,
                                                   PSETOR      IN VARCHAR,
                                                   PREF        IN VARCHAR,
                                                   PJOR        IN VARCHAR,
                                                   PSFIXO      IN VARCHAR,
                                                   PESPEC      IN VARCHAR,
                                                   P_IDEXEC    NUMBER DEFAULT 0) IS
ERRO       UTL_FILE.FILE_TYPE;
CONV       UTL_FILE.FILE_TYPE;
--

WLIDOS     NUMBER       := 0;
WOK        VARCHAR2(2) := NULL;
WOK1       NUMBER      := 0;
WGRUPO     VARCHAR2(5) := PGRUPO;
WPRIVEZ    NUMBER      := 0;
V_IDEXEC   NUMBER;
--
WEMP        NUMBER;


-- Eventos
--
WEVEMAX    NUMBER := 0;
WID_REGMAX NUMBER:=0;
WNUMFUNC    NUMBER;
WNUMVINC NUMBER;
WTIPOEVENTO_OLD VARCHAR2(20):=PTIPOEVENTO;
WFORMAPROV_OLD  VARCHAR2(20):=PFORMAPROV;
WDTINI_EV_OLD   DATE := PDTINI;
WDTFIM_EV_OLD   DATE := PDTFIM;
WCARGO_OLD      NUMBER:= PCARGO;
WSETOREV   VARCHAR2(15):=PSETOR ;
WREFEREV   VARCHAR2(10):=PREF;
WJORNAEV   VARCHAR2(20):=PJOR;
WOBS  VARCHAR2(2000):='DUPLICAÇÃO AUTOMÁTICA DICAD/SEGES - ('||SYSDATE||')';
WFLEX_CAMPO_01 VARCHAR2(999);
WFLEX_CAMPO_02 VARCHAR2(999):=PSFIXO;
WFLEX_CAMPO_03 VARCHAR2(999):=PESPEC;
WHORASSEM  NUMBER(4,2);
WHORASMEN  NUMBER(5,2);
--

WTIPOEVENTO_PAR VARCHAR2(20);
WSETOR_OLD      VARCHAR2(15);
WREFERENCIA_OLD VARCHAR2(10);
WJORNADA_OLD    VARCHAR2(15);
--

WDTINI_EX_OLD DATE;
WDTFIM_EX_OLD DATE;
--

F1DIR      VARCHAR2(99);
--
-- Cursor de Grupo
--
CURSOR CUR_ELEITOS IS
SELECT E.NUMFUNC, E.NUMVINC
  FROM ELEITOS_EXT E, EVENTO_FUNC EV
   WHERE E.GRUPO = PGRUPO
   AND E.EMP_CODIGO = WEMP
   AND E.NUMFUNC = EV.NUMFUNC
   AND E.NUMVINC = EV.NUMVINC
   AND E.EMP_CODIGO = EV.EMP_CODIGO
   AND EV.TIPOEVENTO = 'DESIGNACAO';
--

-- Cursor de Ultimo Evento
--
CURSOR CUR_EVEMAX (PNUMFUNC NUMBER, PNUMVINC NUMBER) IS
   SELECT MAX(EV.NUMEV + 1) WEVEMAX --NUMEV
     FROM EVENTO_FUNC EV
     WHERE EV.NUMFUNC =PNUMFUNC
     AND EV.NUMVINC = PNUMVINC;
--

-- Cursor ID de Ultimo Evento
--
CURSOR CUR_ID_EVENTOMAX (PNUMFUNC NUMBER, PNUMVINC NUMBER) IS
   SELECT MAX(EV.ID_REG + 1) WID_REGMAX
     FROM EVENTO_FUNC EV
     WHERE EV.NUMFUNC =PNUMFUNC
     AND EV.NUMVINC = PNUMVINC;
--
-- Cursor de Eventos
--
CURSOR CUR_EVENTO (PNUMFUNC NUMBER, PNUMVINC NUMBER) IS
   SELECT EV.NUMFUNC, EV.NUMVINC, EV.SETOR,  EV.REFERENCIA, EV.JORNADA, J.HORASSEM,
          J.HORASMEN, EV.FORMAPROV, EV.TIPOEVENTO, EV.CARGO, EV.DTINI, EV.DTFIM,
          EV.FLEX_CAMPO_01, EV.FLEX_CAMPO_02, EV.FLEX_CAMPO_03, EV.ID_REG
     FROM EVENTO_FUNC EV, JORNADAS_ J
    WHERE EV.NUMFUNC    = PNUMFUNC
      AND EV.NUMVINC    = PNUMVINC
      AND EV.EMP_CODIGO = WEMP
      AND EV.DTINI = (SELECT MAX(EV1.DTINI)
                        FROM EVENTO_FUNC EV1
                       WHERE EV1.NUMFUNC = EV.NUMFUNC
                         AND EV1.NUMVINC = EV.NUMVINC
                         AND EV1.TIPOEVENTO = EV.TIPOEVENTO)
      AND J.SIGLA =  EV.JORNADA;
--

BEGIN
   --
    IF P_IDEXEC = 0 THEN
    -- Abre uma transação auditada pelo Ergon
       V_IDEXEC := LOG_PACK.INSERE_LOG_HEADER('DUPLICA_EVENTO_TESTE','Data_referencia = '||TO_CHAR(SYSDATE,'DD/MM/YYYY'));
    ELSE
    -- Utiliza a transação corrente
       V_IDEXEC :=P_IDEXEC;
    END IF;
--
  Log_pack.insere_log_detail(V_IDEXEC,'Inicio do procedimento.                         Data/Hora=> '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS')||'        Usuario=> '||FLAG_PACK.GET_USUARIO());
  Log_pack.insere_log_detail(V_IDEXEC,'GRUPO DE ELEITOS => '||PGRUPO||'                DT EXERC => '||TO_CHAR(PDTINI,'DD/MM/YYYY')||'                    DT VAC => '||TO_CHAR(PDTFIM,'DD/MM/YYYY'));
--
   SELECT DECODE(OP.VALOR, 'WINDOWS', ITEM1, 'UNIX', ITEM2, 'SEM_TIPO_OS') || FLAG_PACK.GET_USUARIO() DIR INTO F1DIR FROM ITEMTABELA,
   (SELECT VALOR FROM HAD_OPCOES_ITENS WHERE SIS='C_Hades' AND GRUPO='SISTEMA OP' AND OPCAO='TIPO SISTEMA OP') OP
   WHERE TAB='DIRETORIOS_UTL_FILE' AND ITEM='Dados Funcionais';
   --
   WEMP    := FLAG_PACK.GET_EMPRESA;
   --
   ERRO    := UTL_FILE.FOPEN(F1DIR,'ERRO'||'_GRUPO_'||WGRUPO||'.TXT','W');
   CONV    := UTL_FILE.FOPEN(F1DIR,'CONV'||'_GRUPO_'||WGRUPO||'.TXT','W');
   --
   
         FOR V_CURELEITOS IN CUR_ELEITOS LOOP
         --
              SELECT EVENTO_FUNC_NUMEV_SEQ.NEXTVAL INTO WEVEMAX FROM DUAL;
              WNUMFUNC     := V_CURELEITOS.NUMFUNC;
              WNUMVINC     := V_CURELEITOS.NUMVINC;
              WLIDOS       := WLIDOS + 1;
         
     --        BEGIN
             
               INSERT INTO EVENTO_FUNC(NUMEV,NUMFUNC, NUMVINC, TIPOEVENTO, FORMAPROV, DTINI, DTFIM, DTINIREM, DTFIMREM,
                                       CARGO, SETOR, REFERENCIA,
                                       JORNADA, OBS, EMP_CODIGO, FLEX_CAMPO_01, FLEX_CAMPO_02, FLEX_CAMPO_03,ID_REG)    
             
                               
                               VALUES (WEVEMAX,WNUMFUNC, WNUMVINC, WTIPOEVENTO_OLD, WFORMAPROV_OLD, WDTINI_EV_OLD, WDTFIM_EV_OLD-1, WDTINI_EV_OLD, WDTFIM_EV_OLD,
                                       WCARGO_OLD, WSETOREV, WREFEREV,
                                      WJORNAEV, WOBS,WEMP, WFLEX_CAMPO_01, WFLEX_CAMPO_02, WFLEX_CAMPO_03,WID_REGMAX);

                    COMMIT;
         
         END LOOP;   
    --
   Log_pack.insere_log_detail(V_IDEXEC,'Fim do procedimento com exito.           Data/Hora=> '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS'));
      --
   UTL_FILE.FFLUSH(ERRO);
   UTL_FILE.FCLOSE(ERRO);
   --
   UTL_FILE.FFLUSH(CONV);
   UTL_FILE.FCLOSE(CONV);
   --

EXCEPTION
   --
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      DBMS_OUTPUT.PUT_LINE('Arquivo não identificado');
   WHEN UTL_FILE.INVALID_OPERATION THEN
      DBMS_OUTPUT.PUT_LINE('Operação Incompatível');
   WHEN UTL_FILE.READ_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Erro de Leitura');
   WHEN NO_DATA_FOUND THEN
   --   Log_pack.insere_log_detail(V_IDEXEC,'ERRO GRUPO DE ELEITOS VAZIO=> '||PGRUPO);
      UTL_FILE.FCLOSE_ALL;
   WHEN VALUE_ERROR THEN
   DBMS_OUTPUT.PUT_LINE('Verifique o tamanho da linha ');
   WHEN UTL_FILE.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Path ');
   WHEN UTL_FILE.INVALID_MODE THEN
        DBMS_OUTPUT.PUT_LINE('Invalid Mode');
   WHEN UTL_FILE.write_error THEN
        DBMS_OUTPUT.PUT_LINE('Write Error ');
   WHEN UTL_FILE.internal_error THEN
        DBMS_OUTPUT.PUT_LINE('Internal Error ');
   --

END;
--EXEC DUPLICA_EVENTO_TESTE(13886,'02/01/2017','31/12/2017','DESIGNACAO',31726,'DESIGNACAO','0260100000','DCA-5',240,'15','01');
Tony Joni Duran

Poston Mon, 20 Mar 2017 9:01 am

Good morning, Tony, okay?

Still in the line of research Sergio you directed, has two other points which could act to check why not insert records: 1) the main cursor LOOP (CUR_ELEITOS), is returning records?
2) is giving an error? From what I have studied, the only error handling for moving files.

Hug, cheezburger network
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Mon, 20 Mar 2017 2:12 pm

Mr cheezburger network, checked the Cursor, and the same was returning duplicate record and why not insert.
Did the proper treatment for the same and it worked.
I appreciate the noble colleagues.
Strong hug.
Tony Joni Duran



Return to Rules / Announcements / Help on Forum

Who is online

Users browsing this forum: No registered users and 1 guest