MacLochlainns Weblog

Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:

ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

This error occurs on an INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an UPDATE statement to populate REF values.

The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.

If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the REF column empty:

-- Create the BASE_T type, or specification for IDL.
CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( obj_id    NUMBER
  , obj_name  VARCHAR2(30)
  , obj_ref   REF base_t)
  NOT FINAL;
/

You can then create a table like the following:

CREATE TABLE base OF base_t
( obj_id CONSTRAINT base_pk PRIMARY KEY )
  OBJECT IDENTIFIER IS PRIMARY KEY;

Let’s insert some rows to test for ourselves that this fails when you try to assign references:

INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL));
INSERT INTO base VALUES (base_t(2, 'Borfur',NULL));
INSERT INTO base VALUES (base_t(3, 'Gloin',NULL));
INSERT INTO base VALUES (base_t(4, 'Kili',NULL));
INSERT INTO base VALUES (base_t(5, 'Fili',NULL));

The following UPDATE statement attempts to assign references, but fails as shown below:

UPDATE   base b
SET      obj_ref = REF(b);

The UPDATE fails as shown:

UPDATE   base b
         *
ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

The simple fix redefines the object table by removing the OBJ_ID column as an object identifier and primary key value. You do that by removing the OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.

CREATE TABLE base OF base_t

Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.

The next statement creates a CHILD table that holds a reference to the BASE (or parent) table and another instance of the same BASE_T object type:

CREATE TABLE child
( child_id   NUMBER  CONSTRAINT child_pk PRIMARY KEY
, base_ref   REF base_t SCOPE IS base
, child      base_t);

The INSERT statement can’t use a VALUES clause because we MUST capture the reference (or in this case primary key) from the BASE (or parent) table. An INSERT statement with a query does the trick:

INSERT INTO child 
SELECT 1, obj_ref, base_t(1, 'Gimli',NULL)
FROM   base b
WHERE  b.obj_name = 'Gloin';

You should note that the reference for the CHILD table’s CHILD column isn’t set but is likewise not required for the example to work.

Now, lets perform an standard INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the BASE (or parent) table and the table, column, and embedded OBJ_REF of the CHILD table.

COLUMN father FORMAT A10
COLUMN son    FORMAT A10
SELECT   b.obj_name AS "Father"
,        c.child.obj_name AS "Son"
FROM     base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;

You get the following results:

Father     Son
---------- ----------
Gloin      Gimli

You can make a view of this table with either of these syntaxes:

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS
SELECT * FROM base;

or,

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS
SELECT * FROM base;

Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

1
2
3
4

CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1
2

CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

Lastly, you create an object table function, like:

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

CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3

CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).

Anyway, let me quote the rules:

Oracle recommends that the password you specify:

