Thursday Jan 22, 2015

A Personal Victory: Oracle Database Sample Schemas are on GitHub

For anyone who ever deleted a row from a table in Oracle's Sample HR schema and wanted it back, help is nearby. You no longer have to download the full "Oracle Database 12c Release 1 Examples" zip (499,228,127 bytes worth for the Linux bundle) and run the Oracle installer. Now you can clone our GitHub db-sample-schema repository and run the creation SQL scripts in SQL*Plus.

This new repository installs these six sample schemas:

  • HR: Human Resources
  • OE: Order Entry
  • PM: Product Media
  • IX: Information Exchange
  • SH: Sales History
  • BI: Business Intelligence

Because of the widespread use of these schemas, we did minimal changes to the bundle. The install, as it is given, installs all schemas and needs to be done on a database server since file system access is needed from the database.

But now, if you want, you can fork the repo and modify it to install just the HR schema from a client machine. Or change your fork to install the HR schema into an arbitrary user name of your choice so multiple people can test the same data set. And what about modifying the script to do DROP TRIGGER SECURE_EMPLOYEES getting rid of that annoying time-based trigger which yells 'You may only make changes during normal office hours' if you try to make changes after 6pm or on weekends? This may be a great teaching tool about triggers but not useful when you are configuring demonstrations for big conferences late into the night!

And why is this a personal victory? Because as a client tool person, how to find these schema creation scripts has irked me in the past. The HR schema replaced SCOTT/TIGER in the Oracle documentation a long time ago but was not easily available to use. I've written a lot of examples using HR but never had a good way to instruct how to install the schema. I'm glad to have helped (being partially modest here about the legal and administrative things it required) getting this small set of scripts out on GitHub. If it makes it easier for someone to talk about features or issues by reference to a common data set, then my job is done. Having the scripts readily available is also a reminder to the Oracle community to share information and knowledge efficiently. Even as we head to a world of cloneable databases and snapshots, sometimes it is just easier to run a SQL script.

This repo is a piece of a jigsaw, and it can be used where it fits. The schemas could be now considered "traditional". In future, Oracle Database teams will continue to create fresh data sets to show off newer and upcoming database features, such as these analytical-sql-examples that you might be interested in.

Thursday Jul 25, 2013

PHP OCI8 2.0.0-devel for Oracle Database is on PECL

PHP OCI8 2.0.0-devel is now available on PECL (and in the PHP 'master' branch). This is a development release, so changes may occur. I'm looking for feedback, particularly on:

  • Feature design, including naming choices
  • Install feedback from different platforms with different build tool versions
  • Whether to drop support for installing on PHP 4. I'm likely to do this. (The OCI8 1.4.10 release is usable for installing on PHP 4)

OCI8 2.0 can be installed as a shared extension from PECL with:

pecl install oci8-devel

Alternatively, if you have OCI8 1.x currently installed as a static extension, then you will need to build PHP again. This is easy using a trunk snapshot from snaps.php.net. Instructions for building are in various places on the web, including in The Underground PHP and Oracle Manual.

The OCI8 2.0.0-devel release contains a bunch of clean ups, and some new and changed features. The full list is found in the package.xml file:

    - NEW FUNCTIONALITY:
 
      - Added Implicit Result Set support for Oracle Database 12c.
	Streaming of all IRS's returned from a PL/SQL block is available
	via oci_fetch_array, oci_fetch_assoc, oci_fetch_object and
	oci_fetch_row (but not oci_fetch or oci_fetch_all).
	Alternatively individual IRS statement resources can be obtained
	with the new function 'oci_get_implicit_resultset' and passed to
	any oci_fetch_* function.

      - Added DTrace probes enabled with PHP's generic --enable-dtrace

    - IMPROVED FUNCTIONALITY:
 
      - Using 'oci_execute($s, OCI_NO_AUTO_COMMIT)' for a SELECT no
	longer unnecessarily initiates an internal ROLLBACK during
	connection close.  This can improve overall scalability by
	reducing "round trips" between PHP and the database.
 
    - CHANGED FUNCTIONALITY:
 
      - PHPINFO() CHANGES:
 
        - The oci8.event and oci8.connection_class values are now shown
          only when the Oracle client libraries support the respective
          functionality.
 
        - Connection statistics are now in a separate phpinfo() table.
 
        - Temporary LOB and Collection support status lines in
          phpinfo() were removed.  These features have always been
          enabled since 2007.
 
      - OCI_INTERNAL_DEBUG() CHANGES:
 
        - The oci_internal_debug() function is now a no-op.  Use PHP's
          --enable-dtrace functionality with DTrace or SystemTap instead.
 
    - INTERNAL CHANGES:
 
      - Fixed a potential NULL pointer dereference flagged by Parfait
        static code analysis.
 
      - Extended testing of existing OCI8 functionality.
 
      - Improved test output portability when using the PHP development
        web server to run tests.
 
      - Removed no-longer necessary Unicode patterns from tests
        (vestiges of PHP's previous PHP 6 project)
        
      - Improved build portability by removing compilation type cast
        warnings with some compilers.
 
      - Fixed compilation warnings when building with Oracle 9.2
        client libraries.
 
      - Updated code to use internal macro PHP_OCI_REGISTER_RESOURCE.
 
      - Regularized code prototypes and fixed some in-line documentation
        prototypes.
 
      - Fixed code folding. 

Subsequent blog posts will talk about the features in OCI8 2.0.0.

About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today