0

I am getting this error:

[Error] PLS-00103 (23: 9): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier>

when attempting to run this code. What is it that I'm doing wrong here?
Should I use bulk collect. if so, how?

CREATE OR REPLACE PROCEDURE P_CLEAN_LICENSEE_ARCHIVE AS
BEGIN
    /*******************************************************************/
    /* This is an array of IDs for licensees                           */
    /*******************************************************************/
   array_l := l_arr(1026679, 1026714, 1036991, 1026735, 1026715, 1159363, 1026703, 1169002, 1052762, 1070152, 1026684, 
                    1026685, 1164376, 1026693, 1026733, 1026698, 1026701, 1026680, 1026708, 1026710, 1026688, 1026697, 
                    1026690, 1026691, 1026692, 1099236, 1026694, 1099235, 1026734, 1026700, 1026682, 1026683, 1099142, 
                    1026687, 1026689, 1026732, 1026699, 1047967, 1026702, 1026704, 1026711, 1026712, 1026713, 1026681, 
                    1108357, 1175754, 1079806, 1099275, 1026709, 1099281, 1099282, 1208557, 1175755, 1194482, 1112569);

    /*******************************************************************/
    /* Loop through the IDs and do the work                            */
    /*******************************************************************/
    FOR i IN array_l.FIRST .. array_l.LAST
    LOOP
        /*******************************************************************/
        /* The following SQL retrieves the fkp_fd_rid from all the records */
        /* in the archive modified more than one year ago for the passed   */ 
        /* CLI_RID, builds a temp table with the fd_rids for use to delete */
        /* the records.                                                    */
        /*******************************************************************/
        CREATE TABLE fd_rid AS 
            SELECT fd.* FROM filedirectory fd 
            WHERE fd.FD_RID IN (SELECT fkp.fkp_fd_rid FROM filekeypair fkp 
                                WHERE fkp.fkp_keyword = 'CLI_RID' 
                                  AND fkp.fkp_value = TO_CHAR(array_l(i))) 
              AND fd.FD_LASTMODIFIED < SYSDATE-365;

        /*******************************************************************/
        /* This code then deletes the records from both filekeypair and    */
        /* filedirectory.                                                  */       
        /*******************************************************************/
        DELETE FROM filekeypair WHERE fkp_fd_rid IN (SELECT * FROM fd_rid);
        COMMIT;
        DELETE FROM filedirectory WHERE fd_rid IN (SELECT * FROM fd_rid);
        COMMIT;
        /*******************************************************************/
        /* Now drop the temp table.                                        */       
        /*******************************************************************/
        DROP TABLE fd_rid;
        COMMIT;
    END LOOP;
END;    
6

You can not write DDL in PL/SQL block directly as you have shown in your question.

However you can execute these DDLs using EXECUTE IMMEDIATE command as demonstrated below.

  CREATE OR REPLACE PROCEDURE ddl_test AS
  BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tbl1';
  END;
  /

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.