The @sfis89test Oracle bug is patched and the links appear to be working correctly now. Here are the queries that determine access to the employee portfolio that failed to run:
--------------------------------------- SELECT COUNT(*) FROM wu_data.MVW_current_benefits@sfis89test WHERE emplid='$wesid' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist ORA-02063: preceding line FROM SFIS89TEST (DBD ERROR: error possibly near <*> indicator at CHAR 29 IN 'select count(*) from wu_data.<*>MVW_current_benefits@sfis89test where emplid='$wesid' ') [FOR Statement "select count(*) from wu_data.MVW_current_benefits@sfis89test where emplid='$wesid' "] at ./sql_access_checker.pl line 67, <> line 25. --------------------------------------- SELECT 1 FROM DUAL WHERE ('$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.MVW_current_benefits@sfis89test b WHERE a.emplid = b.emplid/*and a.emplid in ('291054','251078')*/ )) DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist ORA-02063: preceding line FROM SFIS89TEST (DBD ERROR: error possibly near <*> indicator at CHAR 192 IN 'SELECT 1 FROM DUAL WHERE ('$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.MVW_current<*>_benefits@sfis89test b WHERE a.emplid = b.emplid/*and a.emplid in ('291054','251078')*/ )) ') [FOR Statement "SELECT 1 FROM DUAL WHERE ('$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.MVW_current_benefits@sfis89test b WHERE a.emplid = b.emplid/*and a.emplid in ('291054','251078')*/ )) "] at ./sql_access_checker.pl line 67, <> line 26. --------------------------------------- SELECT COUNT(*) FROM wu_data.MVW_current_benefits@sfis89test b ,wu_data.vw_people_facstaff_curr a WHERE a.emplid = b.emplidand REPLACE (a.wes_email, '@wesleyan.edu', '') = '$username' DBD::Oracle::db PREPARE failed: ORA-00933: SQL command NOT properly ended (DBD ERROR: error possibly near <*> indicator at CHAR 127 IN 'select count(*) from wu_data.MVW_current_benefits@sfis89test b ,wu_data.vw_people_facstaff_curr a where a.emplid = b.emplidand <*>REPLACE (a.wes_email, '@wesleyan.edu', '') = '$username' ') [FOR Statement "select count(*) from wu_data.MVW_current_benefits@sfis89test b ,wu_data.vw_people_facstaff_curr a where a.emplid = b.emplidand REPLACE (a.wes_email, '@wesleyan.edu', '') = '$username' "] at ./sql_access_checker.pl line 67, <> line 29. --------------------------------------- SELECT 1 FROM DUAL WHERE '$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.mvw_current_benefits@sfis89test b WHERE a.emplid = b.emplid AND b.plan_type = '46') DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist ORA-02063: preceding line FROM SFIS89TEST (DBD ERROR: error possibly near <*> indicator at CHAR 191 IN 'SELECT 1 FROM DUAL WHERE '$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.mvw_current<*>_benefits@sfis89test b WHERE a.emplid = b.emplid and b.plan_type = '46') ') [FOR Statement "SELECT 1 FROM DUAL WHERE '$username' IN ( SELECT REPLACE (a.wes_email, '@wesleyan.edu', '') FROM wu_data.vw_people_facstaff_curr a, wu_data.mvw_current_benefits@sfis89test b WHERE a.emplid = b.emplid and b.plan_type = '46') "] at ./sql_access_checker.pl line 67, <> line 30. --------------------------------------- SELECT COUNT(*) FROM wu_data.MVW_current_benefits@sfis89test WHERE emplid='$wesid' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist ORA-02063: preceding line FROM SFIS89TEST (DBD ERROR: error possibly near <*> indicator at CHAR 29 IN 'select count(*) from wu_data.<*>MVW_current_benefits@sfis89test where emplid='$wesid' ') [FOR Statement "select count(*) from wu_data.MVW_current_benefits@sfis89test where emplid='$wesid' "] at ./sql_access_checker.pl line 67, <> line 33. --------------------------------------- SELECT COUNT(*) FROM elf.elf_form_permission WHERE u_id = '$wesid' OR u_id = '$second_wesid' DBD::Oracle::st EXECUTE failed: ORA-01722: invalid NUMBER (DBD ERROR: error possibly near <*> indicator at CHAR 59 IN 'select count(*) from elf.elf_form_permission where u_id = '<*>$wesid' or u_id = '$second_wesid' ') [FOR Statement "select count(*) from elf.elf_form_permission where u_id = '$wesid' or u_id = '$second_wesid' "] at ./sql_access_checker.pl line 68, <> line 45. --------------------------------------- Searched 174 queries
Here is a small sample of captured SQL queries issued through portfolio. Only 2 queries from each table were executed. (The code window is scrollable.)
SELECT a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,b.descr50) FROM sysadm.ps_accomplishments a, sysadm.ps_school_tbl b WHERE a.school_code = b.school_code(+) AND a.emplid = '414987' DBD::Oracle::db PREPARE failed: ORA-00904: "B"."DESCR50": invalid identifier (DBD ERROR: error possibly near <*> indicator at CHAR 75 IN 'select a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,<*>b.descr50) from sysadm.ps_accomplishments a, sysadm.ps_school_tbl b where a.school_code = b.school_code(+) AND a.emplid = '414987'') [FOR Statement "select a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,b.descr50) from sysadm.ps_accomplishments a, sysadm.ps_school_tbl b where a.school_code = b.school_code(+) AND a.emplid = '414987'"] at ./sql_checker.pl line 112, <> chunk 1520. --------------------------------------- SELECT a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,b.descr50),yr_acquired FROM sysadm.ps_accomplishments a, sysadm.ps_school_tbl b WHERE a.school_code = b.school_code(+) AND a.emplid='250370' ORDER BY a.dt_issued DBD::Oracle::db PREPARE failed: ORA-00904: "B"."DESCR50": invalid identifier (DBD ERROR: error possibly near <*> indicator at CHAR 75 IN 'select a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,<*>b.descr50),yr_acquired from sysadm.ps_accomplishments a, sysadm.ps_school_tbl b where a.school_code = b.school_code(+) AND a.emplid='250370' order by a.dt_issued') [FOR Statement "select a.emplid,a.DT_ISSUED,a.accomplishment,decode(b.descr50,'',a.school,b.descr50),yr_acquired from sysadm.ps_accomplishments a, sysadm.ps_school_tbl b where a.school_code = b.school_code(+) AND a.emplid='250370' order by a.dt_issued"] at ./sql_checker.pl line 112, <> chunk 1707. --------------------------------------- SELECT * FROM sysadm.ps_wes_enrlday_rel ORDER BY relation_descr DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 21 IN 'SELECT * from sysadm.<*>ps_wes_enrlday_rel order by relation_descr') [FOR Statement "SELECT * from sysadm.ps_wes_enrlday_rel order by relation_descr"] at ./sql_checker.pl line 112, <> chunk 3756. --------------------------------------- SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE FROM sysadm.ps_wes_enrlday_prs WHERE emplid='708912' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 198 IN 'SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE from sysadm.<*>ps_wes_enrlday_prs where emplid='708912'') [FOR Statement "SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE from sysadm.ps_wes_enrlday_prs where emplid='708912'"] at ./sql_checker.pl line 112, <> chunk 3869. --------------------------------------- SELECT * FROM sysadm.ps_wes_enrlday_rel ORDER BY relation_descr DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 21 IN 'SELECT * from sysadm.<*>ps_wes_enrlday_rel order by relation_descr') [FOR Statement "SELECT * from sysadm.ps_wes_enrlday_rel order by relation_descr"] at ./sql_checker.pl line 112, <> chunk 4125. --------------------------------------- SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE FROM sysadm.ps_wes_enrlday_prs WHERE emplid='708912' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 198 IN 'SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE from sysadm.<*>ps_wes_enrlday_prs where emplid='708912'') [FOR Statement "SELECT EMPLID,NAME,SSN,WES_STUDENT_TYPE,SEX,MAR_STATUS,CITIZENSHIP_STATUS,COUNTRY,BIRTHDATE,BIRTHPLACE,ETHNIC_GROUP,WES_ETHNICITY_DTL,MAJOR,WES_CLASS_YEAR,WESPO,FACILITY_NAME,ROOM,PHONE from sysadm.ps_wes_enrlday_prs where emplid='708912'"] at ./sql_checker.pl line 112, <> chunk 4129. --------------------------------------- SELECT COUNT(*) FROM sysadm.ps_wes_enrlday_adr WHERE emplid='708912' AND UPPER(wes_addr_type)='CAMP' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 28 IN 'SELECT count(*) from sysadm.<*>ps_wes_enrlday_adr where emplid='708912' and upper(wes_addr_type)='CAMP'') [FOR Statement "SELECT count(*) from sysadm.ps_wes_enrlday_adr where emplid='708912' and upper(wes_addr_type)='CAMP'"] at ./sql_checker.pl line 112, <> chunk 4185. --------------------------------------- SELECT ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,POSTAL,COUNTRY,PHONE FROM sysadm.ps_wes_enrlday_adr WHERE emplid='708912' AND UPPER(wes_addr_type)='HOME' DBD::Oracle::db PREPARE failed: ORA-00942: TABLE OR VIEW does NOT exist (DBD ERROR: error possibly near <*> indicator at CHAR 78 IN 'SELECT ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,POSTAL,COUNTRY,PHONE from sysadm.<*>ps_wes_enrlday_adr where emplid='708912' and upper(wes_addr_type)='HOME'') [FOR Statement "SELECT ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,POSTAL,COUNTRY,PHONE from sysadm.ps_wes_enrlday_adr where emplid='708912' and upper(wes_addr_type)='HOME'"] at ./sql_checker.pl line 112, <> chunk 4189. --------------------------------------- Searched 72 queries