Pular para o conteúdo
  • Este tópico contém 6 respostas, 2 vozes e foi atualizado pela última vez 9 anos, 3 meses atrás por Avatar de Joel SchecheleskiJoel Schecheleski.
Visualizando 7 posts - 1 até 7 (de 7 do total)
  • Autor
    Posts
  • #107093
    Avatar de Joel SchecheleskiJoel Schecheleski
    Participante

      Bom dia a todos
      Estou com um puto problema, na verdade essa função abaixo é chamado por uma procedure responsável por ler todas as tabelas de importação em ordem de dados, então cada vez que a procedure anterior entra no loop essa função abaixo é disparada para importar todos os dados de um banco remotamente criado. Ou seja, digamos que o loop esteja na tabela cor, eu gostaria de fazer um rowtype do NOME da tabela passado por parâmetro. Abaixo segue um exemplo da estrutura que estou usando para importar os dados.


      FUNCTION BULK_COLLECT_SB(P_NOMTABELA IN nVARCHAR2) RETURN BOOLEAN AS

      TYPE C_TABELA IS REF CURSOR;
      L_TABELA C_TABELA;

      TYPE FETCH_ARRAY IS TABLE OF COR%ROWTYPE; -- COR DEVER SER SUBSTITUIDO POR P_NOMTABELA
      --ou
      S_ARRAY COR%ROWTYPE; -- COR DEVER SER SUBSTITUIDO POR P_NOMTABELA

      P_STRING VARCHAR2(4000) DEFAULT 'SELECT * FROM '||P_NOMTABELA||'@DBL_LINK';

      BEGIN
      OPEN L_TABELA FOR P_STRING;
      LOOP
      FETCH L_TABELA BULK COLLECT INTO S_ARRAY LIMIT 1000;
      FORALL I IN 1..S_ARRAY.COUNT
      INSERT INTO COR VALUES S_ARRAY(I); -- COR DEVE SER SUBSTITUIDO POR P_NOMTABELA (parâmetro)
      EXIT WHEN L_TABELA%NOTFOUND;
      END LOOP;
      CLOSE L_TABELA;
      COMMIT;
      RETURN TRUE;

      EXCEPTION WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000,'Não foi possível importar dados dessa tabela >> '||P_NOMTABELA||' <<, erro: '||chr(10)|| 'Código....: '||SQLCODE||chr(10)|| 'Descrição.: '||SQLERRM); END;

      Senhoras e senhores, quem conseguir dar uma dica, para sei lá fazer uma conversão de tipos ou coisa do gênero, ficarei muito agradecido B) .

      #107094
      Avatar de rmanrman
      Participante

        @Joel Schecheleski

        Pesquise sobre o pacote DBMS_SQL. É possível através deste pacote você passar o nome da tabela e retornar as colunas. Depois é só montar o INSERT e executar através do EXECUTE IMMEDIATE.

        #107095
        Avatar de Joel SchecheleskiJoel Schecheleski
        Participante

          Interessante sua ideia, porém como fazer isso? você pode passar um exemplo de uso do pacote DBMS_SQL + o BULK COLLECT para aplicar nesse caso? Lembrando que devo substituir o noma da tabela pra que seja sempre dinâmico, exemplo:

          DECLARE
          TYPE RECSTARTYP IS TABLE OF cor%ROWTYPE INDEX BY BINARY_INTEGER;
          REC_TAB RECSTARTYP;
          CURSOR TEMP IS SELECT * FROM cor@dbl_bancoremoto; -- AQUI O NOME DA TABELA COR DEVE SER RECONHECIDO PELO PASSADO POR PARÂMETRO. FICANDO ASSIM: p_cor
          BEGIN
          OPEN TEMP;
          FETCH TEMP BULK COLLECT INTO REC_TAB LIMIT 1000;
          FORALL I IN REC_TAB.FIRST..REC_TAB.LAST
          INSERT /*+ append */ INTO cor VALUES REC_TAB(I);
          COMMIT;
          CLOSE TEMP;
          END;

          Valeu mesmo assim pela dica camarada @rman

          #107097
          Avatar de rmanrman
          Participante

            @Joel Schecheleski

            Já estava na minha lista de TODO algo parecido, então aproveitei para implementar.


            CREATE OR REPLACE PACKAGE PKG_SQL IS

            PROCEDURE COPY_TABLE_ROWS(PSOURCE_TABLE IN VARCHAR2
                                             ,PTARGET_TABLE IN VARCHAR2
                                             ,PCOMMIT IN NUMBER);
            

            END PKG_SQL;
            /

            CREATE OR REPLACE PACKAGE BODY PKG_SQL IS

            PROCEDURE COPY_TABLE_ROWS(PSOURCE_TABLE IN VARCHAR2
                                             ,PTARGET_TABLE IN VARCHAR2
                                             ,PCOMMIT IN NUMBER) IS
                VSYS_REFCURSOR SYS_REFCURSOR;
                VQUERY CLOB;
                VCURSOR_ID NUMBER;
                VDESC_TAB3 DBMS_SQL.DESC_TAB3;
                VTOTAL_COLUMN NUMBER;
                VINSERT CLOB;
                VSEPARATOR CHAR(1);
                VVARCHAR2 VARCHAR2(4000);
                VNUMBER NUMBER;
                VDATE DATE;
                VTIMESTAMP_WITH_TIME_ZONE TIMESTAMP WITH TIME ZONE;
                VINTERVAL_DAY_TO_SECOND INTERVAL DAY TO SECOND;
                VCLOB CLOB;
                VROW NUMBER;
            BEGIN
                VQUERY := 'SELECT * FROM ' || PSOURCE_TABLE;
            
                OPEN VSYS_REFCURSOR FOR VQUERY;
            
                VCURSOR_ID := DBMS_SQL.TO_CURSOR_NUMBER(VSYS_REFCURSOR);
            
                DBMS_SQL.DESCRIBE_COLUMNS3(VCURSOR_ID
                                                  ,VTOTAL_COLUMN
                                                  ,VDESC_TAB3);
            
                FOR I IN 1 .. VTOTAL_COLUMN
                LOOP
                    CASE VDESC_TAB3(I).COL_TYPE
                        WHEN 1 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VVARCHAR2
                                                         ,4000);
                        WHEN 2 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VNUMBER);
                        WHEN 12 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VDATE);
                        WHEN 112 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VCLOB);
                        WHEN 181 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VTIMESTAMP_WITH_TIME_ZONE);
                        WHEN 183 THEN
                            DBMS_SQL.DEFINE_COLUMN(VCURSOR_ID
                                                         ,I
                                                         ,VINTERVAL_DAY_TO_SECOND);
                        ELSE
                            RAISE_APPLICATION_ERROR(-20000
                                                          ,'TODO: tratar o COL_TYPE=' || VDESC_TAB3(I).COL_TYPE);
                    END CASE;
            
                END LOOP;
            
                VROW := 0;
                WHILE DBMS_SQL.FETCH_ROWS(VCURSOR_ID) &gt; 0
                LOOP
            
                    VINSERT := 'INSERT INTO ' || PTARGET_TABLE || '(';
            
                    FOR I IN 1 .. VTOTAL_COLUMN
                    LOOP
                        IF I = 1
                        THEN
                            VSEPARATOR := NULL;
                        ELSE
                            VSEPARATOR := ',';
                        END IF;
            
                        VINSERT := VINSERT || VSEPARATOR || VDESC_TAB3(I).COL_NAME;
                    END LOOP;
            
                    VINSERT := VINSERT || ') VALUES(';
            
                    FOR I IN 1 .. VTOTAL_COLUMN
                    LOOP
                        IF I = 1
                        THEN
                            VSEPARATOR := NULL;
                        ELSE
                            VSEPARATOR := ',';
                        END IF;
            
                        CASE VDESC_TAB3(I).COL_TYPE
                            WHEN 1 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VVARCHAR2);
                                IF VVARCHAR2 IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || '''' || VVARCHAR2 || '''';
                                END IF;
                            WHEN 2 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VNUMBER);
                                IF VNUMBER IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || VNUMBER;
                                END IF;
                            WHEN 12 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VDATE);
                                IF VDATE IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || '''' || VDATE || '''';
                                END IF;
            
                            WHEN 112 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VCLOB);
                                IF VCLOB IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || '''' || VCLOB || '''';
                                END IF;
                            WHEN 181 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VTIMESTAMP_WITH_TIME_ZONE);
                                IF VTIMESTAMP_WITH_TIME_ZONE IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || '''' || VTIMESTAMP_WITH_TIME_ZONE || '''';
                                END IF;
                            WHEN 183 THEN
                                DBMS_SQL.COLUMN_VALUE(VCURSOR_ID
                                                            ,I
                                                            ,VINTERVAL_DAY_TO_SECOND);
                                IF VINTERVAL_DAY_TO_SECOND IS NULL
                                THEN
                                    VINSERT := VINSERT || VSEPARATOR || 'NULL';
                                ELSE
                                    VINSERT := VINSERT || VSEPARATOR || '''' || VINTERVAL_DAY_TO_SECOND || '''';
                                END IF;
                            ELSE
                                RAISE_APPLICATION_ERROR(-20000
                                                              ,'TODO: tratar o COL_TYPE=' || VDESC_TAB3(I).COL_TYPE);
                        END CASE;
            
                    END LOOP;
            
                    VINSERT := VINSERT || ')';
                    EXECUTE IMMEDIATE VINSERT;
                    VROW := VROW + 1;
            
                    IF MOD(VROW
                            ,PCOMMIT) = 0
                    THEN
                        COMMIT;
                    END IF;
                END LOOP;
                COMMIT;
            END COPY_TABLE_ROWS;
            

            END PKG_SQL;
            /

            Exemplo de uso:


            BEGIN
            PKG_SQL.COPY_TABLE_ROWS(PSOURCE_TABLE => 'REMOTE_TABLE@MYDBLINK',
            PTARGET_TABLE => 'LOCAL_TABLE',
            PCOMMIT => 1000);
            END;

            Não vi uma maneira de usar BULK COLLECT na solução. O BULK COLLECT precisa de um RECORD para armazenar o resultado, essa é a parte limitante. Não é possível passar um RECORD dinâmico, ou seja, um %ROWTYPE variando a tabela. Outro detalhe, não é possível fazer um CURSOR dinâmico passando a tabela como parâmetro.

            #107098
            Avatar de Joel SchecheleskiJoel Schecheleski
            Participante

              Resolvi meu problema usando o seguinte bloco:

              BEGIN
              EXECUTE IMMEDIATE
              'DECLARE
              CURSOR C_'||P_NOMTABELA||' IS
              SELECT * FROM '||P_NOMTABELA||'@DBL_BANCO;

                TYPE FETCH_ARRAY IS TABLE OF C_'||P_NOMTABELA||'%ROWTYPE;
                S_ARRAY FETCH_ARRAY;
                BEGIN
                  OPEN C_'||P_NOMTABELA||';
                  LOOP
                    FETCH C_'||P_NOMTABELA||' BULK COLLECT INTO S_ARRAY LIMIT 10000;
              
                    FORALL I IN 1..S_ARRAY.COUNT
                      INSERT INTO '||P_NOMTABELA||' VALUES S_ARRAY(I);
              
                    EXIT WHEN C_'||P_NOMTABELA||'%NOTFOUND;
                  END LOOP;
                  CLOSE C_'||P_NOMTABELA||';
                END; ';
              COMMIT WORK;  
              

              RETURN TRUE;
              END;

              Não era exatamente o que pretendia usar.
              Fico muito feliz pela dica em relação ao uso do pacote DBMS_SQL, acabei pegando um novo conceito de uso desse pacote por intermédio da sua dica.

              Grato pela atenção @RMAN 🙂

              #107099
              Avatar de rmanrman
              Participante

                @Joel Schecheleski

                Bacana o que você fez. Rodou um bloco anônimo inteiro através do EXECUTE IMMEDIATE possibilitando de forma indireta um CURSOR dinâmico.

                Só por curiosidade, compare a minha solução (Sem uso do BULK COLLECT) com a sua em termos de performance. Será que é notório a diferença?

                #107100
                Avatar de Joel SchecheleskiJoel Schecheleski
                Participante

                  @RMAN

                  Cara fiz alguns testes de performace, fiquei surpreso com uma coisa:
                  Quando usado o EXECUTE IMMEDIATE sem o BULK COLLECT a importação
                  levou 4 min e 48s 54ms para importar 254 mil registros.

                  Com o uso do BULK COLLECT fiz uma importação de mais de 1 milhão de registros
                  em apenas 3 min 35s 21ms, ou seja, a importação usando bulk collect +
                  EXECUTE IMMEDIATE se mostrou muito eficiente.

                  Agora com o teste usando o mesmo esquema que vc me passou como dica o desempenho
                  foi similar ao primeiro teste levando 3min 10s 05ms para importar os mesmo 254 mil
                  registros.

                  Bom vou continuar usando o mesmo esquema que apresentei acima. Acredito que essa
                  solução ainda não é a melhor mais se mostrou eficiente e muito confiável em questão
                  de performace.

                  Vou realizar mais testes usando DBMS_SQL + BULK COLLECT, acredito que dá de chegar
                  também em uma eficiente importação como foi o segundo teste, quero reduzir esse tempo pela metade ainda.

                  Mais uma vez obrigado pela dica. B)

                Visualizando 7 posts - 1 até 7 (de 7 do total)
                • Você deve fazer login para responder a este tópico.
                plugins premium WordPress