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;

/