MacLochlainns Weblog

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47
Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48
Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69

Turns out Zend 6 doesn’t automatically set the [Date] elements in the php.ini file, which is required for the oci_set_client_info(), oci_set_module_name(), and oci_set_action() functions of the OCI. You can find the php.ini file in the C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
 
; http://php.net/date.default-latitude
;date.default_latitude = 31.7667
 
; http://php.net/date.default-longitude
;date.default_longitude = 35.2333
 
; http://php.net/date.sunrise-zenith
;date.sunrise_zenith = 90.583333
 
; http://php.net/date.sunset-zenith
;date.sunset_zenith = 90.583333

You can find the values for date.timezone here. Update the date.timezone as follows:

date.timezone = America/Denver

Then, reboot the Zend Server, and it fixes the warning messages.

I actively maintain virtual test instances for my writing and teaching on Windows 7, Red Hat Enterprise Linux, Fedora, and Solaris. Perl on Windows is always interesting. I use ActivePerl on Windows 7 rather than Strawberry Perl, and it was interesting to see this note after I upgraded to the most current Community Edition of ActivePerl (5.16.3).

I thought it might be kind to post ActiveState’s Release Note because you should really read it before you try to install ActivePerl on Windows 7/8. Just make sure you’ve removed any earlier version of ActivePerl before trying the install. That’s what I did, and surprise, there weren’t any problems.

While the installation doesn’t tell you that you need to restart the Windows 7 operating system, you do. If you don’t restart Windows 7 after the ActivePerl install, you’ll get the following error message:

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC

You can grab the DBD::MySQL bundle with this command:

perl -MCPAN -e install Bundle::DBD::mysql

It downloads the MinGW (Minimalist GNU for Windows) C/C++ compiler, but it’ll be nested and not conflict with one if you have installed it independently. Absence of the compiler means you can’t attempt to build the library.

Proceeding further, I discovered a number of incompatibilities. The basic commands require you to load CPAN (Comprehensive Perl Archive Network) shell:

perl -MCPAN -e shell

Inside the Perl CPAN shell:

cpan> force install DBD:mysql --mysql_config C:\ProgramData\MySQL\MySQL Server 5.6

You also need to create a MySQL anonymous user in the database to support the default user during compilation, and if you dropped the test database you need to recreate it. I also ended up creating an ODBC user. Here are the commands to run as the root superuser:

CREATE USER 'ODBC'@'localhost';
CREATE USER ''@'localhost' IDENTIFIED BY 's3krit';
GRANT ALL ON test.* TO ''@'localhost';

I also took the time to update the MySQL test.pl to Perl 5.16; unfortunately, at the end of the day I didn’t get it working. Maybe I’ll try back in a few weeks. You need to change line 57 from port 5050 to port 3306, and “SharedSecret” to “s3krit“, as shown:

Here’s are the edits required test.pl program on lines 189 and 194:

The only release note provided is below, which I found inadequate. If you find the trick before me, please post it.

Windows

The fork() emulation has known limitations. See perlfork for a detailed summary. In particular, fork() emulation will not work correctly with extensions that are either not thread-safe, or maintain internal state that cannot be cloned in the pseudo-child process. This caveat currently applies to extensions such as Tk and Storable.

It seems that some people are having problems with the ActivePerl MSI installer. The first thing to note is that you CANNOT install ActivePerl 5.16 over an older version of ActivePerl based on 5.14, 5.12, 5.10, 5.8, 5.6 or 5.005, such as build 1204 or any other 1200, 1000, 800, 600 or 500 series build. We have determined that some of these problems are due to other installations of Perl that may be pointed at by something in the environment.

The sure-fire solution is to make absolutely certain that no other installations of Perl are on the target machine. Realizing that this is not always possible, you can follow these steps to ensure the other installations will not interfere.

Stop the “Windows Installer” service. This can be accomplished from the command prompt using the following command:
C:\> net stop "Windows Installer"
C:\> net stop "Windows Installer"
Temporarily remove or rename PERLLIB and PERL5LIB environment variables in the system environment.

Temporarily remove or rename the following registry values:

[\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV)
[\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV)
[\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV)
[\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)

Proceed with the installation.

Once the installation has completed successfully, the above actions may be undone although restoring the environment variables or the registry values may interfere with the proper operation of your new ActivePerl installation. In order to perform all of the above steps, you will need to have Administrative privileges on the target machine. If you do not have the required privileges you should contact your Administrator.

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, created_by        INT UNSIGNED
, creation_date     DATE
, last_updated_by   INT UNSIGNED
, last_update_date  DATE) ENGINE=MyISAM;

Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.

CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'insert'
           ,'contact'
           , new.last_name
           , REPLACE(new.last_name,' ','-')
           , new.created_by
           , new.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Replace the name for the INSERT INTO the CONTACT table. */
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$

Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.

CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'update'
           ,'contact'
           , new.last_name
           , null           
           , old.created_by
           , old.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Throw an exception to force the business user to see they
       can't update a last name with a white space. */
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$

