MacLochlainns Weblog

Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.

Here’s the schema-level PL/SQL function:

CREATE OR REPLACE FUNCTION like_boolean
( a NUMBER, b NUMBER ) RETURN NUMBER IS
 
  /* Declare default false return value. */
  lv_return_value NUMBER := 0;
 
BEGIN
 
  /* Compare numbers and return true for a match. */
  IF a = b THEN
    lv_return_value := 1;
  END IF;
 
  /* Return value. */
  RETURN lv_return_value;
 
END;
/

Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

<?php
  // Capture local variables when provided.
  $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1;
  $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1;
 
  // Open a connection.
  if(!$c = oci_connect("student","student","localhost/xe"))
  {
    die;
  }
  else
  {
 
    // Parse a statement.  
    $s = oci_parse($c,"BEGIN
                         :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                       END;");
 
    // Bind input and output values to the statement.
    oci_bind_by_name($s,":returnValue",$returnValue);
    oci_bind_by_name($s,":thingOne",$thingOne);
    oci_bind_by_name($s,":thingTwo",$thingTwo);
 
    // Execute the statement.
    if (@oci_execute($s))
    {
      // Print lead in string.
      print "[".$thingOne."] and [".$thingTwo."] ";  
      if ($returnValue)
        print "are equal.<br />";
      else
        print "aren't equal.<br />";
    }
 
    // Clean up resources.
    oci_close($c);
  }
?>

If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }

If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..