• Contains at least one lowercase letter.
• Contains at least one uppercase letter.
• Contains at least one digit.
• Is at least 8 characters in length.
• Uses the database character set which can include the underscore (_), dollar ($), and pound sign (#) character.
• If (the password) contains special characters, including beginning the password with a number or symbol, then enclose the password with double-quotation marks.
• Should not be an actual word.

Likewise, you can’t use the old educational passwords:

• The SYS account password cannot be change_on_install (case-insensitive).
• The SYSTEM account password cannot be manager (case-insensitive).
• The SYSMAN account password cannot be sysman (case-insensitive).
• The DBSNMP account password cannot be dbsnmp (case-insensitive).
• If you choose to use the same password for all the accounts, then that password cannot be change_on_install, manager, sysman, or dbsnmp (case-insensitive).

Hope this helps, and by the way reading the documentation never hurts too much! 🙂

I’m off to speak at the Utah Oracle User’s Group Training Days 2013 tomorrow and Thursday. I’m presenting on Oracle Database Triggers and on techniques leveraging PHP and MySQL Striped Views. If you’re not there, you can check this older (but recently updated) post on PHP and MySQL Striped Views.

Hope to see a few folks who use the blog. BTW, I won’t be at Collaborate 2013 this year. Travel budgets are always tight, and this year they’re more than tight. 😉

I had an interesting conversation about table functions in Oracle’s PL/SQL; and the fact that they’re not available in MySQL. When I explained they’re available in Microsoft T-SQL User-Defined Functions (UDFs), my students wanted a small example. One of them said they’d tried to do it but couldn’t get it to work because they found the Microsoft web pages difficult to read and use. Specifically, they didn’t like the sparseness of this one on how to create a function.

Here’s a quick definition of a UDF table function that runs in the studentdb schema (created in this post for migrating SQL Server into a MySQL database). The following getConquistador function takes a single string, which acts to filter the result set from a query positioned as the return value of the function. You should note that this is an implementation of Microsoft’s Common Language Infrastructure (CLI).

CREATE FUNCTION studentdb.getConquistador
(@nationality AS VARCHAR(30))
RETURNS TABLE
RETURN SELECT * FROM studentdb.conquistador WHERE nationality = @nationality;

Unlike Oracle SQL, where you need to use the TABLE function to read the content of a table result from a function, you don’t need anything other than the function call in the FROM clause of a T-SQL query. Here’s an example of calling the table function:

SELECT * FROM studentdb.getConquistador('German');

The complete result from the query would produce these results when run from the sqlcmd command-line interface:

conquistador_id conquistador          actual_name          nationality
--------------- --------------------- -------------------- ------------
             11 Nicolas de Federman   Nikolaus Federmann   German
             13 Jorge de la Espira    George von Speyer    German
 
(2 rows affected)

However, you also have the ability to query only rows of interest without any specialized syntax, like this:

1> USE studentdb;
2> SELECT conquistador AS "Conquistador"
3> ,      actual_name AS "Name"
4> FROM   studentdb.getConquistador('German');
5> GO

This produces the following two-column result set:

Conquistador          Name
--------------------- --------------------
Nicolas de Federman   Nikolaus Federmann
Jorge de la Espira    George von Speyer
 
(2 rows affected)

Hope this helps those interested in T-SQL UDFs.

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT   m.account_number
,        m.member_type         -- A fk to common_lookup table.
,        m.credit_card_number
,        m.credit_card_type    -- A fk to common_lookup table.
,        c.first_name
,        c.middle_name
,        c.last_name
,        c.contact_type        -- A fk to common_lookup table.
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

It returns the results on the left, while a set of joins against the common_lookup table returns the results on the right (both use the \G in SQL Monitor to display the data vertically).

The member_type, credit_card_type, and contact_type columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup table is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT   m.account_number
,        cl1.common_lookup_meaning
,        m.credit_card_number
,        cl2.common_lookup_meaning
,        c.first_name
,        c.middle_name
,        c.last_name
,        cl3.common_lookup_meaning
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN common_lookup cl1
ON       cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2
ON       cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3
ON       cl3.common_lookup_id = c.contact_type
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):

The f(x) represents a generic function call where the x substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id) and the other holds the customer facing description (from the common_lookup_meaning column).

These steps describe the process:

1. Use the natural key (the user name) to find the data in the contact table.
2. Use the member_id foreign key column in the contact table to link to the same name column primary key in the member table by joining the two rows.
3. Use the foreign keys in the new row (combining columns from the contact and member tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.

The vertical choices displayed above map to OPTION tag elements of an HTML SELECT tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION tag, and it holds a null as the value of the VALUE attribute for the OPTION tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup function below.

Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.

You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.

Each of the drop downs contains the full selection of possibilities from the common_lookup table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:

The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:

As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup function. The function lets you find an existing value or returns a set of unique values for you to choose from.

You should rename the following credentials.php file as MySQLCredentials.inc for it to work in the file below it.

Assuming you’ve implemented the credentials.php file as the MySQLCredentials.inc file, you can now implement the following file. The get_common_lookup function returns a <SELECT> tag with a list embedded of <OPTION> tags with values; one <OPTION> tag is selected when the foreign key matches a valid primary key value in the common_lookup table; and no <OPTION> tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.

The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup function (on lines 104, 111, and 126). Each call to the get_common_lookup function selects the list of possible values and highlights the value associated with the foreign key value.

Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.

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

<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
  /* Class tag element styles. */
  .box {border:1px solid;padding:0px;width:392px;background-color:silver;}
  .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;}
  .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;}
  .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;}
  .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;}
  .clear {clear:left;}
  .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;}
  .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;}
  .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;}
  .formInput {min-width:150px;margin:5px;text-align:left;}
  .formShortInput {width:80px;margin:5px;text-align:left;}
  .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;}
  .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;}
