Cross-platform Batch Reports

1. Cross-platform Batch Reports Waldo Library Western Michigan University

2. Objectives • show an implementation of automated, cross-platform, hands-off report generation in the Voyager environment • provide you with the information to do this at your site

3. Bonus!!!!! Source code for BLOB access and a table+column schema listing included in the handout.

4. Brief Bio

5. Brief Bio • Working at WMU for 16 years now

6. Brief Bio • Working at WMU for 16 years now • Initially spent many years on the VAX

7. Brief Bio • Working at WMU for 16 years now • Initially spent many years on the VAX • Struggled for several years with the IBM mainframe and NOTIS

8. Brief Bio • Working at WMU for 16 years now • Initially spent many years on the VAX • Struggled for several years with the IBM mainframe and NOTIS • Supporting Voyager for close to 3 years

9. Brief Bio • Working at WMU for 16 years now • Initially spent many years on the VAX • Struggled for several years with the IBM mainframe and NOTIS • Supporting Voyager for close to 3 years • Picked up some Oracle, (PL/)SQL, Perl, and Unix along the way

10. Why We Did This

11. Why We Did This • initially no access to Access with ODBC stuff; had no clue

12. Why We Did This • initially no access to Access with ODBC stuff; had no clue • explored our Voyager Unix box, located SQL*PLUS

13. Why We Did This • So we created reports - • run them on the Voyager box, • transfer to PC • format and print in Word, • etc...

14. Why We Did This • So we created reports - • library liked them - • I got tired of:

15. Why We Did This • So we created reports - • library liked them - • I got tired of: • running reports • ftping them • formatting them • printing them • physically delivering them

16. Unix Cross-Platform Batch Reports Overview of Process (Handout page 4)

17. Unix Cross-Platform Batch Reports Overview of Process cron

18. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file

19. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script

20. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl

21. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running

22. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC

23. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC WinBatch

24. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC WinBatch script or .exe

25. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC WinBatch script or .exe use ftp to get the report pull it into Word format the report

26. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC WinBatch script or .exe use ftp to get the report pull it into Word format the report time/date parameters stored in each script

27. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC WinBatch script or .exe use ftp to get the report pull it into Word format the report copy report to destination on LAN time/date parameters stored in each script

28. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC print report to location WinBatch script or .exe use ftp to get the report pull it into Word format the report copy report to destination on LAN time/date parameters stored in each script

29. Unix Cross-Platform Batch Reports Overview of Process cron Time/date parameters in one crontab file script SQL, PL/SQL, Perl report One cron process running Batch PC print report to location WinBatch Library Administration Library Systems Media Resources Mono Acquisitions Serials script or .exe use ftp to get the report pull it into Word format the report copy report to destination on LAN time/date parameters stored in each script

30. When to Choose SQL, PL/SQL, or Perl • SQL

31. When to Choose SQL, PL/SQL, or Perl • SQL • invoked from within SQL*PLUS

32. When to Choose SQL, PL/SQL, or Perl • SQL • invoked from within SQL*PLUS • sufficient for most reports

33. When to Choose SQL, PL/SQL, or Perl • SQL • invoked from within SQL*PLUS • sufficient for most reports • easy control of page & line size, page formatting

34. When to Choose SQL, PL/SQL, or Perl • SQL • invoked from within SQL*PLUS • sufficient for most reports • easy control of page & line size, page formatting • takes control, does most of the work for you

35. When to Choose SQL, PL/SQL, or Perl • SQL • invoked from within SQL*PLUS • sufficient for most reports • easy control of page & line size, page formatting • takes control, does most of the work for you • output to screen or file

36. When to Choose SQL, PL/SQL, or Perl • SQL • sample of code… (Handout page 5) • output snippet below

37. When to Choose SQL, PL/SQL, or Perl identify what the report generates

38. When to Choose SQL, PL/SQL, or Perl include Word format settings

39. When to Choose SQL, PL/SQL, or Perl define output columns

40. When to Choose SQL, PL/SQL, or Perl these two columns are for sorting only

41. When to Choose SQL, PL/SQL, or Perl The call to FISCYRSETUP creates a temporary table, for the duration of this report, that contains the name and start date for the current and previous year. This is used in a number of reports to automatically reference fiscal year data.

42. When to Choose SQL, PL/SQL, or Perl Set up title and some report parameters. Want output in a file.

43. When to Choose SQL, PL/SQL, or Perl Run the query to create the report. Clean up when done.

44. When to Choose SQL, PL/SQL, or Perl • PL/SQL

45. When to Choose SQL, PL/SQL, or Perl • PL/SQL • invoked from within SQL*PLUS, or embedded in / combined with SQL

46. When to Choose SQL, PL/SQL, or Perl • PL/SQL • invoked from within SQL*PLUS, or embedded in / combined with SQL • provides SQL capabilities with programming language control added

47. When to Choose SQL, PL/SQL, or Perl • PL/SQL • invoked from within SQL*PLUS, or embedded in / combined with SQL • provides SQL capabilities with programming language control added • functionality sort of in-between SQL and Perl

48. When to Choose SQL, PL/SQL, or Perl • PL/SQL • output to screen or to screen

49. When to Choose SQL, PL/SQL, or Perl • PL/SQL • output to screen or to screen • Oracle allows output to file, but the Voyager installation does not enable this

50. When to Choose SQL, PL/SQL, or Perl • PL/SQL • output to screen or to screen • Oracle allows output to file, but the Voyager installation does not enable this • size limited to 1 million bytes output