Procedure is not running

Question

I am running below query, giving error. what needs to be done to get the requested result?

create or replace PROCEDURE data_cleanup_29th_nov_2020
IS
BEGIN

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

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

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

EXECUTE IMMEDIATE 'RENAME TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC TO TEMP_JOB_ID_FROM_JOB_DOC_1119';

END;

commit;

After running the procedure getting below error

BEGIN
  data_cleanup_29th_nov_2020();
--rollback; 
END;

ORA-00955: name is already used by an existing object ORA-06512: at "HUB.DATA_CLEANUP_29TH_NOV_2020", line 7 ORA-06512: at line 2 00955. 00000 - "name is already used by an existing object

Tried with below query:

CREATE OR REPLACE PROCEDURE ATAS_CLEANUP_NOV_2020 IS
BEGIN
    EXECUTE IMMEDIATE 'create table TEMP_ID_STAT_TIME_FRM_JOB_DOC as select JOB_ID, last_update_time_utc, status from J_DOC where 
LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 30 and status=''Sent''';

    EXECUTE IMMEDIATE 'delete from HUB_SIGNATURE_DETAILS where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    EXECUTE IMMEDIATE 'delete from J_DOC
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    --EXECUTE IMMEDIATE 'RENAME TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC TO TEMP_JOB_ID_FROM_JOB_DOC_1119';
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE TEMP_JOB_ID_FROM_JOB_DOC_1119';
    EXCEPTION
        WHEN OTHER THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'ALTER TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC RENAME TO TEMP_JOB_ID_FROM_JOB_DOC_1119';
END;

Getting below error:

Error(14,5): PL/SQL: Statement ignored Error(17,14): PLS-00201: identifier 'OTHER' must be declared

Tried with this and it works successfully:

CREATE OR REPLACE PROCEDURE ATAS_LATEST_DATA_cleanup_nov IS
BEGIN


    EXECUTE IMMEDIATE 'delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    EXECUTE IMMEDIATE 'delete from J_DOC 
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    --EXECUTE IMMEDIATE 'RENAME TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC TO TEMP_JOB_ID_FROM_JOB_DOC_1119';
   --BEGIN
     -- EXECUTE IMMEDIATE 'DROP TABLE TEMP_JOB_ID_FROM_JOB_DOC_1119';
    --EXCEPTION
      --  WHEN OTHER THEN
        --   NULL;
   -- END;
    EXECUTE IMMEDIATE 'ALTER TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC RENAME TO TEMP_JOB_ID_FROM_JOB_DOC_1119';
END;

COMMIT;

BEGIN
  ATAS_LATEST_DATA_cleanup_nov();
--rollback; 
END;

anonymous block completed

Created the temporary table outside the procedure. 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) - 30 and status='Sent'

And used the temporary table inside procedure now after procedure run data is present in TEMP_JOB_ID_FROM_JOB_DOC_1119

Now we have to implement to get the date as input and pass that when rename the table (RENAME TEMP_ID_STAT_TIME_FRM_JOB_DOC TO TEMP_JOB_ID_FROM_JOB_DOC_1119 )

and schedule this procedure in DBMS_SCHEDULER to run every night at 10 PM PST. How can we do this?

Answer 1

Your rename table command is wrong.

It should be:

ALTER TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC RENAME TO TEMP_JOB_ID_FROM_JOB_DOC_1119;

Also, you can execute the procedure once, If you will execute it again then it will throw an error at the rename command.

Now, See if there is any table available in the DB already. If yes, then remove it before calling the following procedure again.

So try this code:

CREATE OR REPLACE PROCEDURE DATA_CLEANUP_29TH_NOV_2020
(P_IN_DATE IN DATE)
IS
BEGIN
    EXECUTE IMMEDIATE 'create table TEMP_ID_STAT_TIME_FRM_JOB_DOC as select JOB_ID, last_update_time_utc, status from J_DOC where 
LAST_UPDATE_TIME_UTC <= TRUNC(SYSDATE) - 30 and status=''Sent''';

    EXECUTE IMMEDIATE 'delete from HUB_SIG where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    EXECUTE IMMEDIATE 'delete from J_DOC 
 where JOB_id IN ( SELECT JOB_ID
  FROM TEMP_ID_STAT_TIME_FRM_JOB_DOC)';

    --EXECUTE IMMEDIATE 'RENAME TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC TO TEMP_JOB_ID_FROM_JOB_DOC_1119';
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE TEMP_JOB_ID_FROM_JOB_DOC_1119';
    EXCEPTION
        WHEN OTHER THEN
            NULL;
    END;
    EXECUTE IMMEDIATE 'ALTER TABLE TEMP_ID_STAT_TIME_FRM_JOB_DOC RENAME TO TEMP_JOB_ID_FROM_JOB_DOC_' || TO_CHAR(P_IN_DATE, 'MMYY');
END;
/

It is always good practice to avoid dynamic queries.

You can create one table outside of this procedure and use it to store temporary data. Or you can use the Global temporary table.