</style>
</header>
<body>
<?php
 
  // Include libraries.
  include_once("MySQLCredentials.inc");
  include_once("library.inc");
 
  // Define a HTML page string.
  $out = '';
 
  // Declare input variables.
  $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry";
  $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James";
  $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter";
 
  // Declare output variables.
  $member_account_number = null;
  $credit_card_number = null;
 
  // Declare lookup input and output (initialized as nulls to suppress warnings) variables.  
  $member_table = 'member';
  $member_type = 'member_type';
  $member_type_id = null;
  $credit_card_type = 'credit_card_type';
  $credit_card_type_id = null;
  $contact_table = 'contact';
  $contact_type = 'contact_type';
  $contact_type_id = null;
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Declare a static query.
    $sql = "SELECT   m.account_number\n"
         . ",        m.member_type\n"
         . ",        m.credit_card_number\n"
         . ",        m.credit_card_type\n"
         . ",        c.first_name\n"
         . ",        c.middle_name\n"
         . ",        c.last_name\n"
         . ",        c.contact_type\n"
         . "FROM     member m INNER JOIN contact c\n"
         . "ON       m.member_id = c.member_id\n"
         . "WHERE    c.first_name = ?\n"
         . "AND      c.middle_name = ?\n"
         . "AND      c.last_name = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      // This query only returns one row, and an empty block follows the while logic.
      $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id);
      while ($stmt->fetch()) {}
    }
  }
 
  // Print the query form.
  $out .= '<form method="post" name="myForm" action="submitItemType.php">';
  $out .= '<label class="title1">Membership Account Information</label><br />';
  $out .= '<div class="box">';
  $out .= '<div class="topBox">';
  $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />';
  $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($member_table, $member_type, $member_type_id);
 
  $out .= '</select><br />';
  $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />';
  $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 
  // Get dynamic credit card type lookup string fragment.
  $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id);
 
  // Print the closing HTML table tag.
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="middleBox">';
  $out .= '<label class="formDownLabel">&nbsp;</label>';
  $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />';
  $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />';
  $out .= '<label class="formDownLabel">Type</label>';
  $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id);
 
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="bottomBox">';
  $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">';
  $out .= '</div>';
  $out .= '</form>';
  $out .= '</body>';
  $out .= '</html>';
 
  print $out;
?>
</body>
</html>

The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:

1
2
3
4
5
6
7
8
9
10
11
12

<html>
<head>
</head>
<body>
<?php
  // Print the surrogate key values for the common_lookup_id column.
  print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />";
  print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />";
  print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />";
?>
</body>
</html>

I hope this helps those who are interested in sorting how to implement a common_lookup table.

Somebody said, I shouldn’t have buried so much information in an answer to a question posed in a comment on a summary blog page. They suggested I put it in a regular blog post, and here it is with as little editing as possible.

The Ron Quizon user provided this sample code and a “What’s wrong with this PL/SQL program?”

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
   v_name friends.fname%TYPE;
   v_grade friends.id%TYPE;
BEGIN
   SELECT fname, grade
      INTO &ssv_name, v_grade
   FROM friends
   WHERE v_name = fname;
   DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0));
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123');
END;

While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.

There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local v_name variable. If you’d taken the time to create the tables and try it, you should generate an error like this:

SQL> /
Enter VALUE FOR ssv_name: Harry
OLD   6:       INTO &ssv_name, v_grade
NEW   6:       INTO Harry, v_grade
      INTO Harry, v_grade
           *
ERROR at line 6:
ORA-06550: line 6, COLUMN 12:
PLS-00201: identifier 'HARRY' must be declared
ORA-06550: line 7, COLUMN 4:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, COLUMN 4:
PL/SQL: SQL Statement ignored

Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:

• The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
• The & (ampersand) preceding ssv_name makes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the &ssv_name input value is a valid locally declared variable or defined session bind variable name.
• Assuming you input a valid identifier, the next problem is that the WHERE clause uses an equality comparison operator against the v_name local variable, which holds a null value. That means the SELECT-INTO always fails on a no data found error unless you add an assignment statement for the v_name variable.

Anyway, I hope spelling it out in a formal blog post was helpful to someone in the future. If so, leave a comment and let me know.

Two things contributed to this post. One was a student question about the difference between the MAX_VALUE of a sequence and the actual sequence value. The other was a comment on an earlier post addressing an NDS approach to resetting sequences.

The student wanted to understand why there were gaps in the sequence, since they created it with the default values, like this:

CREATE SEQUENCE sequence_name;

