Recently in General Category

Hello World! Now, let's see if I can actually publish this post!

The title of this blog probably should have been "Revenge of the HVAC Computers"! Over the last 2-3 months, it seems we've had problems with hacking activity and viruses on the three computers used by the Physical Plant to control various HVAC systems on campus.

The latest episode this week was with "Andy", the newest machine. Mike sent a file to Sophos for analysis, and they reported back that we had the trojan Troj/VBDown-F. Following their directions only made matters worse, as Sophos couldn't really remove the trojan. Finally, with Adam's help, we believe we have made some progress. He installed and ran the AVG anti-virus application. We also worked with a representative from the HVAC contractor to isolate things. No new problems were found. We also ended up upgrading the video driver on "Andy", as we couldn't connect via Remote Desktop. This, in fact, was our first indication of problems. As of now, we have "Andy" removed from the NT domain and we are watching things for a few days to see what happens. Adam has been a great help to us.

We've had two more Programmers' Meetings in the last couple of weeks. As a group, we've narrowed down our choices for Project Management applications to Intelisys and Teamwork. At yesterday's meeting, it was announced that we'll be going with Teamwork. I tested the Intelisys application and didn't like it, as it was very clunky. We also discussed the Banner Consolidation project and what we expect to take place over Thanksgiving weekend.

My office has moved from the "Corporate Headquarters" of UCS to the Physical Plant. My desk is now located in Mike Ross' office. We'll be able to spend more time working on DBA-related tasks, which will provide me with more learning opportunities. I'll still be working on programming projects and will continue the implementation of the Scheduler Application. My move is one part of the general reorganization taking place within UCS.

Since upgrading to PL/SQL Developer 7.1.1, I've had several instances where the application has crashed during what should have been a routine execute/compile of a package. Granted, the offending package is large (relatively speaking), but PL/SQL Developer has historically been more stable. Today's incident happened when I tried to save changes to a simple debugging HTP.PRINT statement. Hoping to solve the problem, I decided to downgrade PL/SQL Developer to a previous version, so I went back to 7.0.3. I noticed that version 7.1.2 is now available; while I was tempted to upgrade, I decided to go with a known quantity for now.

Anyone had this happen -- PL/SQL Developer, version 7.1.1.1339?

Friday afternoon...I made a change to one line of code within one of my packages, thinking this could result in a task needing to be "killed" by the DBA.

...Compiled the package and tested within the browser...
...Sure enough, the web page hung...
...Changing the offending line within PL/SQL back to the way it was and PL/SQL Developer hung too...

...Called DBA to have him kill the offending process...
...Process killed...
...Recompile; PL/SQL Developer still hung...
...Called DBA again; no offending processes needing to be killed...

...Backed up offending package; the backup compiled fine...
...Tried to drop the offending package within PL/SQL Developer; couldn't be dropped...
...Called DBA again to have him try to drop package from command-line; still couldn't be dropped...

Tuesday afternoon...decided, out of the blue, to try to drop offending package and see what would happen, again using PL/SQL Developer...
...Offending package successfully dropped.

...Go figure!

Since upgrading to IE7, I've seen more occurrences of the "page contains both secure and nonsecure items" warning message within my PL/SQL applications, specifically the login to the SIR application and the date pop up box that I'm calling in my form to enter appointment information within my Scheduler application.

I can now say that I know of two ways to "fix" this problem.

Option 1

Use the Firefox browser. This would probably be the first choice for most people.

Option 2

Make some settings changes within IE7; hopefully these won't cause unintended consequences elsewhere. I made the following changes:

1) Go to Tools > Internet Options > Security tab.
2) In the Current Security Zone, click the Custom Level button.
3) Scroll down to Miscellaneous.
4) The first setting under this heading is Access data sources across domains. Click Enable to stop the prompt.
5) A little further down, under the setting Display mixed content, click Enable.

Here's a link to the forum where I found this information:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=855412&SiteID=1

