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;
Best Answer
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.