Menu de navegação

[Joel SchecheleskiParticipante]

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) .

[rmanParticipante]

@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.

[Joel SchecheleskiParticipante]

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

[rmanParticipante]

@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) > 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.

[Joel SchecheleskiParticipante]

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 🙂

[rmanParticipante]

@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?

[Joel SchecheleskiParticipante]

@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)

[Autor]

Posts