Next, you create a test case with an INSERT and UPDATE statement that meets the condition of the triggers.

/* Insert a row meeting the trigger condition. */
INSERT INTO contact VALUES
( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE());
 
/* Update a row meeting the trigger condition. */
UPDATE contact
SET    last_name = 'Zeta Jones'
,      last_updated_by = 1003
,      last_update_date = UTC_DATE()
WHERE  last_name = 'Zeta-Jones';

Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.

SELECT * FROM logger;

It returns:

+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
| logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
|         1 | insert       | contact      | Zeta Jones      | Zeta-Jones       |       1001 | 2013-04-26    |            1001 | 2013-04-26       |
|         2 | update       | contact      | Zeta Jones      | NULL             |       1001 | 2013-04-26    |            1003 | 2013-04-26       |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
2 rows in set (0.00 sec)

This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.

My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.

Installation Steps

The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:

Visual Studio Tools for Office 20120 Runtime
Microsoft .NET Framework 4 Client Profile
Microsoft Excel 2007 or greater
Microsoft .NET Framework 4 Client Profile
Microsoft Visual C++ 2010 32-bit runtime
Microsoft .NET Framework 4 Client Profile

Below are the installation steps after you download the current release .msi file. The icon should look like the one to the right. For this example, I”m using the mysql-installer-community-5.6.11.0.msi. Double-click the icon on your desktop or from your C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).

The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.

The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.

The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.

The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.

The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.

The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.

The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.

MySQL Server

Both the client and server software for the MySQL Server

MySQL Workbench

The GUI application to develop for and manage the server.

MySQL Visual Studio Plugin

To work with the MySQL Server from VS.

MySQL Connectors

Connector/Net, Java, C/C++, OBDC and others.

Examples and tutorials

To help you get started with your development.

Documentation

Allows you to read the documentation offline.

The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.

The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.
- MySQL Server 5.6.11
- MySQL Workbench CE 5.2.47
- MySQL Notifier 1.0.3
- MySQL for Excel 1.1.1
- Connector/ODBC 5.2.4
- Connector/C++ 1.1.2
- Connector/C++ 1.1.2
- Connector/J 5.1.24
- Connector/NET 6.6.5
- MySQL Documentation 5.6.11
- Samples and Examples 5.6.11

The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.

The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.

This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.

Configuration Steps

You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:

The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.

The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.

The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the root password twice, a trivial and unsecure password cangetin is what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.

The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.

The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.

The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.

The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.

The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.

Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System %PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.

I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.

Somebody asked me why they can’t query the DBMS_TYPES.TYPECODE_OBJECT value because they get an ORA-06553 error. Their query attempt is:

SELECT   dbms_types.typecode_object
FROM     dual;

Naturally, it raises the following exception:

SELECT   dbms_types.typecode_object
         *
ERROR at line 1:
ORA-06553: PLS-221: 'TYPECODE_OBJECT' IS NOT a PROCEDURE OR IS undefined

The explanation is very simple. It’s a package scoped variable and in Oracle 11g only accessible in a PL/SQL block. Here’s an anonymous block that would print the value to the console:

BEGIN
  dbms_output.put_line(dbms_types.typecode_object);
END;
/

Hope that helps those trying to discover what a package variable’s value is.

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL hello('\'Harriet\' OR 1 = 1');

It fails because there’s no matching 'Harriet' OR 1 = 1 in the table’s sample_name column. However, it works well when we submit 'Harriet' by herself, without the intended SQL inject phrase “OR 1 = 1“, as you can see:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         2 | Harriet     |
+-----------+-------------+
1 row in set (0.00 sec)

There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END;
$$

The last hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the sample table.

CALL hello('\'Juliette\' OR 1 = 1');

It would display:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         1 | Hank        |
|         2 | Harriet     |
+-----------+-------------+
2 rows in set (0.00 sec)

While there’s no sample_name value of 'Juliette', the “OR 1 = 1” is true. Therefore, the SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.

A solution with the WHERE clause as part of the parameter would look like this:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  pv_where;
END;
$$

The modified call:

CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');

returns all rows from the table.

A solution without the WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the CONCAT function, which also added nothing to the example once I tested it.

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  pv_where;
END;
$$

It’s simply works only when you provide a “1 = 1” or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

but this SQL injection statement works:

CALL hello('1 = 1');

returns all rows from the table.

This example, when you omit the white space also works with embedded strings or numeric operands and an operator:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHEREpv_where;
END;
$$

It returns all rows with a call like this:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because WHEREpv_where simply becomes a table alias in the query.

In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.

Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.

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:

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:

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. 😉

MacLochlainns Weblog

Zend 6 & Timezones

ActivePerl on Windows

MySQL Triggers with Logging

MySQL 5.6 Install Steps

Finding DBMS_TYPES value?

SQL Injection Risks

Conflict between identifiers

Object Table Function View

Oracle Passwords

Speaking at UTOUG

Recent Posts

Things Written About

Pages

Blogroll

Archives