I have recently completed my first year in University Computing Services after transferring from Library and Media Services. This year has gone by very quickly! I have worked on the following projects.

Programming Projects

1) SIR Database. This has been a fairly large project. Working with Paul Lambert, we have made significant changes to the reports within the application and have made improvements to the user interface. I expect to have this project completed within a few weeks.

2) IRB Application Form. This was one of my first projects. This was a lengthy Oracle-based form used by the Institutional Review Board to manage project submissions. Although I made some cosmetic changes to the form, the project was reassigned to Summer Runyan. Summer has migrated this application to a PDF form, which will be much more efficient.

3) Online Employment Application. I was asked to resolve a problem with how the form processed the response to a question regarding the completion of a High School education or GED certificate. Applicants were submitting multiple answers to this question, resulting in run-time errors when Human Resources staff reviewed the completed form. I solved the problem by placing this question in a separate section of the form.

4) Calendar Application for College of Education. I made a copy of an existing Oracle-PL/SQL calendar application for a professor within this college.

5) Email Client. I am working on the development of an Oracle-based web email client that will provide an alternative to Communications Express. Although this application is in its mere infancy, I have developed a login to LDAP and the capability to send a very simple email message. I'm really just getting started with this project and have much, much more to do.

6) Migration of Oracle-based Applications from Wilbur to Aero. I migrated several PL/SQL applications used by Library and Media Services and worked with Mike Ross as part of our staff development to migrate several applications.

Staff Development

7) Database Administration and Tuning. Mike Ross and I have investigated the following topics related to Oracle database administration and tuning:

     â—? RMAN
     â—? SQL Trace Facility
     â—? TKPROF
     â—? Performance Tuning
     â—? Partitioning an Oracle Table
     â—? Oracle Enterprise Manager (OEM)
     â—? Oracle Intelligent Agent
     â—? Organization and Management of Oracle Tablespaces on Aero

8) Oracle Application Migrations. Mike Ross and I spent considerable time working on this project. This provided a great "hands-on" learning opportunity for me. As part of these migrations, we added a "redirect" (taking the user to the most current version of the applications, residing on Aero) and included a command to send Mike and I an email message as an alert when someone accessed an outdated application residing on Wilbur.

9) Investigation into the Use of the PHP Programming Language. Mike Ross and I decided to research this language to learn how it might be used as another interface for our Oracle database. We ran into some significant difficulties and eventually concluded, for now, that our best tool for Oracle development is indeed PL/SQL.

10) Investigation of Ubuntu Linux. I decided to investigate, on my own, the Ubuntu distribution of Linux. As I hadn't worked with Linux before, I decided that the Staff Development time would be a good opportunity to start learning. I borrowed a CPU from Joe, and Ron had a CD containing an ISO image for Ubuntu 5.10. I have learned quite a bit about the installation of Ubuntu, but haven't really used the OS beyond this step. I have encountered some technical difficulties, but have been able to resolve most of them so far.

11) Solaris Installation. Several of us worked on this project during the first few weeks of our Staff Development. Once the installation of Solaris was complete, our ultimate goal was to install Oracle. We had to abandon this project, however, as the machine was needed for another project.

Projects for Library and Media Services

12) Migration from Aviation to MASH Domain. Most of the work for this project was completed during July and August 2005. Working with Joe and Troy, we successfully migrated the department from Aviation to the new MASH domain. We had very few difficulties.

13) Maintenance of PastPerfect Application. This was probably the most significant casualty of the MASH migration. As this program is not always heavily used, the technical problems weren't discovered for several months. I reinstalled the software, on the I: drive and on several workstations, as this was the only way to configure the paths to the database to point to the correct location.

14) Ariel Upgrade to Version 4.1.1. This project had been in the planning for over a year before I attempted the upgrade. Ariel, originally developed by the Research Libraries Group (RLG), was acquired by Infotrieve, which then developed the major upgrade to version 4. I had major difficulties with the installation, as did many people at other sites. On my second major attempt to install the upgrade, I was successful. I attribute this success to my insistance, along with a little complaining, to Infotrieve for a revised, proven set of setup instructions.

