Technology Shaping Education?

Dwight Fischer, the CIO of Plymouth State University wrote recently about how technology is shaping learning in higher ed.

In his article Technology in Classroom: Who’s Driving? Dwight, who also teaches online, makes some interesting observations on the effect of online resources such as Wikipedia on the learning process.

We as educators need to think of ways to engage students through the use of information. Encourage students to surf on ideas and concepts, have them offer up ideas to provocative questions. Point them to audio and visual resources online, then have them discuss their impressions. Gone is the sage on the stage; we are the guides on the side. Facilitate their learning and we will do students a much greater service in the long run.

I agree very strongly with what Dwight is saying here, but I do not feel it is necessarily any different now than it ever has been. The best educators I have worked with have never been the “sage on the stage” kind.

Weather the tools are books, slide rules, calculators, computers, the internet, or whatever may come next, the best educators have always been “the guides on the sides”. Those who facilitate learning and evaluate on understanding, not retention of information, will just see these as what they are, another tool in the toolbox.

For those educators intimidated by rapidly changing information age technology, take a step back and use this as an opportunity to learn with your students. You have more to offer than facts on a webpage… I hope.

“I know nothing excpt the fact of my ignorance.”

–Socrates

education, technology, online learning, online education, higher ed, college

Chronology of Oracle Blogs

OracleAndyC has compiled a short history of Oracle blogging.

It’s interesting to look at these in rough chronological order, and always nice to have a good list. Thanks Andy!

blogs, oracle, oracle database, database administration, dba, database

SQL: Find the Week Number in a Month

OracleAjama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month.

Basically if your week starts on a Sunday, anything leading up to the first Sunday would be week 1, then the first complete week, Sunday through Saturday would be week 2, then week 3, etc. If the month happens to start on a Sunday week 1 would be a complete week.

If you are unfamiliar with manipulating dates in Oracle, you may want to check out my other articles on Oracle, SQL, Dates and Timestamps and Performing Math on Oracle Dates.

Starting simple I worked out how to find the first day of the month. The TRUNC function will allow you to truncate a date to a certain precision, so I started by truncating the date to the month. Today is February 1, so since we’re using sysdate for some testing we coincidentally get back February 1.

SQL> select trunc(sysdate, 'MM') from dual;