A default sequence in an Oracle database sets the cache value to 20, which means you get gaps every time a new set is allocated. This Ask Tom question and answer holds that information.

The blog comment was on an NDS statement post. I wasn’t quite sure what the comment wanted because there really wasn’t a question or a complete code example. Having demonstrated how to dynamically drop and recreate a new sequence without a gap in a comment reply, I thought that was adequate.

Having pointed the student to the Ask Tom column and my answer, he wanted to know how to create a stored procedure to reset sequences dynamically. It took me a couple weeks to get back to this but here’s the procedure. The tricky element of the procedure is the “no binding values allowed” restriction placed on NDS statements that process DDL statements.

The procedure uses two local procedures and two local functinons. The local find_sequence finds a sequence name in the schema, and find_next_sequence function returns the next value. The local create_sequence and drop_sequence procedures respectively isolate the logic for dynamic drops and creates of the sequence.

Warning: The restriction with this design assumes that the table name and sequence name are linked by using the table name and an _ID suffix.

-- Declare an autonomous procedure to drop sequences.
CREATE OR REPLACE PROCEDURE reset_sequence
( pv_table_name     VARCHAR2
, pv_sequence_name  VARCHAR2
, pv_cache_value    NUMBER DEFAULT 0 ) IS
 
  -- Declares stubs to remove forward reference limits.
  PROCEDURE create_sequence ( pv_sequence_name  VARCHAR2, pv_cache_value  NUMBER );
  PROCEDURE drop_sequence ( pv_sequence_name  VARCHAR2 );
  FUNCTION find_sequence ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2;
  FUNCTION find_next_sequence ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2;
 
  -- Drops sequence.
  PROCEDURE drop_sequence
  ( pv_sequence_name  VARCHAR2 ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
  BEGIN
 
    /*  Conditionally drop any sequence using a local function to find a valid
        sequence name before attempting to drop it. */  
    IF find_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)) > 0 THEN  
 
      /* Dynamically drop sequence, which requires concatenating the sanitized 
         sequence name because you can't bind values on a DDL statement. */
      lv_statement := 'DROP sequence '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
 
      -- Execute statement immediately.
      EXECUTE IMMEDIATE lv_statement;
 
    END IF;      
  END drop_sequence;
 
    -- Drops sequence.
  PROCEDURE create_sequence
  ( pv_sequence_name  VARCHAR2
  , pv_cache_value    NUMBER ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_next_sequence  NUMBER;
  BEGIN
 
    -- Assign the next sequence value if one exists.
    lv_next_sequence := find_next_sequence(pv_table_name);
 
    -- Check whether a cache value has been provided.
    IF pv_cache_value > 0 THEN
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       'MINVALUE 1 NOMAXVALUE INCREMENT BY 1'||CHR(10)
                   ||       'START WITH '||lv_next_sequence||' CACHE '||pv_cache_value||' NOORDER NOCYCLE';
 
    ELSE
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       ' MINVALUE 1 NOMAXVALUE'||CHR(10)
                   ||       'INCREMENT BY 1 START WITH '||lv_next_sequence||' NOCACHE NOORDER NOCYCLE';
 
    END IF;
 
    -- Execute create sequence statement.
    EXECUTE IMMEDIATE lv_statement;
 
  END create_sequence;
 
  -- Finds whether a sequence exists.
  FUNCTION find_sequence
  ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
    lv_return_value   NUMBER := 0;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   sequence_name'||CHR(10)
                 || 'FROM     user_sequences'||CHR(10)
                 || 'WHERE    sequence_name = :seq_name';
 
    OPEN lv_cursor FOR lv_statement USING DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
    FETCH lv_cursor INTO lv_sequence_name;
    CLOSE lv_cursor;
 
    -- Convert valid sequence name to positive integer, which represents truth.
    lv_return_value := LENGTH(lv_sequence_name);
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_sequence;
 
  -- Finds the next sequence value.
  FUNCTION find_next_sequence
  ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_value  NUMBER;
    lv_return_value    NUMBER := 1;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name)||'_ID) + 1'||CHR(10)
                 || 'FROM    '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name);
 
    OPEN lv_cursor FOR lv_statement;
    FETCH lv_cursor INTO lv_sequence_value;
    CLOSE lv_cursor;
 
    -- Assign the return value from the NDS statement to a local return variable.
    lv_return_value := lv_sequence_value;
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_next_sequence;
 
  -- Set precompiler instruction to run in a discrete process.
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
  -- Conditionally drop sequence.
  drop_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name));
 
  -- Create sequence.
  create_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name), pv_cache_value);
 
