Reg: procedure is giving erro

Question

Below is my procedure giving error below, kindly check and confirm.

create or replace PROCEDURE CLEANUP_rec

IS

BEGIN

EXECUTE IMMEDIATE 'create table TEMP_JOB_ID_FROM_JOB_DOC_1119 as select JOB_ID, last_update_time_utc, status from J_DOC where 
LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status=''Sent''';

delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119);

  delete from J_DOC 
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119);



EXECUTE IMMEDIATE 'RENAME TABLE TEMP_JOB_ID_FROM_JOB_DOC_1119 TO TEMP_ID_STAT_TIME_FRM_JOB_DOC';



END;

Giving below error

Project: C:\Users\bc8807\AppData\Roaming\SQL Developer\system4.0.1.14.48\o.sqldeveloper.12.2.0.14.48\projects\IdeConnections#database_zltv9883.jpr
t1c3d231_db_connection
Error(10,1): PL/SQL: SQL Statement ignored
Error(11,8): PL/SQL: ORA-00942: table or view does not exist
Error(13,3): PL/SQL: SQL Statement ignored
Error(15,8): PL/SQL: ORA-00942: table or view does not exist

Kindly suggest how can we resolve this issue.

so kindly confirm is below is correct:

create or replace PROCEDURE CLEANUP_AUTOMATION

IS

BEGIN

EXECUTE IMMEDIATE 'create table TEMP_JOB_ID_FROM_JOB_DOC_1119 as select JOB_ID, last_update_time_utc, status from J_DOC where 
LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 90 and status=''Sent''';

  execute immediate 'delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119)';


 execute immediate 'delete from J_DOC
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119)';

EXECUTE IMMEDIATE 'RENAME TABLE TEMP_JOB_ID_FROM_JOB_DOC_1119 TO TEMP_ID_STAT_TIME_FRM_JOB_DOC';



END;

Also Next step would be to automate them inside procedure. we must get the date as input and pass that when rename the table (RENAME TEMP_JOB_ID_FROM_JOB_DOC_1119 TO TEMP_ID_STAT_TIME_FRM_JOB_DOC)

and schedule this procedure in DBMS_SCHEDULER to run every night at 10 PM PST.

Kindly suggest

Answer 1

The issue here is that you are trying to compile a procedure, thus checking that all the code is correct, which uses a table that, at compile time, does not exist.

No matter what you have in the execute immediate string, you can't write a statement that uses something that does not exist.

If you strictly need to create this table at runtine, use it and then rename it, you need to use dynamic SQL for all the statements that use your table:

execute immediate 'delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119)';

 execute immediate 'delete from J_DOC 
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_JOB_ID_FROM_JOB_DOC_1119)';