1 / 22

Picture Perfect

Picture Perfect. A Batch Process to load photos into Oracle 9.0 Campus Solutions. Session # 27561. Presenter. Sushma Mendu Applications Developer Enterprise Applications The College of New Jersey Primary areas of work: Student Administration Security . About TCNJ.

venus
Download Presentation

Picture Perfect

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Picture Perfect ABatch Process to load photos into Oracle 9.0 Campus Solutions Session # 27561 The College of New Jersey

  2. Presenter SushmaMendu Applications Developer Enterprise Applications The College of New Jersey Primary areas of work: Student Administration Security The College of New Jersey

  3. About TCNJ The College of New Jersey (TCNJ) was founded in 1855 and has grown to become one of the top comprehensive colleges in the nation. TCNJ is the State College of NJ located in in Ewing, close to Trenton Primarily Undergraduate Liberal Arts Institution. A wealth of degree programs offered through TCNJ’s seven schools—Arts & Communication; Business; Culture and Society; Education; Science; Nursing, Health, and Exercise Science; and Engineering make its retention and graduation rates among the highest in the country. Students – 6,400 undergraduate, 850 graduate Faculty - 325 full-time, 350 adjunct Staff - 675 full-time, 125 part-time The College of New Jersey

  4. Technology at TCNJ • Migrated from legacy system SIS to PeopleSoft-Oracle suite of applications in 2009. • Currently on • Oracle 10g • Peoplesoft 9.0 • Peopletools 8.49.15 • SunSolaris 9.0 The College of New Jersey

  5. Background PeopleSoft CS 8.9 has several pages which can display photos - a highly visible feature in comparison to the legacy system. This is a capability that the campus community (advisors, administrative staff, campus police etc) would benefit from extensively. There are also other third party systems (such as SOCS) that would benefit from access to these photos. The College of New Jersey

  6. Source • The photos are obtained via an add-on photo management system – IDWorks. • The photos are located on the IDWorks server. • Details about the photos are maintained on the Blackboard database. • Filename – Customer_ID • File Location – Photo_Path The College of New Jersey

  7. Delivered process – Step 1 The College of New Jersey

  8. Step 2 The College of New Jersey

  9. Step 3 The College of New Jersey

  10. Challenge PeopleSoft’s current capability is to manually load one photo at a time – we needed a Mass load process. TCNJ wanted to leverage PeopleSoft functionality and capability to the maximum and minimize customizations TCNJ needed a process that would enable photos to be loaded into PS and also be accessible to other third party systems. The process had to handle large loads during Orientation when incoming students get their pictures taken for their ID cards and also Daily updates for existing students and new Employees The College of New Jersey

  11. Research • Some of solutions we researched included • Loading the photos via the Employee Photo UploadComponent Interface. • C++ load programs • SQL or PL/SQL scripts to load photos in the back-end • Had to be run by the DBAs • Did not provide the capability to check the PeopleSoft system for existing Emplids • Did not give the Functional users control over the load process • Did not provide any log of uploaded/updated photos The College of New Jersey

  12. Solution Other third party software ID Works TCNJ PhotoHub PERL script Gets files, does compare and loads into Oracle Dbase – Photo Hub.Scheduled to run daily Black board DBase TCNJ SERVER Photos.jpg SHELL script Creates a list of new filesS Scheduled to run once a week. PS pages TCNJ PROD SERVER Photos.jpg Emplid.txt SQR Loads jpgs to PS tables PS Oracle DBase The College of New Jersey

  13. Script 1 This script runs daily to keep the photo hub in sync with the BlackBoard/IDWorks tables.  This is scheduled via cron. The script uses DBI and DBD Perl packages which allow access to many database environments in a standard way. DBD implementations exist for proprietary products such as Oracle, Microsoft SQL Server, IBM DB2, and for free-software databases such as SQLite, PostgreSQL, Firebird, and MySQL. The College of New Jersey

  14. Script 2 This is comprised of 4 scripts (3 Bourne shell scripts and one SQL script) to pull down the latest photos as JPGs each Tuesday morning to PAWS.There is a main "wrapper" script (aka driver script).  It calls a script to get all the EMPLIDs where the photos have been updated in the last 8 days.  That script uses and the SQL script.Finally, the wrapper script calls the script that actually loops through all the EMPLIDs that were gathered to download the photos to a directory on the PAWS server and create the text file containing those filenames.   The College of New Jersey

  15. This is the main script (getphotos.wrapper) that runs the sub-scripts below to get the latest photos every Tuesday morning #!/bin/shcd /ora/u05/xfer/photo/ora/u05/xfer/photo/getemplids.sh >/dev/null 2>&1touch /ora/u05/xfer/photo/EMPLIDs.txt/ora/u05/xfer/photo/getphotos.sh </ora/u05/xfer/photo/EMPLIDs.txt >/dev/null 2>&1cd /ora/u05/xfer/photochownpsoft *.jpg *.log *.txt The College of New Jersey

  16. This is the getemplids.sh script referenced in getphotos.wrapper, line 4 #!/bin/shORACLE_HOME=/u01/app/oracle/product/10.2.0PATH=$ORACLE_HOME/bin\:$PATHexport ORACLE_HOME PATH/bin/rm -f /ora/u05/xfer/photo/EMPLIDs.txtsqlplus -S photoid/xxxxxx@photoserver.tcnj.edu:1521/PHOTOS.photoserver.tcnj.edu @/ora/u05/xfer/photo/getemplids.sql The College of New Jersey

  17. This is the getemplids.sql script referenced in the last line of getemplids.sh SPOOL /ora/u05/xfer/photo/EMPLIDs.txtSET TERMOUT OFFSET TRIMS ONSET TRIM ONSET PAGESIZE 0SET HEADING OFFSET FEEDBACK OFFSET TAB OFFSELECT EMPLIDFROM PHOTOID.PHOTOSWHERE LAST_UPDATE > SYSDATE - 8;SPOOL OFFEXIT The College of New Jersey

  18. This is the script that gets the actual photos out of the PHOTO database, referenced in line 6 of getphotos.wrapper . An Apache web server running on the photoserver provides a URL interface to the PHOTO Dbase #!/bin/shPATH=/usr/bin:/usr/sbin:/bin:/sbin;export PATHWGET=/usr/sfw/bin/wget/bin/rm -f *.jpg File_names.txt nophoto.logwhile read EMPLID ; do  if [ ! -z "$EMPLID" ]; then    echo "Getting photo for $EMPLID ..."    $WGET -q -O tmp.jpg 'http://photouser:xxxxxx@photoserver.tcnj.edu/idphoto-cgi/showphoto.pl?emplid='"$EMPLID"'&days=16'    if [ -s tmp.jpg ] ; thenmv tmp.jpg "$EMPLID.jpg"      echo "$EMPLID.jpg" >>File_names.txt    else      echo "$EMPLID" >>nophoto.logfifidonerm -f tmp.jpgchmod 644 *.jpg File_names.txt nophoto.log The College of New Jersey

  19. begin-sql CREATE OR REPLACE DIRECTORY BLACKBOARD_PHOTOS AS '/ora/u05/xfer/photo/'; end-sql begin-sql DECLARE src_lob BFILE ;; dest_lob BLOB;; src_lob_exists BOOLEAN;; f utl_file.file_type;; file_name varchar2(200);; src_emplid varchar2(200);; emplid_exists PS_PERSON.EMPLID%TYPE;; BEGIN src_lob := bfilename( 'BLACKBOARD_PHOTOS', $Emplid_jpg );; INSERT INTO PS_EMPL_PHOTO VALUES($Emplid, 1, EMPTY_BLOB()) RETURNING EMPLOYEE_PHOTO INTO dest_lob;; DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);; DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob, SRC_LOB => src_lob, AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );; DBMS_LOB.CLOSE(src_lob);; EXCEPTION WHEN NO_DATA_FOUND THEN utl_file.fclose(f);; END;; end-sql The College of New Jersey

  20. Summary • New photo is taken via IDworks • The photo is stored on IDworks server • Information about the photo – CustomerID, Photopath are available on the Dbase – Blackboard • Shell script 1 reads information on Blackboard, compares to local Dbase and • Inserts/Updates photos to TCNJ Photo Hub in BLOB format • FTPs the photos as BLOB to TCNJ Server • This script is scheduled via Cron to run daily • Shell script 2 reads information on the TCNJ PhotoHub and • gives a list of Emplids created in the past 7days - Change Log • Saves the photos as JPG on the prod server • This script is scheduled via Cron to run once a week • Run Control / Scheduled job • SQR reads the change log file and • Compares with PS transaction tables to make sure Emplid is valid • Checks if photo is existing – Insert or Update • Creates a log of activity • PS logs – emailed via Distribution Lists to Functional Users The College of New Jersey

  21. Solution - SQR and RunCntl page • Advantages: • Built off of existing PeopleSoft functionality • Will not interfere with upgrades • Allows users to have control over the process • Allows control over the cohort of Emplids for whom we want to load photos • Can be scheduled • Clean and non invasive • Secure • This process can work with other source Dbases as well. • Disadvantages: • Reliant on a third party system to provide the jpg files • Requires Shell scripts to run as a means of connecting to the photo source • Sometimes the photos are imported in different sizes The College of New Jersey

  22. Questions? Contact Information: SushmaMendu mendu@tcnj.edu Shawn Sivvy ssivvy@tcnj.edu The College of New Jersey

More Related