END;
/

You can test this procedure with the following script, which includes COMMIT statements. Those are requires because the stand alone procedure runs independently of the current session as an autonomous procedure.

-- Conditionally drop existing tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('SAMPLE','SAMPLE_S')) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
      dbms_output.put_line(i.object_name);
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;   
      dbms_output.put_line(i.object_name);
    END IF;
  END LOOP;
END;
/
 
-- Create test table.
CREATE TABLE sample
(sample_id  NUMBER);
 
-- Create test table.
CREATE SEQUENCE sample_s;
 
-- Insert a seeding row.
INSERT INTO sample VALUES (10);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Reset the sequence value.
EXECUTE reset_sequence('SAMPLE','SAMPLE_S');
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;
 
EXECUTE reset_sequence('SAMPLE','SAMPLE_S',500);
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;

Hope this helps anybody interested in automating the process of resetting a START WITH value of an Oracle sequence.

Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.

A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!

It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.

First of all, the argument against stored programs is simply not true. SQL DML statements, like the INSERT, UPDATE, and DELETE statements should maintain ACID compliant interactions with a single table in a database. Unfortunately, the same statements create anomalies (errors) in a poorly designed database.

Stored programs provide the ability to perform ACID compliant interactions across a series of tables in a database. They may also hide database design errors and protect the data from corruption. The same can’t be said for Java or C# developers. Java and C# developers frequently fail to see database design errors or they overlook them as inconsequential. This type of behavior results in corrupt data.

It typically raises cost, errors, and overall application complexity when key logic migrates outside the database. If you’re asking why, that’s great. Here are my thoughts on why:

1. Making a Java or C# programmer responsible for managing the transaction scope across multiple tables in a database is not trivial. It requires a Java programmer that truly has mastered SQL. As a rule, it means a programmer writes many more lines of logic in their code because they don’t understand how to use SQL. It often eliminates joins from being performed in the database where they would considerably outperform external language operations.
2. Identifying bottlenecks and poor usage of data becomes much more complex for DBAs because small queries that avoid joins don’t appear problematic inside the database. DBAs don’t look at the execution or scope of transactions running outside of the database and you generally are left with anecdotal customer complaints about the inefficiency of the application. Therefore, you have diminished accountability.
3. Developing a library of stored procedures (and functions) ensures the integrity of transaction management. It also provides a series of published interfaces to developers writing the application logic. The published interface provides a modular interface, and lets developers focus on delivering quality applications without worrying about the database design. It lowers costs and increases quality by focusing developers on their strengths rather than trying to make them generalists. That having been said, it should never mask a poorly designed database!
4. Service level agreements are critical metrics in any organization because they compel efficiency. If you mix the logic of the database and the application layer together, you can’t hold the development team responsible for the interface or batch processing metrics because they’ll always “blame” the database. Likewise, you can’t hold the database team responsible for performance when their metrics will only show trivial DML statement processing. Moreover, the DBA team will always show you that it’s not their fault because they’ve got metrics!
5. Removing transaction controls from the database server generally means you increase the analysis and design costs. That’s because few developers have deep understanding of a non-database programming language and the database. Likewise, input from DBAs is marginalized because the solution that makes sense is disallowed by design fiat. Systems designed in this type of disparate way often evolve into extremely awkward application models.

Interestingly, the effective use of T-SQL or PL/SQL often identifies, isolates, and manages issues in poorly designed database models. That’s because they focus on the integrity of transactions across tables and leverage native database features. They also act like CSS files, effectively avoiding the use of inline style or embedded SQL and transaction control statements.

Let’s face this fact; any person who writes something like “spaghetti” code in the original context is poorly informed. They’re typically trying to sidestep blame for an existing bad application design or drive a change of platform without cost justification.

My take on this argument is two fold. Technologists in the organization may want to dump what they have and play with something else; or business and IT management may want to sidestep the wrath of angry users by blaming their failure on technology instead of how they didn’t design, manage, or deliver it.

Oh, wait … isn’t that last paragraph the reason for the existence of pre-package software? 😉 Don’t hesitate to chime in, after all it’s just my off-the-cuff opinion.

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7^th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …