Fellas,
I need to execute a simple pl/sql procedure written for Oracle in Informix, the syntax seem completely different in Informix. I'll appreciate help in converting this code to run in Informix.
CREATE OR REPLACE PROCEDURE archive_table
AS
DECLARE
-- First create cursors for the different tables
CURSOR application_rec IS
SELECT ID,
STATUS,
TYPE,
APPLICANT_ID,
APPROVER_ID,
INPUT_DATE,
SYS_USERID,
SYS_FNAME,
SYS_LNAME,
SYS_PHONE,
APPROVER_REASON,
ADMIN_REASON
FROM APPLICATION
WHERE ROUND(MONTHS_BETWEEN(SYSDATE,INPUT_DATE),0) >= 3;
CURSOR tivoliadminapp_rec IS
SELECT T.APP_ID,
T.ACTION,
T.TIVOLIROLE_ID,
T.EVENTS_FLAG
FROM TIVOLIADMINAPP T, APPLICATION A
WHERE T.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
CURSOR unixgroupapp_rec IS
SELECT U.APP_ID,
U.ACTION,
U.GROUP_NAME,
U.REASON,
U.START_DATE,
U.END_DATE
FROM UNIXGROUPAPP U, APPLICATION A
WHERE U.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
CURSOR passwordapp_rec IS
SELECT P.APP_ID,
P.PASSWORD,
P.USECODE,
P.COLLECTED,
FROM PASSWORDAPP P , APPLICATION A
WHERE P.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
CURSOR approverapp_rec IS
SELECT V.APP_ID,
V.ACTION,
V.USER_ID,
V.EMAIL
FROM APPROVERAPP V, APPLICATION A
WHERE V.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
CURSOR systemadminapp_rec IS
SELECT S.APP_ID,
S.ACTION
FROM SYSTEMADMINAPP S, APPLICATION A
WHERE S.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
CURSOR useridapp IS
SELECT U.APP_ID,
U.ACTION,
U.OLD_FNAME,
U.OLD_LNAME,
U.PGROUP,
U.SGROUP1,
U.SGROUP2,
U.SGROUP3,
U.SGROUP4,
U.SHELL_NAME,
U.HOME_DIR,
U.START_DATE,
U.END_DATE,
U.DESCRIPTION,
U.REASON
FROM USERIDAPP U, APPLICATION A
WHERE U.APP_ID = A.ID
AND ROUND(MONTHS_BETWEEN(SYSDATE, A.INPUT_DATE), 0) >= 3;
-- create record variable to for each cursor
app_rec application_rec%ROWTYPE;
tivoli_rec tivoliadminapp_rec%ROWTYPE;
unix_rec unixgroupapp_rec%ROWTYPE;
pass_rec passwordapp_rec%ROWTYPE;
appr_rec approverapp_rec%ROWTYPE;
system_rec systemadminapp_rec%ROWTYPE;
userid_rec useridapp_rec%ROWTYPE;
BEGIN
-- insert into application_arch table
OPEN application_rec;
LOOP
FETCH application_rec into app_rec;
EXIT WHEN application_rec%NOTFOUND;
INSERT INTO APPLICATION_ARCH
VALUES (app_rec.ID,
app_rec.STATUS,
app_rec.TYPE,
app_rec.APPLICANT_ID,
app_rec.APPROVER_ID,
app_rec.INPUT_DATE,
app_rec.SYS_USERID,
app_rec.SYS_FNAME,
app_rec.SYS_LNAME,
app_rec.SYS_PHONE,
app_rec.SYS_APPROVER_REASON,
app_rec.ADMIN_REASON);
END LOOP;
CLOSE application_rec;
-- insert into tivoliadminapp_arch table
OPEN tivoliadminapp_rec;
LOOP
FETCH tivoliadminapp_rec into tivoli_rec;
EXIT WHEN tivoliadminapp_rec%NOTFOUND;
INSERT INTO TIVOLIADMINAPP_ARCH
VALUES (tivoli_rec.APP_ID,
tivoli_rec.ACTION,
tivoli_rec.TIVOLIROLE_ID,
tivoli_rec.EVENTS_FLAG);
END LOOP;
CLOSE tivoliadminapp_rec;
-- insert into unixgroupapp_arch table
OPEN unixgroupapp_rec;
LOOP
FETCH unixgroupapp_rec into unix_rec;
EXIT WHEN unixgroupapp_rec%NOTFOUND;
INSERT INTO UNIXGROUPAPP_ARCH
VALUES (unix_rec.APP_ID,
unix_rec.ACTION,
unix_rec.GROUP_NAME,
unix_rec.REASON,
unix_rec.START_DATE,
unix_rec.END_DATE);
END LOOP;
CLOSE unixgroupapp_rec;
-- insert into passwordapp_arch table
OPEN passwordapp_rec;
LOOP
FETCH passwordapp_rec into pass_rec;
EXIT WHEN passwordapp_rec%NOTFOUND;
INSERT INTO PASSWORDAPP_ARCH
VALUES (pass_rec.APP_ID,
pass_rec.PASSWORD,
pass_rec.USECODE,
pass_rec.COLLECTED);
END LOOP;
CLOSE passwordapp_rec;
-- insert into approverapp_arch table
OPEN approverapp_rec;
LOOP
FETCH approverapp_rec into appr_rec;
EXIT WHEN approverapp_rec%NOTFOUND;
INSERT INTO APPROVERAPP_ARCH
VALUES (appr_rec.APP_ID,
appr_rec.ACTION,
appr_rec.USER_ID,
appr_rec.EMAIL);
END LOOP;
CLOSE approverapp_rec;
-- insert into systemadminapp_arch table
OPEN systemadminapp_rec;
LOOP
FETCH systemadminapp_rec into system_rec;
EXIT WHEN systemadminapp_rec%NOTFOUND;
INSERT INTO SYSTEMADMINAPP_ARCH
VALUES (system_rec.APP_ID,
system_rec.ACTION);
END LOOP;
CLOSE systemadminapp_rec;
-- insert into useridapp_arch table
OPEN useridapp_rec;
LOOP
FETCH useridapp_rec into userid_rec;
EXIT WHEN useridapp_rec%NOTFOUND;
INSERT INTO USERIDAPP_ARCH
VALUES (userid_rec.APP_ID,
userid_rec.ACTION
userid_rec.OLD_FNAME,
userid_rec.OLD_LNAME,
userid_rec.PGROUP,
userid_rec.SGROUP1,
userid_rec.SGROUP2,
userid_rec.SGROUP3,
userid_rec.SGROUP4,
userid_rec.SHELL_NAME,
userid_rec.HOME_DIR,
userid_rec.START_DATE,
userid_rec.END_DATE,
userid_rec.DESCRIPTION,
userid_rec.REASON);
END LOOP;
CLOSE useridapp_rec;
END;
/