10
11
12
13

  $s = oci_parse($c,strip_special_characters(
                    "BEGIN
                       :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                     END;"));

Hope that answers the question and helps some folks.

A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.

Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.

The CSS sign-on form looks like this:

The portal page looks like this:

The add new user page looks like this:

The source files are as follows:

1. Database Authentication #1 – IdMgmt1.zip
2. Database Authentication #2 – IdMgmt2.zip
3. Database Authentication #3 – IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.

Hope this helps those who wanted the files.

We had a discussion today about how you can manage legacy code that you can’t change. For example, how do you access a PL/SQL function in PHP that returns a PL/SQL table of record structures? PL/SQL tables, index-by tables, or associate arrays are one in the same dependent on the release documentation. They’ve been available since Oracle 7.3 (roughly 15+ years).

You’ve a handful of solutions but I think the best is to wrap it in a Pipelined Table function (more on that in this older post). Here’s an example of such a package, wrapper function, and PHP program calling the wrapper function (command-line only PHP sample code).

Let’s say you have the following type of legacy package specification and body:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- Create the package specification.
CREATE OR REPLACE PACKAGE lib IS
  /* Define a record structure. */
  TYPE movie_title_record IS RECORD
  ( title    VARCHAR2(60)
  , subtitle VARCHAR2(60));
 
  /* Define an associative array of a package record structure. */
  TYPE movie_title_table IS TABLE OF movie_title_record
  INDEX BY BINARY_INTEGER;
 
  /* Define a prototype of a package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table;
 
END lib;
/
 
-- Create the package body.
CREATE OR REPLACE PACKAGE BODY lib IS
 
  /* Implement the package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table IS
 
    /* Declare a counter variable. */
    lv_counter INTEGER := 1;
 
    /* Declare an instance of the package nested table and initialize it. */
    lv_table   LIB.MOVIE_TITLE_TABLE := lib.movie_title_table();
 
    /* Define a parameterized cursor to read values from the ITEM table. */  
    CURSOR c ( cv_partial_title VARCHAR2 ) IS
      SELECT   i.item_title
      ,        i.item_subtitle
      FROM     item i
      WHERE    i.item_title LIKE '%'||cv_partial_title||'%';
 
  BEGIN
 
    /* Read the contents of the parameterized cursor. */
    FOR i IN c (pv_title) LOOP
 
      /* Extend space, assign values from the cursor to the record structure
         of the nested table, and increment counter. */
      lv_table.EXTEND;
      lv_table(lv_counter) := i;
      lv_counter := lv_counter + 1;
 
    END LOOP;
 
    /* Return PL/SQL-scope nested table. */
    RETURN lv_table;
 
  END get_movie;
 
END lib;
/

You can wrap the lib package’s get_movie function with a schema-level function provided you convert the older associative array to a PL/SQL-scope nested table. You can do that in two steps. The first requires that you create a wrapper package specification, like the following example. The second step requires you to write a conversion wrapper function, shown later.

The table is dependent on the named record structure from the lib, and as such the packages are now tightly coupled. This is not uncommon when you can’t fix a vendors legacy code set.

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE wlib IS
 
  /* Define a nested table of a package record structure. */
  TYPE movie_title_table IS TABLE OF lib.movie_title_record;
 
END wlib;
/

The wrapper function also converts the Oracle Database 7.3 forward data type to an Oracle Database 8.0.3 data type, and then pipes it into a SQL aggregate table. SQL aggregate tables are valid call parameters in the SQL-context. The TABLE function converts the collection of record structures into an inline view or derived table, as you’ll see a little farther along.

You should note that the return type of this function differs from the original package-level get_movie function. The former uses an associative array defined in the lib, while the latter uses a nested table defined in the wlib package.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE OR REPLACE FUNCTION get_movie
( pv_title VARCHAR2 ) RETURN wlib.movie_title_table
PIPELINED IS
 
  /* Define a PL/SQL-scope associative array (Available Oracle 7.3). */
  lv_table_source LIB.MOVIE_TITLE_TABLE;
 
  /* Define a PL/SQL-scope nested table (Available Oracle 8.0.3). */
  lv_table_target WLIB.MOVIE_TITLE_TABLE := wlib.movie_title_table();
 
BEGIN
 
  /* Assign the results of a PL/SQL-scope call to the package function. */
  lv_table_source := lib.get_movie(dbms_assert.simple_sql_name(pv_title));
 
  /* Read the contents of the PL/SQL-scope nested table into a PIPE ROW. */    
  FOR i IN 1..lv_table_source.COUNT LOOP
    lv_table_target.EXTEND;
    lv_table_target(i) := lv_table_source(i);
    PIPE ROW(lv_table_target(i));
  END LOOP;
 
END;
/

You can test this exclusively in SQL*Plus with the following formatting and query. The TABLE function translates the returned array into an inline view or derived table for processing.

-- Format columns for display with SQL*Plus.
COLUMN title    FORMAT A20 HEADING "Movie Title"
COLUMN subtitle FORMAT A20 HEADING "Movie Subtilte"
 
-- Select the contents of the schema-level function in a SQL-context.
SELECT *
FROM   TABLE(get_movie('Star'));

If you’re using my sample code from the Oracle Database 11g PL/SQL Programming book, you should see:

Movie Title          Movie Subtilte
-------------------- --------------------
Star Wars I          Phantom Menace
Star Wars II         Attack of the Clones
Star Wars II         Attack of the Clones
Star Wars III        Revenge of the Sith

The following is a simple command-line PHP program that calls the wrapper function. It calls the wrapper function, which calls the lib.get_movie() function, and it converts the PL/SQL data type from an associative array (Oracle 7.3+ data type) to a nested table (Oracle 8.0.3+ data type). The nested table is defined in the wlib library, which supplements rather than replaces the original lib library.

The last thing that the wrapper function does is transform the associative array result into a nested table before placing it in the pipe (this process is known as a Pipelined Table function). Only nested table and varray data types may be piped into a SQL aggregate table. Then, the external programming language can manage the output as if it were a query.

Here’s the PHP program:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

<?php
  // Connect to the database.
  if ($c = oci_connect("student","student","localhost/orcl"))
  {
    // Parsing a statement into a digestable SQL statement.
    $s = oci_parse($c,"SELECT * FROM TABLE(get_movie('Star'))");
 
  // Execute the parsed statement.
  oci_execute($s,OCI_DEFAULT);
 
  // Store control variable for the number of columns returned by the statement.
  $columns = oci_num_fields($s);
 
  // Find the number of columns, loop through them, and write their column name metadata.
  for ($i = 1; $i <= $columns; $i++) {
 
    // Print the column names, also known as field names.
    print oci_field_name($s,$i);
 
    // Define a variable.
    $line;
 
    /* Check whether a variable is declared and pad it.
     *   The numeric literal is for my convenience because the maximum size
     *   of possible returns is known. In a real situation, you'd use the 
     *   following str_pad() call:
     *
     *   str_pad($temp,oci_field_size($s,$i),"-") */
    if (!isset($line)) 
      $line .= str_pad($temp,15,"-");
    else
      $line .= " ".str_pad($temp,20,"-");
 
    /* One of the values requires a double tab to line up, otherwise this
       practice is unwise. */    
    if ($i < $columns)
      print "\t\t"; }
 
  // Print line return for the header and a line to mimic Oracle SQL*Plus output.
  print "\n";
  print $line .= "\n";
 
  // Process row-by-row data returned when data is returned.
  while (oci_fetch($s))
  {
    // Process column-by-column data returned for each row.
    for ($i = 1; $i <= $columns; $i++) {
      print oci_result($s,$i);
 
    if ($i < $columns) print "\t"; }
 
      // Print line return for the row of data returned.
    print "\n";
  }
 
  // Release resources.
  oci_close($c);
 
  // Explicitly free any resources.
  oci_free_statement($s);
  oci_free_cursor($c);
  }
?>

Assuming you call this callOracle.php, you can call it from the command-line with this syntax:

php callOracle.php

It prints, like it would in SQL*Plus:

TITLE           SUBTITLE
--------------- --------------------
Star Wars I     Phantom Menace
Star Wars II    Attack of the Clones
Star Wars II    Attack of the Clones
Star Wars III   Revenge of the Sith

Hope that helps those working with legacy Oracle code.

Sometimes I’m amazed at things that come up. A student wondered why the sequences were incrementing by two when they’re defined to increment by one. It turns out that they were using Oracle APEX to create SQL statements to build a table, constraints, and a auto-numbering sequence trigger. Before executing the code, they’d copy it into their re-runnable script that created their schema.

Here’s an example of code that was generated by APEX for a table:

-- Create the table.
CREATE TABLE onesy
( onesy_id NUMBER
, onesy_text VARCHAR2(20));
 
-- Add the primary key constraint.
ALTER TABLE onesy ADD CONSTRAINT onesy_seq PRIMARY KEY (onesy_id);
 
-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

This works in APEX because it doesn’t create forms that call onesy_seq.NEXTVAL but they did create that logic in their forms. The INSERT statement would look like:

INSERT INTO onesy VALUES (onesy_seq.NEXTVAL, 'One');

Therefore, the INSERT statement incremented the trigger by one and the database trigger incremented it by one. The result is that sequences two-step, which isn’t effective or the desired behavior.

After I explained the two-step problem, they asked if they could only call the trigger when the primary key value was null. While they could do that like this:

1
2
3
4
5
6
7
8
9

-- Add a database trigger.
CREATE OR REPLACE TRIGGER onesy_trg 
BEFORE INSERT ON onesy
FOR EACH ROW
WHEN (NEW.one_id IS NULL)
BEGIN
  :NEW.onesy_id := onesy_s1.NEXTVAL;
END;
/

The problem is that this type of trigger doesn’t stop other possible problems. While it prevents two-stepping the sequence, it doesn’t prevent two other errors.

One possible error that isn’t managed in this scenario is the use of numeric literals beyond the next value of the sequence. It writes the row but eventually the sequence catches up to the higher value and a production insert would fail. It would raise the following exception.

INSERT INTO onesy (onesy_text) VALUES ('Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

Another possible error can occur when you use a bulk insert operation. Assuming you’re inserting 500 rows at a go, you query the maximum value of the onesy_id column and then create an array of 500 numbers. Then, you perform the bulk INSERT statement. The next call to the trigger would raise another ORA-00001 unique constraint error.

Yes, you could lock the table before you perform the bulk operation. After the bulk operation you would drop and recreate the sequence with a new value equal to the maximum value in the column, and unlock the table. This limits concurrency of operation. You could treat these bulk operations as off-line transactions (batch processing) and it would work nicely.

You could also implement a policy that no bulk operations provide generated column values that link to a sequence. Beyond it’s impracticality to manage, that type of restriction does limit the benefit of bulk operations.

The students wanted a solution. So, here’s my take on a trigger that prevents collision with values above the next sequence value. It assumes that bulk operations will be performed as batch processing where you can disable this trigger.

This trigger disallows numeric literals, logs any attempts to use them, and stops processing when an INSERT statement tries to use anything other than the .NEXTVAL of the sequence. It will only work in an Oracle Database 11g database because the context of using a sequence_name.CURRVAL in a comparison isn’t supported in prior releases. The onesy table is renamed the one table in the example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/

Since anonymous transaction triggers are tricky, it’s important to note that the message writing requires two commits. One before raising the exception when the .CURRVAL is in session scope and another in the exception handler before raising the error. If you forget those COMMIT statements, this is a sample of the error stack:

INSERT INTO one VALUES (one_s1.nextval,'Six')
                        *
ERROR at line 1:
ORA-06519: active autonomous TRANSACTION detected AND rolled back
ORA-06512: at "STUDENT.ONE_T1", line 31
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

The trigger raises the following type of exceptions for an offending INSERT statement. The first occurs when the sequence is valid in the session scope, like:

DECLARE
*
ERROR at line 1:
ORA-20001: NOT a SEQUENCE generated VALUE [1].
ORA-06512: at "STUDENT.ONE_T1", line 48
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'
ORA-06512: at line 15

The second occurs when the sequence isn’t valid in the session scope.

INSERT INTO one VALUES (401,'Nine')
            *
ERROR at line 1:
ORA-20002: ID provided BY calling scope IS NOT NEXT SEQUENCE VALUE [401][Nine].
ORA-06512: at "STUDENT.ONE_T1", line 24
ORA-04088: error during execution OF TRIGGER 'STUDENT.ONE_T1'

A value that’s below the current high-watermark of the sequence raises a unique constraint, like this:

INSERT INTO one VALUES (1,'Eight')
*
ERROR at line 1:
ORA-00001: UNIQUE CONSTRAINT (STUDENT.PK_ONE) violated

The following is a script with all the necessary code components to test the example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('ONE','ONE_S1','MSG','MSG_S1')) LOOP
    IF i.object_name = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';  
    ELSE
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;  
    END IF;
  END LOOP;
END;
/
 
-- Create a table ONE.
CREATE TABLE one (one_id NUMBER CONSTRAINT pk_one PRIMARY KEY, one_text VARCHAR2(20));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE one_s1;
 
-- Create a table MSG.
CREATE TABLE msg (msg_id NUMBER, msg_text VARCHAR2(80));
 
-- Create a sequence for table ONE that starts with 1 and increments by 1.
CREATE SEQUENCE msg_s1;
 
-- Create or replace trigger.
CREATE OR REPLACE TRIGGER one_t1 
BEFORE INSERT ON one
FOR EACH ROW
DECLARE
 
  /* Define an autonomous transaction scope to the trigger. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
  /* Declare a local exception raised when a .CURRVAL pseudo column for a sequence
     is called before a .NEXTVAL for the same sequence in the same session. */
  no_sequence_in_scope EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_sequence_in_scope,-08002);
 
BEGIN
 
  /* Check if surrogate key is provided and the sequence not out of transaction scope. */
  IF :NEW.one_id IS NOT NULL AND NOT :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Write message when sequence value is a numeric literal not a sequence
       generated value but a one_s1.NEXTVAL was previously called in the session.
       Commit after write or information is lost because it throws an user-defined
       exception. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID value less or greater than .NEXTVAL ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing exception. */
    RAISE_APPLICATION_ERROR(-20002,'ID provided by calling scope is not next sequence value ['||:NEW.one_id||']['||:NEW.one_text||'].');
 
  ELSIF :NEW.one_id = one_s1.CURRVAL THEN
 
    /* Do nothing, calling scope is correct with a one_s1.NEXTVAL sequence call. */
    NULL;
 
  ELSE
 
    /* Increment sequence and assign a value when one isn't provided, like a NULL value. */
    :NEW.one_id := one_s1.NEXTVAL;
 
  END IF;
 
EXCEPTION
 
  /* Handle a no sequence in scope error. */
  WHEN no_sequence_in_scope THEN
 
    /* Write and commit log message for error. */
    INSERT INTO msg VALUES (msg_s1.NEXTVAL,'ID provided by calling scope is invalid ['||:NEW.one_id||']['||:NEW.one_text||'].');
    COMMIT;
 
    /* Stop processing by throwing an exception. */
    RAISE_APPLICATION_ERROR(-20001,'Not a sequence generated value ['||:NEW.one_id||'].');
 
END;
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_index AS TABLE OF VARCHAR2(20);
/
 
-- Define a schema collection of strings.
CREATE OR REPLACE TYPE one_list AS TABLE OF VARCHAR2(20);
/
 
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_index ONE_INDEX := one_index();
  lv_list  ONE_LIST := one_list('One','Two','Three','Four','Five');
BEGIN
 
  -- Initialize the collection of the ONE_INDEX collection.
  lv_index.EXTEND(lv_list.COUNT);
  FOR i IN lv_list.FIRST..lv_list.LAST LOOP
    dbms_output.put_line('['||i||']['||lv_list(i)||']');
    lv_index(i) := i;
  END LOOP;
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (lv_index(i),lv_list(i));
 
END;
/
 
-- Testing INSERT statements until sequence value increments beyond bulk insert values.
INSERT INTO one VALUES (6,'Six');
INSERT INTO one VALUES (one_s1.nextval,'Seven');
INSERT INTO one VALUES (1,'Eight');
INSERT INTO one VALUES (401,'Nine');
INSERT INTO one VALUES (NULL,'Ten');
 
COLUMN msg_id   FORMAT 999 HEADING "Msg|ID"
COLUMN msg_text FORMAT A72 HEADING "Msg Text"
 
-- 
SELECT * FROM one;
SELECT * FROM msg;
 
-- Anonymous block bulk testing program.
DECLARE
  -- Declare two scalar collection variables.
  lv_list  ONE_LIST := one_list('Twenty-one','Twenty-two','Twenty-three','Twenty-four','Twenty-five');
BEGIN
 
  -- Perform a trivial example of a bulk insert.  
  FORALL i IN lv_list.FIRST..lv_list.LAST
    INSERT INTO one VALUES (NULL,lv_list(i));
 
END;
/
 
-- Query tables.
SELECT * FROM one;
SELECT * FROM msg;

If I’ve fat fingered any typing or made logical errors, please let me know.

The last day of Oracle Open World 2010. My focus today was on attending OracleDevelop and JavaOne.

The weather picked up today and it was a nice warm Indian Summer day. There were lots of tourists out too. The photo is taken of the building where you board the Powell Street cable car. There was a lot of walking with the way the events are dispersed among the Moscone South, Moscone West, Marriott Hotel, Westin Hotel, Hilton Hotel, W Hotel, and Hotel Nikko. I can’t quite remember how many times I walked back and forth across the 6 blocks between the Moscone centers and Union Square hotels. I can tell you that we went only twice to Mel’s Drive-in.

It was amazing to see how quickly the various conference expedition centers shutdown, packed up, and had their materials shipped out. We had to step over all the plywood that protected tiles and carpets to attend events.

Day 4 also brought a smaller audience for venues. I’m not quite sure if they left earlier or slept in because they were out too late last night attending the Wednesday night event on Treasure Island. The reduced number of attendees was great for those of us who remained. You can see how few attended the .NET/Oracle hands-on lab in the Hilton, which made finding a nice spot easy. You can find the .NET/Oracle hands-on tutorial materials on the Oracle Technical site.

If you opt to use the tutorials, you may benefit from these hints. You should be able to avoid some of the issues that I ran into when working through the open labs. First, you should expand the Microsoft Studio to full screen. Second, you should look for context pop-ups attached to a small arrow at the top right corner of grids, et cetera. Lastly, there are a few small mistakes that you’ll need to work through. Look at the errors as an opportunity to think and experiment and they’re great basic .NET/Oracle tutorials.

Oracle Open World 2010 is done. Time to review the keynotes for those things that I missed while listening to them, and consider the new role of Tuxedo in the life of Oracle’s product stack. It’s also time to download and play with the MySQL 5.5 candidate release; and it’s time to kick off my shoes, put up my feet, and play with the technology again.

Tomorrow I turn my fate over to the airlines, and hope to arrive home on schedule.

Oracle Press authors met at the bookstore to sign books for an hour this morning and visit with our editors (shown at left). Then, we went off to the OTN Lounge and JavaOne at the Hilton.

It’s interesting to see different Java solution sets, especially on embedded devices. I also got a good look at the Vaadin server-side AJAX framework. It was interesting. The QuickConnect AJAX framework isn’t represented at the conference, but it’s a free open source project that may interest some.

Java’s dominance in the Oracle product stack is clear. Java is the crux of the SOA architecture for Oracle’s middleware solutions. Interestingly, I’ve met a number who are new to Java. A number of the vendors here are looking for skilled Java programmers, which leads me to see a lot of opportunity for developers.

I snagged a copy of Oracle Essbase and Oracle OLAP today. Oracle Essbase is part of Oracle Fusion Middleware 11g. I’ve began reading the book this evening. It appears a good starting place for those exploring Oracle Essbase and Oracle OLAP solution space.

This was a day with a busy schedule because of the publisher’s seminar. It’s where the authors, like me, learn about Oracle products and market focus. Andy Mendolsohn went over the Oracle Database 11g R2, and other VPs presented BI/ERP solutions and the effect of Oracle’s Essbase Plus (previously known as Hyperion), Java’s position and direction, Solaris’ position and direction, and the Fusion Applications. A summary of the highlights I noted follows:

• The Oracle Database 11g R2 presentation explained the idea of quarterly patch set updates (PSUs), advanced compression, the new Oracle Database Firewall, OCFS (Oracle Clustered File System), and the Exadata Server. Three quarterly PSUs, then a point release is a change but a welcome one.
• Advanced compression qualified three subgroups: (a) A 3 times compression for OLTP systems; (b) A 10 times compression for data warehousing, and (c) a 15 to 50 times compression for archive data. If we apply this with the capital cost figure of 40% for storage as valid, compression may substantially reduce costs.
• They shared Gartner statistics that 82% of Fortune 500 companies use Oracle BI/ERP solutions was very interesting. The idea that the largest customer deploys an Oracle BI/ERP solution to 1 million users turns the demarcation between traditional data warehouse explorers and farmers into a historical footnote. They positioned Oracle BI EE Plus as targeted to the development of dashboard and BI Publisher (formally XML Publisher). Oracle Essbase Plus for Model-OLAP (Online Analytical Processing – focused on resolving the discrepancies between R-OLAP (Relational-OLAP) and M-OLAP (Model-OLAP). Oracle Scorecard and Strategy Management tools are reserved to planning Long-Range Objectivs (LROs), Short-Range Objectives (SROs), and Key Indicators (KPIs). The last requires a carefully modeling of the objective needs of the business and data model capability.
• The Exadata server now comes in two types, the X2-2 and X2-8. The first may be a quarter, half, or full Exadata server while the latter is always a full Exadata server. The X2- has 128 cores, 2 TB of memory, a Linux/Solaris 10 GB Ethernet connection, 14 storage servers with 6 core CPU in each storage server.
• Oracle’s commitment to Java is very solid but a question on NetBeans leaves one to believe that it isn’t part of Oracle’s solution space and it may not see much if any evolutionary change. They said that NetBeans would be supported to help external Open Source developers. My guess is that we should plan to migrate NetBeans or bear the cost of owning solutions with a higher integration cost.
• Solaris has come through the merger with new strength and a commitment to four goals that I caught: availability, scalability, and efficiency, and security. They also committed to make Solaris fully virtualizable, which will more effectively support private clouds. You may also note that Oracle has improved efficiency of MySQL 5.5 on the Solaris platform.
• Fusion Middleware Applications focus on: (a) Best Practice – leveraging the best in class of applications from the Oracle eBusiness Suite, PeopleSoft, JD Edwards, and Siebel; (b) Re-inventing User Experience – A role-based user interface, embedded decision support, and pervasive collaboration; (c) Cloud – Support for both private and public clouds; (d) Platform – Standard-based, configurable, adaptive, and secure Fusion middleware. Customers will have three options for the future. They may continue the current path and upgrade eBusiness Suite to eBusiness Suite, et cetera. Alternatively, they can adopt a co-existence strategy that lets them mix and match components form multiple suites. Lastly, they may embrace the complete suite as a whole.

After the Publisher’s Seminar, we went to the Nikko for Oracle Develop. On the way, we had to cross Powell Street. I snapped a photo of John Harper, co-author of Oracle Database 11g Programming Workbook, with the Cable Car. The Nikko’s door is on the right, and the sessions were on the third floor.

After one session, we went back to the Moscone South Exhibition hall to snag a copy of Guy Harrison’s Oracle Performance Survival Guide: A Systematic Approach to Database Optimization. It’s the fresh version of his prior SQL Tuning books. I’d already bought one two months ago, but I couldn’t resist a signed copy for the office when it was free. I recommend the book as worthwhile and have promised Guy to write a review when I get home.

At the conclusion of the day, John and I went to the Oracle Press party to meet other authors. Ric Niemiec, founder of TUSC and author of Oracle Database 10g Performance Tuning: Tips and Techniques, and we took a photo. He’s also mentioned that the next edition covering Oracle Database 11g will include coverage of the Exadata Server. It’s awesome that he gets to write after the terminal release of the database.

I started the day at JavaOne. It’s at the Hilton on Mason Street. I attended a couple sessions on Java. Then, I went to the JavaDEMO Grounds to check things out rather than catch a sandwich.

There were a few interesting venues but I found the JBoss presentation the most useful on Seam 3. The presentation was worth the time, and the demonstration using the technology at the end was effective for a couple reasons. While the presenter’s environment wasn’t ready, he was able to fix it while working through his discussion of the technical stack. Also, the use of Seam 3 was first class. I’d recommend you stop by if you have a chance to see it.

After seeing the JavaDEMO Grounds, I went back to the Marriott to attend the Data Integration hands-on lab with Data Integrator and the Database Performance Diagnostics and Tuning presentations.

Afterward, a friend and I went to Moscone South to the Exhibition Hall. There we found the air hockey table and other game room. Only one here this year. We finished up by hitting the Moscone South Exhibition Hall. Saw some interesting hardware demos and that’s a wrap for Day 1 at Oracle Open World 2010. Actually, my second day because MySQL Sunday counts in my book.

This week I attended the first ACM conference on Bioinformatics and Computational Biology in Niagara Falls, NY. The next conference is in Rome next January. It was interesting to note who’s using what technology in their research.

Here’s a breakdown:

• Databases: MySQL is the de facto winner for research. Oracle for clinical systems, mostly Oracle 10g implementations. That means moving data between the two is a critical skill. Specifically, exporting data from Oracle and importing it into MySQL. Oracle was criticized for being a DBA-preserve and unfriendly to development. When I probed this trend, it seemed to point to DBAs over managing sandbox instances at companies with site licenses. Microsoft SQL Server didn’t find a lot of popularity in the research community.
• Programming Skills: C#, C++, Objective-C and PHP were high on the list. C# to import data into Microsoft SharePoint and develop Windows SmartPhones. C++ to extend MySQL. Objective-C to develop iPhone and iPad applications. PHP to build applications to manage studies and facilitate input, but there were a couple using Perl (not many).
• Collaboration Tools: Microsoft SharePoint won handily. It’s made a home in the clinical and research communities.

Overall, they want programmers who understand biology and chemistry. They’d like knowledge through Medical Microbiology and Introductory Biochemistry, and they want strong math and statistical knowledge in their programming staff. They like Scrum development frameworks. They seem to emphasize a chief engineering team, which means the developers get maximum face-time with the domain experts. The developers also have to speak and walk the talk of science to be very successful.

As to Niagara Falls, I’m glad that I took my passport. The Canadian side is where I spent most of my extra time and money. It has the best views of the falls, the best food, and ambiance. Goat Island and the Cave of the Winds are the only two features I really liked on the U.S. side of Niagara Falls. The U.S. side is dreary unless you like gambling in the Seneca Niagara Casino & Hotel. Since I’m originally from Nevada, I never entered it to check it out. Technically, when you step on the casino property you enter the Seneca Nation of New York. The New York state government in Albany really needs to address the imbalance or they’ll continue to see Canada score the preponderance of tourist dollars.

Somebody posted a comment inquiring about the release of Oracle 11g XE. They felt it had been delayed. As far as I know, the last word on that came from Andy Mendelsohn last October, as published in this InfoWorld article.

The Oracle Database 11g XE is released and the download link is here.

When Andy Mendelsohn originally announced Oracle 11g XE in an interview with the NY Times, he clearly said it would be based on the terminal release of Oracle 11g. I also suspect it’ll include APEX 4.0, which was recently released. Maybe we’ll see the release at or near Oracle Open World 2010.

I don’t endorse the negative remarks in the InfoWorld article about why Oracle 11g XE isn’t released. While I have no direct knowledge of the forthcoming release, I think there’s an alternative explanation for any delay. Based on my involvement with the Oracle 10g XE release, I believe Oracle wants a rock solid starter version. At least, that was a major concern when they released Oracle 10g XE.