Ray's questions

我有一个脚本，它将满足特定条件的记录复制到临时表中。然后原始表被截断并将记录复制回来。

用于创建临时表的语句是；

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

而再次复制记录的声明是；

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

当脚本中执行第二行时，它会引发异常，ORA-01007: variable not in select list但如果我手动运行第二条语句，它就会按预期工作。

我已经检查用户是否具有 CREATE 和 INSERT 权限，因为之前存在问题，即权限被分配给角色而不是用户。

我也尝试过在语句中指定列名select而不是使用select *，但这没有帮助。

以下是整个脚本；

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;
/