15) Installation of Clio Application. This software is used as a management tool within the Interlibrary Loan unit. The installation of Clio was fairly straightforward.

16) Investigation into the Use of Thin Clients. Our goal was to implement Thin Clients to replace the staff workstations at the Checkout Desk. We ran into difficulties because of the use of barcode wands. Although we purchased a set of adapter cables for testing with the USB ports on the Thin Clients, there is more work to be done to configure the Windows session (on a Terminal Server) to work properly. We hope to make progress over the summer; I am working with Alex on this project.

I'm back to work today after taking a few days' vacation, so I'm getting back into a normal routine.

As this is the first Wednesday after the second Tuesday of the month, this means that new Windows updates have been released by Microsoft. As I still provide some support for Library and Media Services, this morning I took care of the updates on the three staff workstations located at the Library Checkout Desk. As these machines have security software installed that interferes with automatic updates, I have considered these as a special case and have taken the responsibility to coordinate the monthly updates by temporarily disabling the security software to allow the updates to be installed. In discussing this special need with Joe this morning, we have agreed to no longer use this particular security software. In the next day or so I will uninstall it from the three machines, solving the problem with automatic updates and making our lives a little easier.

As part of some reorganization of space and remodeling here at UCS, I've moved my workstation to a new area. Ron Swartzendruber and I now share the room that used to be Joe's workspace/tech support area. The lighting in this room is excellent.

I greatly appreciatated the help from Troy, Joe and Shaun in moving my workstation. I have my two LCD monitors mounted to my desk with height-adjustable swing-arm desk mounts for greater flexibility in moving the monitors to compensate for my low vision. With the move of my desk, these had to be disassembled then reassembled.

1) Integration of Library and Media Services computer support within University Computing Services.

The goal is to consolidate all computer support activities within UCS. The expectation here is that LMS staff and designated student workers will coordinate service requests with the UCS Service Request Desk. My only involvement will be to manage and provide support for tasks and projects that are unique to LMS. Some examples of these include Ariel and the OCLC Connexion interface.

2) For the long term, I will not be involved in support for Library and Media Services.

Exceptions will include areas where I have the most knowledge, due to my experience working in LMS. As part of this transition, an idea worth considering would be to have a UCS staff member accompany me when I’m called upon for support. This will be helpful for UCS staff as support for these projects becomes integrated into our operation.

Since the LMS migration to the MASH domain over the summer, my involvement in support issues has steadily decreased. This trend will continue.

3) Migration to the MASH Domain over the summer.

As we had planned this project for several months, it was agreed that I would be the person to coordinate the migration within LMS. In general, everything went well. We experienced few major unexpected issues. This project provided a great opportunity for me to work with UCS staff and student workers.

4) Support of public areas and labs at Library and Media Services.

Several staff members from LMS and UCS met back in December to discuss how our two departments provide support for the public areas within the Library building. As UCS staff members, we stressed the importance of reporting all operational problems and service requests to the UCS Service Request Desk. A major topic of discussion was printers and how we might expedite support when problems occur. We discussed the possibility of establishing printer pools within the Library building. A benefit would be that, if a particular printer is “down�, a near-by printer will automatically take over and process these jobs. Before introducing printer pooling within the Library, we would need to test and perfect the concept in other labs first.

Projects

IRB Application. In progress.

Despite a few run-time errors reported to me by the committee, the online form is working with some changes I have added for better functionality and readability. In discussing the current application with Mark Girod and Hank Bersani, they are considering the possibility of converting the application to a Word document for download. The form is currently over 15 pages in length and is somewhat time-consuming for applicants.

If the decision is made to proceed with the form as an Oracle-based application, I will develop the capability for committee members to view completed applications online. Because of my work with other Oracle projects the last few months, my skills have increased, and I would have greater confidence in completing the project.

SIR Database. In progress.

