42
Scheduling Background or Long Running Operations
For example, there might be some database cleanup to be run periodically. Another
example is when a user of a photo site decides to change the name of a tag associated with
images. The photo site application might initiate the name change, but return the user an
HTML page saying Your request is being processed and will soon complete. The user can
continue viewing photos without having to wait for the renaming process to complete. This
technique can improve user satisfaction. It can also free up an Apache server that would
otherwise be blocked, allowing it to be used by another page request.
The DBMS_SCHEDULER package can be used to start such background database tasks. It
has a lot of functionality, including allowing tasks to be repeated at intervals, or started when
events are received. It can also be used to invoke operating system programs. In Oracle 9i,
the DBMS_JOB package can be used instead of DBMS_SCHEDULER.
For the photo site example, create some data with the tag weeding:
Script 83: dschedinit.sql
connect system/systempwd
grant create job to hr;
connect hr/welcome
drop table tag_table;
create table tag_table (tag varchar2(20), photo_id number);
insert into tag_table values ('weeding', 2034);
insert into tag_table values ('weeding', 2035);
insert into tag_table values ('sanfrancisco', 4540);
commit;
To change the tag weeding to wedding, a procedure changetagname() can be created:
Script 84: dbsched.sql
create or replace procedure changetagname(old in varchar2, new in varchar2) as
b number;
begin
for i in 1..100000000 loop b := 1; end loop; -- simulate slow transaction
update tag_table set tag = new where tag = old;
commit;
end;
/
show errors
This script creates a sample table and the procedure to update tags. The procedure is
artificially slowed down to simulate a big, long running database operation.
The following PHP script uses an anonymous block to create a job calling changetagname().
Script 85: dsched.php
<?php
$c = oci_connect("hr", "welcome", "localhost/XE");
function doquery($c)
219
56
Using PL/SQL With OCI8
{
$s = oci_parse($c, "select tag from tag_table");
oci_execute($s);
oci_fetch_all($s, $res);
var_dump($res);
}
// Schedule a task to change a tag name from 'weeding' to 'wedding'
$stmt =
"begin
dbms_scheduler.create_job(
job_name
=> :jobname,
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'changetagname', // procedure to call
number_of_arguments => 2);
dbms_scheduler.set_job_argument_value (
job_name
=> :jobname,
argument_position
=> 1,
argument_value
=> :oldval);
dbms_scheduler.set_job_argument_value (
job_name
=> :jobname,
argument_position
=> 2,
argument_value
=> :newval);
dbms_scheduler.enable(:jobname);
end;";
$s = oci_parse($c, $stmt);
$jobname = uniqid('ut');
$oldval = 'weeding';
$newval = 'wedding';
oci_bind_by_name($s, ":jobname", $jobname);
oci_bind_by_name($s, ":oldval", $oldval);
oci_bind_by_name($s, ":newval", $newval);
oci_execute($s);
echo "<pre>Your request is being processed and will soon complete\n";
doquery($c); // gives old results
sleep(10);
echo "Your request has probably completed\n";
doquery($c); // gives new results
?>
The PHP call to the anonymous PL/SQL block returns quickly. The background PL/SQL call to
changetagname() will take several more seconds to complete (because of its for loop), so the
first doquery() output shows the original, incorrect tag values. Then, after PHP has given the
job time to conclude, the second doquery() call shows the updated values:
Your request is being processed and will soon complete
array(1) {
["TAG"]=>
220
47
Scheduling Background or Long Running Operations
array(3) {
[0]=>
string(7) "weeding"
[1]=>
string(7) "weeding"
[2]=>
string(12) "sanfrancisco"
}
}
Your request has probably completed
array(1) {
["TAG"]=>
array(3) {
[0]=>
string(7) "wedding"
[1]=>
string(7) "wedding"
[2]=>
string(12) "sanfrancisco"
}
}
Oracle Streams Advanced Queuing
Another way to initiate background tasks is to use Oracle Streams Advanced Queuing in a
producer-consumer message passing fashion. Oracle AQ is highly configurable. Messages can
queued by multiple producers. Different consumers can filter messages for them. Messages
can also be propagated to queues in other databases. Oracle AQ has PL/SQL, Java, C and
HTTPS interfaces. From PHP, the PL/SQL interface is used.
The following example simulates an application user registration system where the PHP
application queues each new user's street address. An external system can then fetch and
process that address. In real life the external system might mail a welcome letter, or do
further, slower validation on the address.
The SQL*Plus script qcreate.sql creates a new Oracle user demoqueue with permission to
create and use queues. A payload type for the address is created and a queue set up for this
payload.
Script 86: qcreate.sql
connect / as sysdba
drop user demoqueue cascade;
create user demoqueue identified by welcome;
grant connect, resource to demoqueue;
grant aq_administrator_role, aq_user_role to demoqueue;
grant execute on dbms_aq to demoqueue;
grant create type to demoqueue;
connect demoqueue/welcome@localhost/xe
-- The data we want to queue
create or replace type user_address_type as object (
221
47
Using PL/SQL With OCI8
name varchar2(10),
address varchar2(50)
);
/
begin
dbms_aqadm.create_queue_table(
queue_table => 'demoqueue.addr_queue_tab',
queue_payload_type => 'demoqueue.user_address_type');
end;
/
begin
dbms_aqadm.create_queue(
queue_name => 'demoqueue.addr_queue',
queue_table => 'demoqueue.addr_queue_tab');
end;
/
begin
dbms_aqadm.start_queue(
queue_name => 'demoqueue.addr_queue',
enqueue => true);
end;
/
The script qhelper.sql creates two helper functions to enqueue and dequeue messages.
Script 87: qhelper.sql
-- Set up enqueue/dequeue procedures
connect demoqueue/welcome@localhost/xe
create or replace procedure my_enq(
user_addr_p in user_address_type,
priority_p in number) as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
enq_id raw(16);
begin
dbms_aq.enqueue(queue_name => 'demoqueue.addr_queue',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => user_addr_p,
msgid => enq_id);
commit;
end;
/
show errors
create or replace procedure my_deq(
user_addr_p out user_address_type) as
dequeue_options dbms_aq.dequeue_options_t;
222
42
Oracle Streams Advanced Queuing
message_properties dbms_aq.message_properties_t;
enq_id raw(16);
begin
dbms_aq.dequeue(queue_name => 'demoqueue.addr_queue',
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => user_addr_p,
msgid => enq_id);
commit;
end;
/
show errors
The script newuser.php handles a new application user and queues a message containing
their address:
Script 88: newuser.php
<?php
$c = oci_connect("demoqueue", "welcome", "localhost/xe");
// The new application user details
$username = 'Fred';
$address = '500 Oracle Parkway';
// Enqueue the user information for further offline handling
$sql = "begin my_enq(user_address_type('$username', '$address'), 1); end;";
$s = oci_parse($c, $sql);
$r = oci_execute($s);
// Continue processing the new user in the current script
echo "Welcome $username\n";
?>
This executes an anonymous PL/SQL block to create and enqueue the address message. The
immediate output to the caller is simply the welcome message:
Welcome Fred
Once this PHP script is executed, any application can dequeue the new message at its leisure.
For example, the following SQL*Plus commands call the helper my_deq() dequeue function
and display the user details:
Script 89: showuser.sql
connect demoqueue/welcome@localhost/xe
set serveroutput on
declare
user_address user_address_type;
begin
my_deq(user_address);
223
46
Using PL/SQL With OCI8
dbms_output.put_line('Name : ' || user_address.name);
dbms_output.put_line('Address : ' || user_address.address);
end;
/
The output is:
Name : Fred
Address : 500 Oracle Parkway
If this dequeue operation is called without anything in the queue, it will block waiting for a
message until the queue wait time expires.
The PL/SQL API has much more functionality than shown in this overview. For example you
can enqueue an array of messages, or listen to more than one queue.
Queuing is highly configurable and scalable, providing a great way to distribute workload
for a web application. Oracle AQ is available in all editions of the database.
Reusing Procedures Written for MOD_PLSQL
Oracle's MOD_PLSQL gateway allows a Web browser to invoke a PL/SQL stored subprogram
through an HTTP listener. This is the interface used by Oracle Application Express. Existing
user-created PL/SQL procedures written for this gateway can be called from PHP using a
wrapper function. For example, consider a stored procedure for MOD_PLSQL that was created
in SQL*Plus:
Script 90: myowa.sql
create or replace procedure myowa as
begin
htp.htmlOpen;
htp.headOpen;
htp.title('Greeting Title');
htp.headClose;
htp.bodyOpen;
htp.header(1, 'Salutation Heading');
htp.p('Hello, world!');
htp.bodyClose;
htp.htmlClose;
end;
/
show errors
This generates HTML output to the gateway:
<HTML>
<HEAD>
<TITLE>Greeting Title</TITLE>
</HEAD>
<BODY>
<H1>Salutation Heading</H1>
Hello, world!
</BODY>
</HTML>
224
47
Reusing Procedures Written for MOD_PLSQL
To reuse the procedure directly in PHP, use HTP.GET_LINE in a mapping function to pipe the
output from the myowa.sql HTP calls:
Script 91: mymodplsql.sql
create or replace type modpsrow as table of varchar2(512);
/
show errors
create or replace function mymodplsql(proc varchar2) return modpsrow pipelined is
param_val owa.vc_arr;
line varchar2(256);
irows integer;
begin
owa.init_cgi_env(param_val);
htp.init;
execute immediate 'begin '||proc||'; end;';
loop
line := htp.get_line(irows);
exit when line is null;
pipe row (line);
end loop;
return;
end;
/
show errors
This is fundamentally similar to the previous pipelined examples.
In modpsrow() you can optionally use PARAM_VAL to set CGI values. See the definition of
init.cgi_env() in $ORACLE_HOME/rdbms/admin/privowa.sql for details.
In PHP, the new wrapper can be called like:
Script 92: mymodplsql.php
<?php
$c = oci_connect('hr', 'welcome', 'localhost/XE');
$stmt = oci_parse($c, 'select * from table(mymodplsql(:proc))');
$func = 'myowa';
oci_bind_by_name($stmt, ':proc', $func);
oci_execute($stmt);
$content = false;
while ($row = oci_fetch_array($stmt, OCI_ASSOC)) {
if ($content) {
print $row["COLUMN_VALUE"];
} else {
if ($row["COLUMN_VALUE"] == "\n")
$content = true;
else
header($row["COLUMN_VALUE"]);
}
}
225
48
Using PL/SQL With OCI8
?>
When called in a browser, the output is the expected rendition of the HTML fragment shown
earlier.
Easy PL/SQL Upgrades With Edition Based Redefinition
The Editioning feature of Oracle Database 11gR2 is very useful for web applications that aim
for no downtime when releasing enhanced versions of applications. It allows multiple versions
of PL/SQL objects with the same names to be used concurrently. This lets you upgrade stored
procedures and test new versions while production users are still accessing the original
versions. As well as allowing this safe way to upgrade, you can also use it for A/B testing,
where you evaluate the user response to a new version of your application. The application
versions that users don't appreciate can quickly be dropped.
The objects you can edition are:
●
synonyms
●
views
●
PL/SQL object types:
●
function
●
library
●
package and package body
●
procedure
●
trigger
●
type and type body
Tables themselves can't be editioned but there is support for moving and viewing data across
editions.
The following example shows how editioning can be used to upgrade a live PHP
application. The changes can be made and tested on the production database and then
enabled for all users with a one keyword change in the application.
As the user SYSTEM, allow the application user HR to use editions:
Script 93: ed1.sql
connect system/systempwd@localhost/XE
grant create any edition to hr;
alter user hr enable editions;
As the user HR create a table of employees for the application and create the stored function
that calculates the number of days of vacation an employee is eligible for. This function is
stored in the database so all Oracle applications can reuse the same logic:
Script 94: ed2.sql
connect hr/welcome@localhost/XE
drop table myemp;
226
Documents you may be interested
Documents you may be interested
