Oracle PL SQL Insert Statement - ORA-01007: variable not in select list

Question

I have a script that copies records meeting a specific condition to a temporary table. The original table is then truncated and the records are copied back.

The statement used to create the temporary table is;

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

And the statement to copy the records back again is;

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

When the 2nd line is executed in the script it raises the exception ORA-01007: variable not in select list but if I manually run the 2nd statement it works as expected.

I have checked the user has CREATE and INSERT privileges as there was a problem previously where the privileges were assigned to a role instead of the user.

I have also tried specifying the column names in the select statement instead of using select * but this didn't help.

Following is the entire script;

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

Answer 1

SelectSql := 'insert /*+ APPEND */ into ' || table_rec.owner || '.' || table_rec.table_name || ' select * from REODT_PROD.ARCHIVE_TMP';
execute immediate SelectSql into InsertedCount;

Is an INSERT (and not a SELECT) so you do not want to have an INTO clause.

You can simplify the procedure to:

create or replace procedure TRUNCATE_EXPIRED_ARCHIVE_DATA
(
    p_retention_period      in number   := 84
)
as
  TABLE_NOT_FOUND EXCEPTION;
  RetentionDt     date := add_months(TRUNC(sysdate), -p_retention_period);
  ExpiredCount    number := 0;
  InsertedCount   number := 0;

  PRAGMA EXCEPTION_INIT(TABLE_NOT_FOUND, -942);
begin
  for table_rec in (
    select      owner, table_name, column_name, data_type
    from        all_tab_columns
    where       table_name like '%!_A' escape '!'
    and         column_name like 'TIMESTAMP!_%' escape '!'
    order by    table_name, column_name
  ) loop
    -- Check if there are any expired records...
    execute immediate
         'select count(*)'
      || ' from "' || table_rec.owner || '"."' || table_rec.table_name || '"'
      || ' where "' || table_rec.column_name || '" < :1'
      INTO ExpiredCount
      USING RetentionDt;
    IF ExpiredCount = 0 THEN
      DBMS_OUTPUT.PUT_LINE(
        '"' || table_rec.owner || '"."' || table_rec.table_name || '" Not found'
      );
      CONTINUE;
    END IF;
    BEGIN
      execute immediate 'drop table REODT_PROD.ARCHIVE_TMP';
    EXCEPTION
      WHEN TABLE_NOT_FOUND THEN
        NULL;
    END;
    execute immediate
         'create table REODT_PROD.ARCHIVE_TMP as'
      || ' select * from "' || table_rec.owner || '"."' || table_rec.table_name || '"'
      || ' where "' || table_rec.column_name || '"'
      || ' >= TIMESTAMP ''' || TO_CHAR(RetentionDt, 'YYYY-MM-DD HH24:MI:SS') || '''';

    InsertedCount := sql%rowcount;
    commit;

    if InsertedCount > 0 then
      execute immediate 'truncate table "' || table_rec.owner || '"."' || table_rec.table_name || '"';
      execute immediate
           'insert /*+ APPEND */ into "' || table_rec.owner || '"."' || table_rec.table_name || '"'
        || ' select * from REODT_PROD.ARCHIVE_TMP';
      commit;
    end if;
  end loop;
end;
/

fiddle