I am working on an interface to allow instructors to authenticate to this application through LDAP and view reports available for recent terms in which they taught courses. I have developed the capability to select a term, courses taught, and generate reports in HTML, ASCII and comma-delimited forms.

The largest remaining task for this project is authentication through LDAP. As LDAP authentication has been conquered by UCS programmers for other applications, I do not expect this to be a difficult task.

Calendar Application for Dana Ulveland. Project completed.

This project involved copying, or migrating, an existing calendar application in use by the College of Education, to a new Oracle schema. Once everything was copied to the new schema, I changed URLs and email address references as needed.

Online Employment Application for Human Resources. In progress.

An error was reported with this application when applicants click the browser’s Back button to return to the question dealing with completion of a High School diploma or GED certificate. This seems to be the only part of the application where this is happening. In working with Jan Carlson in the Human Resources office, we decided to add a new table, just for this question, and make some changes to the PL/SQL code to make the process more intuitive to applicants. I have made good progress with this and should have the application ready for testing by Human Resources by early next week.

Database Migration for Campus Public Safety from Wilbur to Aero. Project completed.

Mike Ross was the coordinator for this project. The migration went very smoothly and took much less time than we had expected. I was given the task of modifying PL/SQL code, residing on Wilbur, to redirect to code residing on Aero. The result is that, if the end-user should attempt to go to a page on Wilbur for whatever reason, they will be automatically taken to the appropriate page residing on Aero.

Database Migrations from Wilbur to Aero for Library and Media Services. Projects completed.

Applications included the Periodicals Title List, TILT and the Interlibrary Loan Form. These migrations all went well, as I had worked with Ron Swartzendruber to establish good documentation for the migration steps.

Coordination of Migration to MASH Domain for Library and Media Services Staff. Project completed.

I coordinated this project. As Library and Media Services has a somewhat complex networking environment, we put a great deal of planning into the migration. Even with the details involved, the migration went quite smoothly and with few unexpected problems.

Upgrade of Ariel Software for Interlibrary Loan. In progress.

For several months, we have been planning for a major upgrade to this software. As other libraries have upgraded and had significant technical problems, I made the decision to wait until a more stable version was released. Finally, about two weeks ago, the anticipated version was released and I decided to move forward. My installation attempt was not successful. After contacting technical support, I was given a new set of installation instructions. I hope to get back to this project within the next month or so, after I’ve had a chance to learn from other libraries as they complete the upgrade.

Staff Development Activities.

During the fall, Mike Ross and I worked together to begin to learn the PHP programming language and to develop the capability to query Oracle databases. Our long-term plan is to get back to this project and to hopefully consider the use of PHP as an additional tool for use in programming projects.

Training

Self-Paced Training

