DokuWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Portfolio PeopleSoft Queries ======

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:

<code sql>


---------------------------------------
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


</code>


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.)


<code sql>
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
</code>
DokuWiki

User Tools

Site Tools

Page Tools
