Início / user-5652860

Ray's questions

Martin Hope
Ray
Asked: 2024-10-04 05:03:40 +0800 CST
  • 5

Tenho um script que copia registros que atendem a uma condição específica para uma tabela temporária. A tabela original é então truncada e os registros são copiados de volta.

A instrução usada para criar a tabela temporária é;

create table ARCHIVE_TMP as select * from ORIGINAL_TABLE where TIMESTAMP_UPDATED < '2017-10-04'

E a declaração para copiar os registros novamente é;

insert /*+ APPEND */ into ORIGINAL_TABLE select * from ARCHIVE_TMP

Quando a segunda linha é executada no script, ela gera a exceção ORA-01007: variable not in select list, mas se eu executar manualmente a segunda instrução, ela funciona como esperado.

Verifiquei se o usuário tem privilégios CREATE e INSERT, pois houve um problema anteriormente em que os privilégios foram atribuídos a uma função em vez do usuário.

Também tentei especificar os nomes das colunas na selectinstrução em vez de usar select *, mas isso não ajudou.

A seguir está o roteiro completo;

create or replace procedure TRUNCATE_EXPIRED_ARCHIVE_DATA
(
    p_retention_period      in number   := 84, 
)
as
begin
    declare
        cursor table_cursor is
            select      owner, table_name, column_name, data_type
            from        all_tab_columns
            where       table_name like '%!_A' escape '!'
                        and column_name like 'TIMESTAMP!_%' escape '!'
            group by    owner, table_name, column_name, data_type           
            order by    table_name, column_name;
        RetentionDt     date := null;
        ExpiredCount    number := 0;
        InsertedCount   number := 0;
        TableExists     number := 0;
        ExpiredSql      varchar2(500);
        SelectSql       varchar2(800);
        SqlStmt         varchar2(1300);
    begin
        RetentionDt := add_months(sysdate, p_retention_period * -1);
        for table_rec in table_cursor loop
            begin
                -- Check if there are any expired records...
                SelectSql := 'select count(*) from ' || table_rec.owner || '.' || table_rec.table_name;
                ExpiredSql := ' where ' || table_rec.column_name || ' < ''' || RetentionDt || '''';
                SqlStmt := SelectSql || ExpiredSql;
                execute immediate SqlStmt into ExpiredCount;
                if (ExpiredCount > 0) then
                    -- Drop the temporary table if it already exists...
                    SelectSql := 'select count(*) from tab where tname = ''ARCHIVE_TMP''';
                    execute immediate SelectSql into TableExists;
                    if (TableExists > 0) then
                        SelectSql := 'drop table REODT_PROD.ARCHIVE_TMP';
                        execute immediate SelectSql;
                    end if;
                    -- Transfer the records to be retained to the temporary table...
                    SelectSql := 'create table REODT_PROD.ARCHIVE_TMP as select * from ' || table_rec.owner || '.' || table_rec.table_name || ' where ' || table_rec.column_name || ' >= ''' || to_char(RetentionDt, 'YYYY-MM-DD') || '''';
                    execute immediate SelectSql;
                    InsertedCount := sql%rowcount;
                    commit;
                    if (InsertedCount > 0) then
                        SelectSql := 'truncate table ' || table_rec.owner || '.' || table_rec.table_name;
                        dbms_output.put_line('Truncate Table    : ' || SelectSql);
                        execute immediate SelectSql;
                        SelectSql := 'insert /*+ APPEND */ into ' || table_rec.owner || '.' || table_rec.table_name || ' select * from REODT_PROD.ARCHIVE_TMP';
                        execute immediate SelectSql into InsertedCount;
                        commit;
                    end if;
                end if;
            end;
        end loop;
    end;
end;
/
oracle