I have devoted time most days to the study of Oracle. I recently finished reading the book, Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein (O'Reilly Media Inc., 2002). The book covers the basics of program development with the PL/SQL language. The authors also explore, what I would consider, more advanced topics, such as PL/SQL Server Pages (PSP). PSPs allow the creation of web pages by embedding PL/SQL commands within HTML. For my overall knowledge of PL/SQL, this concept may be useful in the future. In addition, the authors present a detailed discussion of techniques for facilitating interaction and communication with the outside world. Topics discussed here include sending and receiving email within PL/SQL and the database, the use of the mail sender, fetching data from a remote site, and integration with other languages (including Java and C). As a side note, the book makes no mention of the PHP language. In general, a good book!

I am currently reading the book, Oracle9i PL/SQL Programming, by Scott Urman (Oracle Press series by McGraw-Hill/Osborne, 2002). The authors present a detailed discussion of the syntax of PL/SQL, including code examples that are very straightforward.

Training Courses

July 2005

"Oracle9i PL/SQL Basics", CD-based training

This was a self-paced course that provided an excellent introduction and overview of Oracle9i and the PL/SQL programming environment.

Topics covered:

â—? PL/SQL Engine: Functionality
â—? PL/SQL Program Constructs: Features
â—? PL/SQL Variables: Functions
â—? Scalar Datatypes: Introduction
â—? Scalar Variables: Declararing
â—? Nested PL/SQL Blocks: Variable Scoping
â—? Bind Variables
â—? Retrieving Data with a SELECT Statement
â—? Inserting, Updating, Deleting and Merging Data
â—? SQL Cursors: Features
â—? Controlling Transaction Points
â—? Performing Actions Using IF-THEN-ELSE
â—? CASE Expressions
â—? Boolean Conditions
â—? Repeating Statements Using Basic, FOR and WHILE Loops
â—? Declaring a PL/SQL Record
â—? Referencing a PL/SQL Record
â—? Declaring with %ROWTYPE
â—? INDEX BY Tables: Declaring, Referencing and Methods
â—? Cursors: Declaration Methods
â—? Controlling Explicit Cursors
â—? Declaring Explicit Cursors
â—? Retrieving Data from Explicit Cursors
â—? Cursors with Parameters
â—? FOR UPDATE Clause
â—? Updating the Latest Fetched Row
â—? Exceptions: Types
â—? Trapping Predefined, Nonpredefined and User-Defined Exceptions
â—? Trapping Exceptions: Functions
â—? Propagating Exceptions
â—? RAISE_APPLICATION_ERROR Procedure

Winter Term 2005, Chemeketa Community College

CS276A, "Introduction to Oracle: SQL", 4 credits

Course Description from the College Catalog:

"Offers an extensive introduction to data server technology. Examines the concepts of both relational and object relational databases and the Structured Query Language (SQL) programming language. Covers creating and maintaining database objects and storing, retrieving, and manipulating data. Also covers retrieving data by using advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. Includes writing SQL and SQL*Plus script files using the iSQL*Plus tool to generate report-like output."

The course provided an excellent introduction to SQL and served as a good prerequisite to PL/SQL programming.

We used the following texts:

Dawes, Chip, and Biju Thomas. OCA/OCP Introduction to Oracle9i SQL Study Guide. San Francisco: SYBEX, Inc., 2002.

Greenberg, Nancy, and Nathan Priya. Introduction to Oracle9i: SQL. Oracle Corporation, 2001.

Topics covered:

â—? Writing Basic SQL Select Statements
â—? Restricting and Sorting Data
â—? Single-Row Functions
â—? Displaying Data from Multiple Tables
â—? Aggregating Data Using Group Functions
â—? Subqueries
â—? Producing Readable Output with iSQL*Plus
â—? Manipulating Data
â—? Creating and Managing Tables
â—? Including Constraints
â—? Creating Views
â—? Other Database Objects
â—? Controlling User Access
â—? Using SET Operators
â—? Oracle9i Datetime Functions
â—? Enhancements to the GROUP BY Clause
â—? Advanced Subqueries
â—? Hierarchical Retrieval
â—? Oracle9i Extensions to DML and DDL Statements

I received an A for the course as well as a Certificate of Completion.

In this case, I’m referring to the use of cursors within PL/SQL. No, of course I’m not serious; cursors are an essential component of PL/SQL, and I have used them successfully within my applications.

My unsuccessful attempt yesterday in the implementation of a new cursor resulted in the associated query taking much, much longer than I had expected. Because of this, when I exited the browser in an attempt to “terminate� the application, only the browser was terminated; the application (session) was still running. I discovered this when I attempted to edit the PL/SQL package and remove the code containing the bad cursor. Because the session was still running, I could not recompile the package. At this point, I could edit the existing package and make a backup copy (saving it under a different name, which I did). I could not recompile or drop the offending package.

When I asked Travis and Troy about this, they explained what had happened. Troy quickly found the running session and terminated it. I could then edit the package, remove the code containing the bad cursor, and successfully recompile. Problem solved!

This was a good learning opportunity for me, so I thought it was worthy of a blog entry.

I have started reading the book, Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein (O’Reilly Media, Inc., 2002). The authors claim to have developed the book’s content with the new Oracle9i application developer in mind.

I believe the material covered here will serve as an excellent compliment to my Oracle and PL/SQL experience and training thus far, as well as a good reference manual for my ongoing development.

The book has already proven to be a good reference for me in working with the SIR application. As a specific example, I am developing a web form in which users will submit a CRN, term and year to generate specific course survey summaries. The simple example of an HTML form presented in the text was useful for me in fixing a bug in my code. I am also working on a PSP (PL/SQL Server Page), which I will use collect more information from the user.

July 2005

Oracle9i PL/SQL Basics, CD-based training

This was a self-paced course that provided an excellent introduction and overview of Oracle9i and the PL/SQL programming environment.

Topics covered:

â—? PL/SQL Engine: Functionality
â—? PL/SQL Program Constructs: Features
â—? PL/SQL Variables: Functions
â—? Scalar Datatypes: Introduction
â—? Scalar Variables: Declararing
â—? Nested PL/SQL Blocks: Variable Scoping
â—? Bind Variables
â—? Retrieving Data with a SELECT Statement
â—? Inserting, Updating, Deleting and Merging Data
â—? SQL Cursors: Features
â—? Controlling Transaction Points
â—? Performing Actions Using IF-THEN-ELSE
â—? CASE Expressions
â—? Boolean Conditions
â—? Repeating Statements Using Basic, FOR and WHILE Loops
â—? Declaring a PL/SQL Record
â—? Referencing a PL/SQL Record
â—? Declaring with %ROWTYPE
â—? INDEX BY Tables: Declaring, Referencing and Methods
â—? Cursors: Declaration Methods
â—? Controlling Explicit Cursors
â—? Declaring Explicit Cursors
â—? Retrieving Data from Explicit Cursors
â—? Cursors with Parameters
â—? FOR UPDATE Clause
â—? Updating the Latest Fetched Row
â—? Exceptions: Types
â—? Trapping Predefined, Nonpredefined and User-Defined Exceptions
â—? Trapping Exceptions: Functions
â—? Propagating Exceptions
â—? RAISE_APPLICATION_ERROR Procedure

Winter Term 2005, Chemeketa Community College

CS276A, Introduction to Oracle: SQL, 4 credits

Course Description from the College Catalog:

“Offers an extensive introduction to data server technology. Examines the concepts of both relational and object relational databases and the Structured Query Language (SQL) programming language. Covers creating and maintaining database objects and storing, retrieving, and manipulating data. Also covers retrieving data by using advanced techniques such as ROLLUP, CUBE, set operators, and hierarchical retrieval. Includes writing SQL and SQL*Plus script files using the iSQL*Plus tool to generate report-like output.�

The course provided an excellent introduction to SQL and served as a good prerequisite to PL/SQL programming.

We used the following texts:

Dawes, Chip, and Biju Thomas. OCA/OCP Introduction to Oracle9i SQL Study Guide. San Francisco: SYBEX, Inc., 2002.

Greenberg, Nancy, and Nathan Priya. Introduction to Oracle9i: SQL. Oracle Corporation, 2001.

Topics covered:

â—? Writing Basic SQL Select Statements
â—? Restricting and Sorting Data
â—? Single-Row Functions
â—? Displaying Data from Multiple Tables
â—? Aggregating Data Using Group Functions
â—? Subqueries
â—? Producing Readable Output with iSQL*Plus
â—? Manipulating Data
â—? Creating and Managing Tables
â—? Including Constraints
â—? Creating Views
â—? Other Database Objects
â—? Controlling User Access
â—? Using SET Operators
â—? Oracle9i Datetime Functions
â—? Enhancements to the GROUP BY Clause
â—? Advanced Subqueries
â—? Hierarchical Retrieval
â—? Oracle9i Extensions to DML and DDL Statements

I received an A for the course as well as a Certificate of Completion.

Effective July 1, 2005 I am transferring from Library and Media Services to University Computing Services. My duties will include programming in the Oracle9i PL/SQL development environment as well as coordination of computer support for Library and Media Services. I look forward to working with the UCS team!