TRUNC(SYS
---------
01-FEB-06

Now we have a starting point. From here I used the NEXT_DAY function to find the beginning of the first week of the month. This is found by looking at the first of the month and the 6 days leading up to it. Since next_day excludes the day it is calculating from we subtract 7 for the start date.

SQL > select next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;

NEXT_DAY(
---------
29-JAN-06

Now we have established the beginning of the first week in the month. The first week starts at the end of the previous month as it is only partially in February. With this date in mind we can now calculate how many days we are from then:

SQL> select sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual;

SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY')
------------------------------------------------
3.55945602

This tells us we are 3.559 days from the beginning of the first week in the month. To express that in weeks we simply divide by 7.

SQL> select (sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7 from dual;

(SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY'))/7
----------------------------------------------------
.508627646

This tells us that we are about .5 weeks from that first Sunday. We don’t care about the fractional part so we can use the other form of the TRUNC function to to truncate this to a whole number. That would put us in the 0th week of the month, but since we want to start counting at 1 we add 1 to the number.

SQL> select trunc(((sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7),0) + 1
from dual;

TRUNC(((SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY'))/7),0)+1
-----------------------------------------------------------------
1

This tells us accurately that right now we’re in the first week of the month. Now we want to try this logic out with some other dates. It’s easiest to do that if we just create a function for it.

CREATE OR REPLACE FUNCTION week_in_month
(check_date DATE DEFAULT sysdate, week_start CHAR DEFAULT 'Sunday')
RETURN number
IS
week_number NUMBER;
BEGIN
select trunc(((check_date - next_day(trunc(check_date, 'MM') - 7, week_start))/7),0) + 1 into week_number from dual;
RETURN week_number;
END;
/

Here I’ve created a function week_in_month to execute this SQL with two parameters. The first, check_date can be any Oracle date. The second parameter, week_start is the day that your week starts. This must be spelled out, like ‘Sunday’, ‘Monday’, etc. The function will return the week number from start of month.

If the check_date is not specified, the current date and time will be used. If the week_start is not specified we’ll default to Sunday.

Now let’s try our function without parameters.

SQL> select week_in_month from dual;

WEEK_IN_MONTH
-------------
1

The function correctly identifies that we’re in week 1 of the month by our original definition. Now we’ll look at a few other examples. Remember, where we have not specified a starting day our function will assume Sunday.

SQL> select week_in_month(to_date('01/29/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('01/29/06','MM/DD/YY'))
---------------------------------------------
5

Here we see that 1/29/06 was in the 5th week of January.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'))
---------------------------------------------
2

This shows that February 6 will be in the second week in February.

SQL> select week_in_month(to_date('02/06/06','MM/DD/YY'), 'Wednesday') from dual;

WEEK_IN_MONTH(TO_DATE('02/06/06','MM/DD/YY'),'WEDNESDAY')
---------------------------------------------------------
1

Here we have specified that our week starts on Wednesday, and based on that February 6th will be in the first week in February.

SQL> select week_in_month(to_date('04/30/06','MM/DD/YY')) from dual;

WEEK_IN_MONTH(TO_DATE('04/30/06','MM/DD/YY'))
---------------------------------------------
6

This is a somewhat rare instance, and it may look like there’s a problem with the logic, but because of how April of 2006 falls, the 30th would actually be in the 6th week of the month based on our criteria.

oracle, sql, dba, database administration, database development

(Re)Securing Oracle Application Server Control

OracleIf you’re running an Oracle Application Server 10g instance you are probably familiar with Oracle Enterprise Manager Application Server Control. If not, go back to the manual. This is not a how-to on setting it up or using it. If you want to know how to secure it and refresh the certificate when it expires, read on.

Application Server Control is installed with Application Server 10g and typically runs on a port like 1810. By default it uses the non-secure http protocol. Since your whole application server is controlled through this interface, you probably want to secure it. The instructions below will generate a self signed certificate and get your Application Server Control up and running with https.

As usual this post is written for Oracle Application Server 10g on UNIX. Always review the documentation for your release before trying any of these steps.

Securing Application Server Control

Oracle has provided a simple way to secure Application Server Control.

Note: If $ORACLE_HOME/bin is not in your path you will need to provide this path to emctl.

1. Connect to the command line on the application server and set all the appropriate environment variables for your application instance.

2. Run the command emctl stop iasconsole to stop Application Server Control.

3. Run the command emctl secure em to secure Application Server control. This will perform a few steps including generating a self-signed secure certificate.

4. Run emctl start iasconsole to start Application Server Control.

If all goes well you will now be able to connect to your Application Server Control instance on the same port as before but now with the https protocol. In most browsers you will need to specify ‘https://’ in the URL.

Depending on your browser settings you may get a warning when accessing the site that the secure certificate was not issued by a trusted company. That is normal with a self-signed certificate. You can either tell your browser to trust the certificate or simply disregard the warning when it appears.

Renewing the Certificate

By default the certificate created in the steps above will only be good for six months. Once the cert goes stale you will probably get a warning that the certificate date is invalid. You may additionally get some java errors like below.

When this happens you can simply re-secure Application Server Control with the same steps above. This will create a new certificate which will be valid for another six months.

Some Potential Problems

If the certificate has expired you will likely get a java error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: X509CertExpiredErr

If this happens simply re-secure Application Server Control with the instructions above.

Sometimes Application Server Control will not shut down properly and you may get an error like this:

IOException in sending Request :: javax.net.ssl.SSLException: SSL handshake failed: SSLIOClosedOverrideGoodbyeKiss

If this happens you will probably have to kill the enterprise manager process (look for a process called emagent) and re-secure again.

oracle, oracle application server, oracle security

Scheduling Jobs Using Oracle’s Job Queue

OracleOracle database offers a job queue for scheduling certain operations to happen routinely in a database. The functionality is similar to UNIX cron jobs with the primary difference being if your database is not running, the job will not attempt to run.

Scheduling is accomplished through the DBMS_JOB package which is provided by Oracle. While nearly anything can be scheduled, my rule of thumb is to use this only for things which happen exclusively within the database. For tasks which involve manipulating files at the operating system level I still prefer cron.

Oracle will start a coordinator job queue (CJQ0) process to handle scheduled jobs. The CJQ0 process will keep track of the schedule and start helper processes (J000 – J999) to execute the scheduled jbos.

This is being written for use with Oracle Database 9i, but will likely work in most of the recent revisions. As always, consult the documentation for your release before attempting any of this.

Setting the database up for job execution:

Before we can schedule job execution we need to make sure the database is set up to process jobs.

The first thing we need to do is check the number of job queue processes available to execute jobs. For that we check the job_queue_processes initialization parameter.

SQL> show parameter job_queue_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 100

This parameter should be set higher than the maximum number of simultaneous jobs you expect to run (the limit is 1000). Some jobs may require more than one process, so headroom is important.

If this parameter is set to 0 jobs will not be processed. If you wish to start processing jobs the parameter can be set dynamically with the ALTER SYSTEM command.

ALTER SYSTEM SET job_queue_processes = 100;

This command enables job queue processing by starting the CJQ0 process. Similarly, you can disable all job queue processing and stop the CJQ0 process by setting the parameter to 0.

ALTER SYSTEM SET job_queue_processes = 0;

Changes to the parameter with the ALTER SYSTEM command will only be in effect until the database is restarted. Make sure you change them in your init or spfile if you want the changes to be permanent.

Submitting a job to the queue

The procedure DBMS_JOB.SUBMIT is called to submit a new job to the queue. The procedure is called in the following format:

BEGIN
dbms_job.submit(JOB => :jobnumber, -- (this is a return variable, not a supplied number)
WHAT => 'code to execute',
NEXT_DATE => first_execution_date,
INTERVAL => next_execution_date);
commit;
END;
/

An example should make things a little more clear:

VARIABLE jobnumber number
BEGIN
DBMS_JOB.SUBMIT(JOB => :jobnumber,
WHAT => 'DBMS_STATS.GATHER_DATABASE_STATS(options => ''GATHER AUTO'');',
NEXT_DATE => to_date('11:30 01/23/06','HH24:MI MM/DD/YY'),
INTERVAL => 'SYSDATE + 1');
COMMIT;
END;
/
print jobnumber

JOBNUMBER
----------
21

Here we see a complete job submission. First we set up a variable to hold the job number which will be assigned automatically and returned by the procedure. We then begin a PL/SQL block and call the DBMS_JOB.SUBMIT command.

The JOB parameter will be the variable which will be populated with the job number. If you need to alter or delete a job in the future it will be easiest to find by job number.

The WHAT parameter is the code to be executed. This could be a simple statement or (as in our example) a call to another procedure or function. Note that the parameters for the procedure we’re calling must be in two single quotes so the single quotes are interpreted correctly.

The NEXT_DATE parameter specifies the first time this job should be run. This can be any valid Oracle date. Here we are telling it to run January 23 at 11:30 am.

If NEXT_DATE is not in the future you may run into problems, so it may be better to use a formula for NEXT_DATE. Anything that evaluates to a valid Oracle date is fair game.

Finally we specify INTERVAL, the way to calculate how often we want the job to run at. This is a date string which will be evaluated each time the job is run. Here SYSDATE + 1 will be evaluated to once a day. SYSDATE + .5 would cause the job to execute every 12 hours, or SYSDATE + 7 would cause it to run once a week. Any formula can be used here as long as it evaluates to a date.

The best way to assure a job will run at a specific time every day is to truncate the date and add a number of hours to it. I have explained this in greater detail in a separate article Getting a Specific Time of Day in Oracle.

If NULL is specified instead of an interval the job will be run once at the specified first execution time then be removed from the job queue.

The commit here is important to assure the newly created job will run. We then end the PL/SQL block and execute it. For future reference we print the jobnumber variable.

Now this job will be executed at 11:30am on 1/23/06 and every 24 hours after that.

Viewing the job queue

Oracle provides four useful views of the job queue.

DBA_JOBS lists information on all the jobs in the database.

ALL_JOBS has the same information as DBA_JOBS but only on jobs which are accessible tot he current user.

USER_JOBS again has the same job information, but will only list jobs owned by the current user.

DBA_JOBS_RUNNING contains information on all jobs currently running in the database. This view can be joined with the other views for detailed information on running jobs.

Running a job manually

While the point is to automate jobs, occasionally you may find you need to run a job manually. To do this you can call the RUN procedure. This is especially useful if you need to run a job which has been marked as broken.

BEGIN
DBMS_JOB.RUN(JOB => 21);
END;
/

Note: if you use DBMS_JOB.RUN to manually execute a job the value for NEXT_DATE will be updated based on the current date and time and your formula. This becomes important if you have a job running at, say 11:00 pm with an interval of SYSDATE + 1, but then you run it manually at 3:45pm, the next_date will be recalculated at that time and it will now be run at 3:45pm each day.

How to tell if a job has failed

When a database job fails an error will be written to the Oracle alert log with the error number of ORA-12012 and will include the job number which failed. Jobs could fail for a variety of reasons but most common are missing or altered objects or insufficient privileges to execute.

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;
/

The job will remain broken and will not be run until you either force it to run or mark it as not broken. When marking it as not broken you must also specify the next date for the job to run. The following will mark job 21 as not broken and have it execute at 11:00pm on January 23.

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => FALSE, NEXT_DATE => TO_DATE('23:00 01/23/06', 'HH24:MI MM/DD/YY'));
END;
/

Changing a job

You can update a job with the DBMS_JOB.CHANGE procedure. This procedure takes job number, code, next date and interval, in that order, as conditions. Whatever you don’t want to change can be passed as NULL.

With that in mind, the command to change just the interval would look something like this:

BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'SYSDATE + 7');
END;
/

This would change job number 21 to execute every 7 days. The previous value is discarded.

Removing a job

To remove a job from the job queue you will need the job number. If you’re not sure what it is you should be able to find it by querying one of the views listed above.

Once you have the job number, run the following command.

BEGIN
DBMS_JOB.REMOVE(JOB => 21);
END;
/

This will remove the job and it will not be executed again. You can only remove jobs that you own. If this is run while the job is executing it will not be interrupted, but will not be run again.

oracle, database, database administration, database development