MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle 11g’ Category

Windows 10 Laptops

without comments

Teaching Oracle technology always has challenges. They’re generally large challenges because we ask students to run 4 GB Linux VM with Oracle Database 11g XE pre-configured for them. A number of the student computers aren’t up to the task of running the virtualization.

Installing VMware Workstation or Player and a 64-bit Linux operating system is the easiest way to discover a laptop that advertises itself as 64-bit when it truly isn’t. Most of the computers raise an exception that says they’re unable to run hyperthreading, and naturally two BIOS settings are disabled by the manufacturers.

As a result, I get a lot of questions from students on computers. Some of the questions are simple and driven by a desire to maximize their investment. Other questions aren’t quite as simple. The harder questions are typically driven by a need to accomplish something they can’t do with their computer.

I can’t help but feel too many students see laptops as commodities, like televisions. They purchase their laptops thinking they’ve bought the right computer because it provides features like a touch screen. Unfortunately, they don’t notice things like the operating system because many of them purchase computers that run the Microsoft Windows.

They believe Microsoft Windows is simply a single operating system. They don’t know that there are seven versions of Windows 10 with different features. More importantly, they don’t know there are two key versions of Windows 10 when they purchase a laptop – the Windows 10 Home and Windows 10 Pro. The student seem to never find a simple Windows 10 Buyers Guide.

Windows 10 Home Edition is designed for end-user computing that includes using application software, whereas Windows 10 Pro Edition is designed for computing that runs both application and server software. The choice of one over the other determines what you can or can’t do with your Windows software.

Changing between Windows 10 Home and Windows 10 Pro comes at a cost to most consumers. That’s because they purchase machines with OEM versions of the Windows operating system. Vendors provide OEM versions of Windows 10 because they customize boards and chip-sets; and sometimes they purchase and install chips that fail to meet manufacturing standards. In these cases, the OEM Windows 10 comes with modifications and custom drivers. Moving from an OEM Windows 10 Home to a Windows 10 Pro can be very complicated.

Also, it’s all too common for OEM Windows 10 to disable 64-bit operations while advertising their product as 64-bit. The reasons for this can be complex and hard to identify sometimes. When a manufacturer purchases defective CPUs, they tend to disable some of the chips features. Manufacturers often disable 64-bit features to work around a defective CPU, one or more chip-sets, or their own customizations to the Windows 10 operating system.

I wrote all this to help focus purchases for those who want to run an Oracle Database on a Windows 10 operating system. You have two choices. One uses the native Windows 10 Pro operating system to run Oracle Database 11g XE natively, and the other uses Windows 10 to run VMware or Virtual Box to support a Linux operating system and Oracle Database 11g XE instance.

Best of luck, and always check the laptop specifications. As a rule, don’t buy Windows 10 Home machines if you want to run an Oracle Database.

Written by maclochlainn

January 15th, 2018 at 9:27 pm

Type Dependency Tree

without comments

While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.

This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):

CREATE OR REPLACE TYPE base_t AS OBJECT
( base_id  NUMBER ) NOT FINAL;
/
 
CREATE OR REPLACE TYPE person_t UNDER base_t
( first_name   VARCHAR2(20)
, middle_name  VARCHAR2(20)
, last_name    VARCHAR2(20)) NOT FINAL;
/
 
CREATE OR REPLACE TYPE driver_t UNDER person_t
( license VARCHAR2(20));
/

Here’s a query to show the hierarchy of object types and attributes by object-level in the hierarchy:

COL type_name  FORMAT A20  HEADING TYPE_NAME
COL attr_no    FORMAT 999  HEADING ATTR_NO
COL attr_name  FORMAT A20  HEADING ATTR_NAME
COL TYPE       FORMAT A12  HEADING TYPE
SELECT   DISTINCT
         LPAD(' ',2*(LEVEL-1)) || ut.type_name AS type_name
,        uta.attr_no
,        uta.attr_name
,        CASE
           WHEN uta.attr_type_name = 'NUMBER' THEN
             uta.attr_type_name
           WHEN uta.attr_type_name = 'VARCHAR2' THEN
             uta.attr_type_name || '(' || uta.LENGTH || ')'
         END AS TYPE
FROM     user_types ut
,        user_type_attrs uta
WHERE    ut.typecode = 'OBJECT'
AND      ut.type_name = uta.type_name
AND      uta.inherited = 'NO'
START
WITH     ut.type_name = 'BASE_T'
CONNECT
BY PRIOR ut.type_name = ut.supertype_name
ORDER BY uta.attr_no;

It should return the following:

TYPE_NAME	     ATTR_NO ATTR_NAME		  TYPE
-------------------- ------- -------------------- ------------
BASE_T			   1 BASE_ID		  NUMBER
  PERSON_T		   2 FIRST_NAME 	  VARCHAR2(20)
  PERSON_T		   3 MIDDLE_NAME	  VARCHAR2(20)
  PERSON_T		   4 LAST_NAME		  VARCHAR2(20)
    DRIVER_T		   5 LICENSE		  VARCHAR2(20)

As always, I hope this helps those looking to discover an Oracle object type hierarchy without examining each object type in turn.

Written by maclochlainn

December 10th, 2017 at 12:59 am

Substitutable Columns

with 2 comments

Oracle’s substitutable columns are interesting and substantially different than Oracle’s nested tables. The benefit of substitutable columns is that you can create one for an object type or any subtypes of that object type. Unfortunately, you can’t create the same behavior with nested tables because Oracle’s implementation of collection types are always final data types and you can’t extend their behaviors.

The Oracle Database has three types of collections. Two are SQL scoped collection types and the remaining one is a PL/SQL-only collection. You can only use the two SQL scoped collection types as column data types. One of the SQL-scoped collection types is an Attribute Data Type (ADT), which uses a base data type of DATA, NUMBER, or VARCHAR2.

The base data types of a UDT are scalar data types and scalar data types are data types that hold one thing. The other SQL-scoped collection type is a collection of User-Defined Types (UDTs), which are object types that you create like record structures by assembling sets of basic scalar data types. The elements of a UDT are known as members, whereas the instances of a collection are known as elements because they indexed in a set.

You can join a row with any nested table by using a cross join because they match the row with the nested table by using an ID-dependent join. An ID-dependent join is inexpensive because it relies on a structural dependency, the existence of the nested table in a column of a row. Typical joins on the other hand are joins between two tables or two copies of the same table. These non ID-dependent joins use at least matching values in one column of each table or one column of two copies of a table.

Joins between substitutable columns that hold UDTs are unlike joins between nested tables. The following sets up an example to demonstrate how you can join the non-substitutable columns of a row with the substitutable columns.

  1. You need a base UDT object type that you can extend, where extend means you can create a subtype of the base object type. While this is straight forward when you create an Oracle object type with methods, it isn’t necessarily straight forward when you want to simply create a base data structure as a generalized type with subtypes.

    The important clause is overriding the FINAL default by making the base type NOT FINAL. The example use BASE_T as the generalized type or data structure of a substitutable column:

    CREATE OR REPLACE TYPE base_t AS OBJECT
    ( base_id  NUMBER ) NOT FINAL;
    /
  2. After you create your base data structure, you create a specialized subtype. The following example creates a PERSON_T type and accepts the default of FINAL, which means you can’t create another subtype level.

    CREATE OR REPLACE TYPE person_t UNDER base_t
    ( first_name   VARCHAR2(20)
    , middle_name  VARCHAR2(20)
    , last_name    VARCHAR2(20));
    /
  3. With a generalized BASE_T type and a specialized PERSON_T subtype, you create a CUSTOMER table with a substitutable CUSTOMER_NAME column. The CUSTOMER_NAME column uses the generalized BASE_T data type. You should also create a CUSTOMER_S sequence that you can use as a surrogate key column for the table.

    CREATE TABLE customer
    ( customer_id    NUMBER
    , customer_name  BASE_T );
     
    CREATE SEQUENCE customer_s;
  4. You can now populate the table with instances of the BASE_T type or the PERSON_T subtype. The following inserts three rows into the CUSTOMER table. One for Hank Pym the original Ant-Man, one for Scott Lang the succeeding Ant-Man, and another for Darren Cross the original Yellowjacket.

    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Hank'
              , middle_name => NULL
              , last_name => 'Pym'));
     
    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Scott'
              , middle_name => NULL
              , last_name => 'Lang'));
     
    INSERT INTO customer
    VALUES
    ( customer_s.NEXTVAL
    , person_t( customer_s.CURRVAL
              , first_name => 'Darren'
              , middle_name => NULL
              , last_name => 'Cross'));
  5. The significance or problem associated with substitutable columns is that the actual columns of the object data type are hidden, which means you can’t query them like they’re nested elements of the substitutable column. The following query demonstrates what happens when you try to access those hidden member columns:

    SELECT customer_id
    ,      customer_name.base_id
    ,      customer_name.first_name
    ,      customer_name.middle_name
    ,      customer_name.last_name
    FROM   customer;

    It returns the following error message:

    ,      customer_name.last_name
           *
    ERROR at line 5:
    ORA-00904: "CUSTOMER_NAME"."LAST_NAME": invalid identifier
  6. It only raises the last column in the SELECT-list because that’s the first place where it fails to recognize an identifier, which is a valid column name in scope of the query.

  7. This error message may lead you to call the CUSTOMER_NAME column in a subquery and use the TABLE function to convert it to a result set. However, it also fails because a UDT object type by itself is an ordinary object type not a collection of object types. The TABLE function can’t promote the single instance to collection.

    SELECT *
    FROM   TABLE(SELECT TREAT(customer_name AS person_t) FROM customer);

    It returns the following error message:

    FROM   TABLE(SELECT TREAT(customer_name AS person_t) FROM customer)
           *
    ERROR at line 2:
    ORA-22905: cannot access rows from a non-nested table item
  8. The non-nested table error message should lead you to wrap the call to the TREAT function in a call to the COLLECT function, like this:

    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT *
    FROM   TABLE(
             SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
             FROM customer);

    It returns the substitutable column’s hidden column labels and their values:

     Base First  Middle Last
     ID # Name   Name   Name
    ----- ------ ------ ------
        1 Hank	    Pym
        2 Scott	    Lang
        3 Darren	    Cross
  9. After learning how to unwrap the hidden columns of the substitutable column, you can now join the ordinary columns to the hidden columns like this:

    COL customer_id    FORMAT 9999  HEADING "Customer|ID #"
    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c INNER JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    It returns the ordinary column and substitutable column’s hidden column labels and their values:

    Customer  Base First  Middle Last
        ID #  ID # Name   Name   Name
    -------- ----- ------ ------ ------
           1     1 Hank	     Pym
           2     2 Scott	     Lang
           3     3 Darren	     Cross
  10. The preceding query only returns values when the substitutable column holds a value. It fails to return a value when the substitutable column holds a null value. You need to use a LEFT JOIN to ensure you see all ordinary columns whether or not the substitutable column holds a value.

    COL customer_id    FORMAT 9999  HEADING "Customer|ID #"
    COL base_id        FORMAT 9999  HEADING "Base|ID #"
    COL customer_name  FORMAT A38   HEADING "Customer Name"
    COL first_name     FORMAT A6    HEADING "First|Name"
    COL middle_name    FORMAT A6    HEADING "Middle|Name"
    COL last_name      FORMAT A6    HEADING "Last|Name"
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c LEFT JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    It returns the ordinary column and substitutable column’s hidden column labels and their values when the substitutable column holds an instance value. However, it only returns the ordinary column when the substitutable column holds a null value, as shown below:

    Customer  Base First  Middle Last
        ID #  ID # Name   Name   Name
    -------- ----- ------ ------ ------
           1     1 Hank	     Pym
           2     2 Scott	     Lang
           3     3 Darren	     Cross
           4
  11. It should be noted that queries like this have a cost, and that cost is high. So, you should only implement substitutable columns when the maintenance coding costs (or sustaining engineering) outweighs the processing cost.

    You can determine the cost like this:

    EXPLAIN PLAN
    SET STATEMENT_ID = 'Strange'
    FOR
    SELECT   c.customer_id
    ,        o.*
    FROM     customer c LEFT JOIN
             TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte
                   FROM   customer) o
    ON       c.customer_id = o.base_id
    ORDER BY c.customer_id;

    You can query the cost like this:

    SET LINESIZE 130
    SELECT *
    FROM   TABLE(dbms_xplan.display(NULL,'Strange'));

    It should return something like this for the sample table and solution:

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------
    Plan hash value: 2373055701
     
    ---------------------------------------------------------------------------------------------------------
    | Id  | Operation			     | Name	| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
    ---------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT		     |		|  8168 |   550K|	|   167   (2)| 00:00:03 |
    |   1 |  SORT ORDER BY			     |		|  8168 |   550K|   624K|   167   (2)| 00:00:03 |
    |*  2 |   HASH JOIN OUTER		     |		|  8168 |   550K|	|    32   (4)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL		     | CUSTOMER |     5 |    15 |	|     2   (0)| 00:00:01 |
    |   4 |    VIEW 			     |		|  8168 |   526K|	|    29   (0)| 00:00:01 |
    |   5 |     COLLECTION ITERATOR PICKLER FETCH|		|  8168 |	|	|    29   (0)| 00:00:01 |
    |   6 |      SORT AGGREGATE		     |		|     1 |    14 |	|	     |		|
    |   7 |       TABLE ACCESS FULL 	     | CUSTOMER |     5 |    70 |	|     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("C"."CUSTOMER_ID"="O"."SYS_NC_ROWINFO$"."BASE_ID"(+))

As always, I hope this explains how to insert and query the hidden columns of a substitutable column, and how you join ordinary columns and hidden columns of a substitutable column from a table.

Written by maclochlainn

December 8th, 2017 at 11:17 pm

Upgrade APEX 4 to 5.1

with 2 comments

This blog post shows you how to upgrade APEX Version 4.0.2 on a default Oracle Database 11g XE instance to APEX 5.1.2. Oracle’s APEX t upgrade document was just a bit too short and missed some details. It divided into five parts. The first part confirms your starting point. The second part downloads and positions the extracted software. The third part installs APEX 5. The fourth part configures APEX 5. The fifth part shows you how to access and get to the APEX 5 home page.

Confirm APEX 4 Installation

  1. Verify the database version by connecting as the system user through SQL*Plus and running the following query:

    SELECT banner
    FROM   v$version
    WHERE  banner LIKE 'Oracle Database%';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  2. Verify the APEX version by connecting as the system user through SQL*Plus and running the following query:

    COLUMN version_no        FORMAT A16 HEADING "Version Number"
    COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility"
    COLUMN patch_applied     FORMAT A14 HEADING "Patch Applied"
    SELECT *
    FROM   apex_release;

    It should return the following when you start with the base Oracle Database 11g XE:

    		 API
    Version Number	 Compatibility	  Patch Applied
    ---------------- ---------------- --------------
    4.0.2.00.09      2010.05.13
  3. Verify the XML Database version by connecting as the system user through SQL*Plus and running the following query:

    COLUMN comp_name FORMAT A20
    COLUMN version   FORMAT A12
    COLUMN status    FORMAT A8
    SELECT comp_name
    ,      version
    ,      status
    FROM   dba_registry
    WHERE  comp_id = 'XDB';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    COMP_NAME            VERSION      STATUS
    -------------------- ------------ --------
    Oracle XML Database  11.2.0.2.0   VALID
  4. Verify the memory_target of the instance by connecting as the system user through SQL*Plus and running the following query. It should be no smaller than 300 MB.

    show parameter memory_target

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    memory_target                        big integer 1G
  5. Oracle’s instructions qualify that APEX 5 will install into the APEX_050000 schema. Oracle creates the new APEX_050000 schema with a default of the sysaux and temp table space. You can verify these as the system user through SQL*Plus by running the following two queries. The first one checks for the tablespaces and the second for available space and auto extensibility.

    COLUMN default_tablespace   FORMAT A22
    COLUMN temporary_tablespace FORMAT A22
    SELECT default_tablespace
    ,      temporary_tablespace
    FROM   dba_users
    WHERE  username = 'APEX_040000';

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
    ---------------------- ----------------------
    SYSAUX                 TEMP

    COLUMN tablespace_name FORMAT A10        HEADING "Tablespace Name"
    COLUMN file_name       FORMAT A38        HEADING "File Name"
    COLUMN available_space FORMAT 999,999.00 HEADING "Available|Space MB"
    COLUMN autoextensible  FORMAT A10        HEADING "Auto|Extensible"
    SELECT   tablespace_name
    ,        file_name
    ,      ((maxbytes - bytes) / 1024) / 1024 AS available_space
    ,        autoextensible
    FROM     dba_data_files
    WHERE    tablespace_name IN ('SYSAUX','SYSTEM');

    It should return the following when you’re upgrading the Oracle Database 11g XE:

    Tablespace                                        Available   Auto
    Name       File Name                              Space MB    Extensible
    ---------- -------------------------------------- ----------- ----------
    SYSAUX     /u01/app/oracle/oradata/XE/sysaux.dbf    32,027.98 YES
    SYSTEM     /u01/app/oracle/oradata/XE/system.dbf       200.00 YES

Download APEX 5

  1. Download the APEX software from the Oracle web site. Assuming you download the software as the student user, you can save it in your Downloads directory.

    You should open a Terminal session and connect as the oracle user. If you’ve setup your instance correctly, you will need to first become the root user and then the oracle user. As the oracle user, you source the Oracle environment and copy the apex_x.x.x.zip file from the ~student/Downloads directory to the /u01/app/oracle directory.

  1. You copy the file from the student user’s Downloads directory with the following command:

    cp /home/student/Downloads/apex_x.x.x.zip /u01/app/oracle

  1. You unzip the copied apex_x.x.x.zip file (version 5.1.2 in this example) with the following command, and it will create a new apex directory as a subdirectory of the /u01/app/oracle directory.

    unzip apex_5.1.2.zip

Install APEX 5

  1. You should query the dba_users view to check the status of the apex_public_user and anonymous user accounts, like this:

    COLUMN username       FORMAT A18 HEADING "User Name"
    COLUMN account_status FORMAT A10 HEADING "Account|Status"
    SELECT   username
    ,        account_status
    FROM     dba_users
    WHERE    username IN ('APEX_PUBLIC_USER','ANONYMOUS');

    It should return the following when you’re upgrading the Oracle Database 11g XE but the anonymous user name may be open if you’ve previously unlocked it:

    		   Account
    User Name	   Status
    ------------------ ----------
    APEX_PUBLIC_USER   LOCKED
    ANONYMOUS	   LOCKED
  2. You can unlock the apex_public_user and anonymous accounts with the following statements:

    ALTER USER apex_public_user ACCOUNT UNLOCK;
    ALTER USER anonymous ACCOUNT UNLOCK;

  3. The installation uses the flows_files schema, which should be installed. You can verify the default and temporary tablespaces with the following query:

    COLUMN default_tablespace   FORMAT A22
    COLUMN temporary_tablespace FORMAT A22
    SELECT default_tablespace
    ,      temporary_tablespace
    FROM   dba_users
    WHERE  username = 'FLOWS_FILES';

    DEFAULT_TABLESPACE     TEMPORARY_TABLESPACE
    ---------------------- ----------------------
    SYSAUX		       TEMP
  4. Open a Terminal session, connect as the oracle user, source the Oracle environment file, and change your active directory to the /u01/app/oracle/apex directory, and open a SQL*Plus connection as the sys user. You need superuser privileges, which means you need to connect to the Oracle database with the “sys as sysdba” syntax.

    sqlplus sys as sysdba

    You can now install APEX 5.x.x by calling the following script with four parameters:

    @apexins.sql SYSAUX SYSAUX TEMP /i/

    It will take a couple minutes for the installation script to succeed. You will know that it is completed when you see the following message:

    Thank you for installing Oracle Application Express 5.1.2.00.09
     
    Oracle Application Express is installed in the APEX_050100 schema.
     
    The structure of the link to the Application Express administration services is as follows:
    http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
    http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
    http://host:port/apex/apex_admin     (Oracle REST Data Services)
     
    The structure of the link to the Application Express development interface is as follows:
    http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
    http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
    http://host:port/apex     (Oracle REST Data Services)
     
    PL/SQL procedure successfully completed.
     
    Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  5. After installing APEX 5, you can re-verify the APEX version by connecting as the system user through SQL*Plus and running the following query:

    COLUMN version_no        FORMAT A16 HEADING "Version Number"
    COLUMN api_compatibility FORMAT A16 HEADING "API|Compatibility"
    COLUMN patch_applied     FORMAT A14 HEADING "Patch Applied"
    SELECT *
    FROM   apex_release;

    It should return the following after upgrading with APEX 5:

    		 API
    Version Number	 Compatibility	  Patch Applied
    ---------------- ---------------- --------------
    5.1.2.00.09	 2016.08.24	  APPLIED

Configure APEX 5

  1. At this point, you need to set the internal password, which you can do by navigating to the /u01/app/oracle/apex directory. In that directory, you should open a SQL*Plus session as the sys user with the “sys as sysdba” privilege. Run the following script to set the APEX Administrator’s credentials:

    @apxchpwd.SQL

    It will prompt you for parameters, like so:

    ================================================================================
    This script can be used to change the password of an Application Express
    instance administrator. If the user does not yet exist, a user record will be
    created.
    ================================================================================
    Enter the administrator's username [ADMIN] ADMIN
    User "ADMIN" exists.
    Enter ADMIN's email [ADMIN] mclaughlinm@byui.edu
    Enter ADMIN's password [] 
    Changed password of instance administrator ADMIN.

  2. The next task requires you to run the apex_epg_config.sql script with one directory parameter. You should be connect to the sys user with the “sys as sysdba” privilege:

    @apex_epg_config.SQL /u01/app/oracle

    It will take a couple minutes to complete this script, and when it is complete it displays:

    . Loading images directory: /u01/app/oracle/apex/images

  3. While the default port for XML DB is 8080, you should confirm it with this query:

    SELECT dbms_xdb.gethttpport
    FROM   dual;

    It should return the following:

    GETHTTPPORT
    -----------
           8080

Connect to and use APEX 5

  • You can type the following URL into your local browser to get to the APEX 5 Administration page:

    http://localhost:8080/apex/apex_admin

    It should display the following login. The password is the one you entered when you ran the apxchpwd.sql script in the configuration section of this post.

    Oracle12cInstall04
    After you enter proper credentials, click the Sign in to Administration button to proceed. It should display the following APEX 5 home page.

    Oracle12cInstall04
    You can now work in APEX 5 Administration and setup a individual workspaces.

Cleanup APEX 4

Migrating functionality to APEX 5 is possible but reworking the existing design in the context of new features is better. After you have migrated your applications and upgraded your production instance, you can drop the APEX_040000 user/schema and remove any APEX 4 workspaces. This segment of shows you how to remove an APEX 4 workspace and drop the APEX_040000 user/schema.

  1. The following anonymous PL/SQL block will remove an APEX 4 workspace from a user schema. It’s designed for you to run it inside the target schema but you can change it to run it as the system user against multiple schemas.

    DECLARE
      /* Cursor for all APEX 4 tables and sequences. */
      CURSOR c IS
        SELECT   uo.object_type
        ,        uo.object_name
        FROM     user_objects uo
        WHERE    uo.object_name IN
                   ('DEPT'
                   ,'APEX$_WS_WEBPG_SECTION_HISTORY'
                   ,'APEX$_ACL'
                   ,'APEX$_WS_WEBPG_SECTIONS'
                   ,'APEX$_WS_ROWS'
                   ,'EMP'
                   ,'APEX$_WS_FILES'
                   ,'APEX$_WS_TAGS'
                   ,'APEX$_WS_LINKS'
                   ,'APEX$_WS_NOTES'
                   ,'DEMO_USERS'
                   ,'DEMO_CUSTOMERS'
                   ,'DEMO_ORDERS'
                   ,'DEMO_PRODUCT_INFO'
                   ,'DEMO_ORDER_ITEMS'
                   ,'DEMO_STATES'
                   ,'APEX$_WS_HISTORY'
                   ,'DEMO_USERS_SEQ'
                   ,'DEMO_PROD_SEQ'
                   ,'DEMO_ORD_SEQ'
                   ,'DEMO_ORDER_ITEMS_SEQ'
                   ,'DEMO_CUST_SEQ'
                   ,'CUSTOM_HASH'
                   ,'CUSTOM_AUTH')
        ORDER BY  uo.object_type DESC;
    BEGIN
      FOR i IN c LOOP
        IF i.object_type = '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;
    /
  2. You can then connect as the sys user with the “sys as sysdba” privilege and drop the APEX_040000 user/schema, like this:

    DROP USER apex_040000 CASCADE;

Written by maclochlainn

July 23rd, 2017 at 12:41 am

SQL Logic Overkill, again …

with 2 comments

It’s interesting to watch people try to solve problems. For example, the student is required to use a scalar subquery in a SQL lab exercise that I wrote. It should be a simple fix. The problem is structured with an incorrect foreign key value in an external CSV file and the restriction that you can not replace the value in the external CSV file. I hoped that students would see the easiest option was to write a scalar subquery in the SELECT clause to replace the value found in the external file. There’s even a hint about how to use a scalar subquery.

Students who are new to SQL can take very interesting approaches to solve problems. The flexibility of SQL can lead them to solve problems in interesting ways. While the following solution worked to solve the problem, it’s wrong on two levels:

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
INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          END AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN common_lookup cl
 WHERE    cl.common_lookup_table = 'TRANSACTION'
 AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
 AND      cl.common_lookup_type = 'CREDIT');

The CASE statement on lines 4 through 12 substitutes a value only when the source value is not a match. That means if the source file is ever correct a null value would become the transaction_type column value, which would make the statement fail because the transaction_type column is NOT NULL constrained in the target transaction table. Therefore, the logic of the student’s approach requires adding an ELSE clause to the CASE statement for the event that the source file is ever corrected. The modified CASE statement would be =the following:

4
5
6
7
8
9
10
11
12
13
14
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          ELSE
            tr.transaction_type
          END AS transaction_type

The second element of student thought at issue is the CROSS JOIN to the in-line view. It does one thing right and another wrong. It uses the unique key to identify a single row, which effectively adds all the columns for that one row to all rows returned from the external transaction_reversal table. The CROSS JOIN is a correct approach to adding values for computations to a query when you need those columns for computations. The problem with this CROSS JOIN logic may not be immediately obvious when you write it in ANSI SQL 1992 syntax, but it should become obvious when you replace the inline view with a Common Table Expression (CTE) in ANSI SQL 1999 syntax, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
INSERT INTO TRANSACTION
(WITH cte AS
 (SELECT *
  FROM   common_lookup
  WHERE  common_lookup_table = 'TRANSACTION'
  AND    common_lookup_column = 'TRANSACTION_TYPE'
  AND    common_lookup_type = 'CREDIT')
 SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        cte.common_lookup_id AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN cte);

Unfortunately, you would discover that Oracle Database 11g does not support the use of an ANSI SQL 1999 WITH clause inside as the source for an INSERT statement. Oracle Database 12c does support the use of the ANSI SQL 1999 WITH clause inside a subquery of an INSERT statement. That’s an “Oops!” for Oracle 11g because that means the Oracle database fails to meet the ANSI SQL 1999 compliance test. 😉 Great that they fixed it in Oracle 12c. While the nested query would work in Oracle as an ordinary query (outside of an INSERT statement). It raises the following error when you embed it in an INSERT statement:

ERROR AT line 20:
ORA-32034: unsupported USE OF WITH clause

The WITH clause does highlight a key problem with the idea of a CROSS JOIN in this situation. You don’t need all the columns from the common_lookup table. You only need the common_lookup_id column. That make the CROSS JOIN approach suboptimal if it worked.

The complex logic in the original approach is wasted. That’s true because the common_lookup_id value can be supplied to each row as the value from a scalar subquery. The scalar query runs once and the result is placed in the return set for each row. You implement the scalar subquery in the SELECT clause, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,       (SELECT common_lookup_id
          FROM   common_lookup
          WHERE  common_lookup_table = 'TRANSACTION'
          AND    common_lookup_column = 'TRANSACTION_TYPE'
          AND    common_lookup_type = 'CREDIT') AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr);

There really was no intent or logical outcome where the value from the original CASE statement would be different than the subquery’s common_lookup_id value. That fact makes adding an ELSE clause useless, and the solution viable though inefficient. Also, there was no need for the additional columns from the common_lookup table because they are unused. The subquery on lines 4 through 8 provides the optimal solution and improved efficiency.

Developers should ask themselves two questions when they write SQL:

  • If my logic is so elegant why do I need it to be so elegant?
  • Is there a simpler solution to provide the desired result set?

If there aren’t good answers to both questions, they should re-write it. I hope the examples answer questions and help folks solve problems.

Written by maclochlainn

July 9th, 2017 at 11:08 am

Oracle SQL Strip Quotes

without comments

Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:

SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message"
FROM   dual;

It will print:

Hello World!

As always, I hope this helps those looking for a solution.

Written by maclochlainn

June 18th, 2017 at 10:30 am

Reset Oracle Password

without comments

This blog entry shows you how to reset the system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the student user is the sudoer user.

After you sign on to the student user account, you open a Terminal session and you should see the following:

[student@localhost python]$

The oracle user account should be configured to prevent a login. So, you should use the su command or sudo command to open a terminal shell as the root user.

[student@localhost python]$ sudo sh
[sudo] password for student:

As the root user, you can login as the oracle user with the following command:

su - oracle

and, you should see the following prompt. You can see the present working directory (pwd) with the pwd command:

-bash-4.2$ pwd
/u01/app/oracle

You need to source the oracle_env.sh shell file created by the installation of the Oracle Database during the installation. You have two approaches to source the environment file, the first approach is with a dot (.), like

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

or, this

source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

The oracle_env.sh file contains the following:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$ORACLE_HOME/bin:$PATH

Now, you can connect to the Oracle Database as the internal user with the following command:

sqlplus / as sysdba

Once connected as the internal user, you can reset the system user’s password to “cangetin” with this command:

ALTER USER system IDENTIFIED BY cangetin;

At this point, you can also stop and start the database. You stop the database with this command:

shutdown immediate

You can then start the database with this command:

startup

After setting the system user password, sign out of SQL*Plus. Then, you can type two exits to return to the student user account, like this:

-bash-4.2$ exit
logout
sh-4.2# exit
exit
[student@localhost python]$

As always, I hope this helps those who need to reset the system password when they don’t know what it was to begin with.

Written by maclochlainn

February 21st, 2017 at 3:45 pm

Oracle Diagnostic Queries

without comments

It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.

For this example, you create the following table_struct object type and a table_list collection type:

/* Drop the types from most to least dependent. */
DROP TYPE table_list;
DROP TYPE table_struct;
 
/* Create the record type structure. */
CREATE OR REPLACE
  TYPE table_struct IS OBJECT
  ( table_name    VARCHAR2(30)
  , column_cnt    NUMBER
  , row_cnt       NUMBER );
/
 
/* Create the collection of a record type structure. */
CREATE OR REPLACE
  TYPE table_list IS TABLE OF table_struct;
/

The following listing function now reads all table names from the user_tables view. A subordinate cursor reads the user_tab_columns view for the number of columns in a table. A Native Dynamic SQL (NDS) cursor counts the number of rows in each tables found in the .

/* Create the listing function. */
CREATE OR REPLACE
FUNCTION listing RETURN table_list IS
 
  /* Variable list. */
  lv_column_cnt  NUMBER;
  lv_row_cnt     NUMBER;
 
  /* Declare a statement variable. */
  stmt  VARCHAR2(200);
 
  /* Declare a system reference cursor variable. */
  lv_refcursor  SYS_REFCURSOR;
  lv_table_cnt  NUMBER;
 
  /* Declare an output variable.  */
  lv_list  TABLE_LIST := table_list();
 
  /* Declare a table list cursor that excludes APEX tables. */
  CURSOR c IS
    SELECT table_name
    FROM   user_tables
    WHERE  table_name NOT IN
            ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS'
            ,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS'
            ,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY'
            ,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
            ,'DEMO_ORDER_ITEMS','DEMO_STATES');
 
  /* Declare a column count. */
  CURSOR cnt
  ( cv_table_name  VARCHAR2 ) IS
    SELECT   table_name
    ,        COUNT(column_id) AS cnt_columns
    FROM     user_tab_columns
    WHERE    table_name = cv_table_name
    GROUP BY table_name;
 
BEGIN
  /* Read through the data set of non-environment variables. */
  FOR i IN c LOOP
 
    /* Count the columns of a table. */
    FOR j IN cnt(i.table_name) LOOP
      lv_column_cnt := j.cnt_columns;
    END LOOP;
 
    /* Declare a statement. */
    stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
 
    /* Open the cursor and write set to collection. */
    OPEN lv_refcursor FOR stmt;
    LOOP
      FETCH lv_refcursor INTO lv_table_cnt;
      EXIT WHEN lv_refcursor%NOTFOUND; 
      lv_list.EXTEND;
      lv_list(lv_list.COUNT) := table_struct(
                                    table_name => i.table_name
                                  , column_cnt => lv_column_cnt
                                  , row_cnt    => lv_table_cnt );
    END LOOP;
  END LOOP;
 
  RETURN lv_list;
END;
/

The following query pulls the processed data set as the function’s result:

COL table_name   FORMAT A20     HEADING "Table Name"
COL column_cnt   FORMAT 9,999  HEADING "Column #"
COL row_cnt      FORMAT 9,999  HEADING "Row #"
SELECT table_name
,      column_cnt
,      row_cnt
FROM   TABLE(listing);

It returns the following result set:

Table Name	     Column #  Row #
-------------------- -------- ------
SYSTEM_USER		   11	   5
COMMON_LOOKUP		   10	  49
MEMBER			    9	  10
CONTACT 		   10	  18
ADDRESS 		   10	  18
STREET_ADDRESS		    8	  28
TELEPHONE		   11	  18
RENTAL			    8  4,694
ITEM			   14	  93
RENTAL_ITEM		    9  4,703
PRICE			   11	 558
TRANSACTION		   12  4,694
CALENDAR		    9	 300
AIRPORT 		    9	   6
ACCOUNT_LIST		    8	 200
 
15 rows selected.

As always, I hope this helps those trying to work with the Oracle database.

Written by maclochlainn

January 5th, 2017 at 7:28 pm

Install cx_Oracle for Python

without comments

This shows you how to install the cx_Oracle library for Python 2.7 on Fedora Linux. If Fedora has it on the server you can download it with the following yum command:

yum install -y cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm

Currently, you’ll get the following failure because it’s not available in the Fedora repository:

Loaded plugins: langpacks, refresh-packagekit
mysql-connectors-community                                      | 2.5 kB  00:00:00     
mysql-tools-community                                           | 2.5 kB  00:00:00     
mysql56-community                                               | 2.5 kB  00:00:00     
pgdg93                                                          | 3.6 kB  00:00:00     
updates/20/x86_64/metalink                                      | 2.3 kB  00:00:00     
No package cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm available.
Error: Nothing to do

You can download the cx_Oracle library from the Python web site. The cx_Oracle documentation qualifies module interfaces, objects, and connections. Assuming your Linux user’s name is student, you download the cx_Oracle library into the /home/student/Downloads directory. Then, you use the su or sudo command to become the root user.

As the root user, run the following yum command:

yum install -y ~student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm

You should see the following output:

Loaded plugins: langpacks, refresh-packagekit
Examining /home/student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm: cx_Oracle-5.2.1-1.x86_64
Marking /home/student/Downloads/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package cx_Oracle.x86_64 0:5.2.1-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=======================================================================================
 Package        Arch        Version      Repository                               Size
=======================================================================================
Installing:
 cx_Oracle      x86_64      5.2.1-1      /cx_Oracle-5.2.1-11g-py27-1.x86_64      717 k
 
Transaction Summary
=======================================================================================
Install  1 Package
 
Total size: 717 k
Installed size: 717 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : cx_Oracle-5.2.1-1.x86_64                                            1/1 
  Verifying  : cx_Oracle-5.2.1-1.x86_64                                            1/1 
 
Installed:
  cx_Oracle.x86_64 0:5.2.1-1                                                           
 
Complete!

After you install the cx_Oracle-5.2.1-1.x86_64 package, you can find the installed files with this rpm command:

rpm -ql cx_Oracle-5.2.1-1.x86_64

It lists:

/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info
/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/PKG-INFO
/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/SOURCES.txt
/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/dependency_links.txt
/usr/lib64/python2.7/site-packages/cx_Oracle-5.2.1-py2.7.egg-info/top_level.txt
/usr/lib64/python2.7/site-packages/cx_Oracle.so
/usr/share/doc/cx_Oracle-5.2.1
/usr/share/doc/cx_Oracle-5.2.1/BUILD.txt
/usr/share/doc/cx_Oracle-5.2.1/README.txt
/usr/share/doc/cx_Oracle-5.2.1/samples
/usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseChangeNotification.py
/usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseShutdown.py
/usr/share/doc/cx_Oracle-5.2.1/samples/DatabaseStartup.py
/usr/share/doc/cx_Oracle-5.2.1/samples/ReturnLongs.py
/usr/share/doc/cx_Oracle-5.2.1/samples/ReturnUnicode.py
/usr/share/doc/cx_Oracle-5.2.1/samples/RowsAsInstance.py
/usr/share/doc/cx_Oracle-5.2.1/test
/usr/share/doc/cx_Oracle-5.2.1/test/3kArrayDMLBatchError.py
/usr/share/doc/cx_Oracle-5.2.1/test/3kNumberVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/3kStringVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/ArrayDMLBatchError.py
/usr/share/doc/cx_Oracle-5.2.1/test/BooleanVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/Connection.py
/usr/share/doc/cx_Oracle-5.2.1/test/Cursor.py
/usr/share/doc/cx_Oracle-5.2.1/test/CursorVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/DateTimeVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/IntervalVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/LobVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/LongVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/NCharVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/NumberVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/ObjectVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/SessionPool.py
/usr/share/doc/cx_Oracle-5.2.1/test/SetupTest.sql
/usr/share/doc/cx_Oracle-5.2.1/test/StringVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/TestEnv.py
/usr/share/doc/cx_Oracle-5.2.1/test/TimestampVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/test.py
/usr/share/doc/cx_Oracle-5.2.1/test/test3k.py
/usr/share/doc/cx_Oracle-5.2.1/test/test_dbapi20.py
/usr/share/doc/cx_Oracle-5.2.1/test/uArrayDMLBatchError.py
/usr/share/doc/cx_Oracle-5.2.1/test/uConnection.py
/usr/share/doc/cx_Oracle-5.2.1/test/uCursor.py
/usr/share/doc/cx_Oracle-5.2.1/test/uCursorVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uDateTimeVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uIntervalVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uLobVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uLongVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uNumberVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uObjectVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uSessionPool.py
/usr/share/doc/cx_Oracle-5.2.1/test/uStringVar.py
/usr/share/doc/cx_Oracle-5.2.1/test/uTimestampVar.py

After you installed the software, you can test whether inside Python’s IDLE environment with the import command, like this:

Python 2.7.5 (default, Apr 10 2015, 08:09:05) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory

This error indicates that Oracle Client software isn’t installed, which is true in this case. I only installed the Oracle Database 11g Express Edition. You need to download the Oracle Client software and install it as the root user.

You download the Oracle Client software from the Oracle web site. Assuming your Linux user’s name is student, you download the cx_Oracle library into the /home/student/Downloads directory. Then, you use the su or sudo command to become the root user.

As the root user, run the following yum command:

yum install -y ~student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

You should see the following output:

Loaded plugins: langpacks, refresh-packagekit
Examining /home/student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
Marking /home/student/Downloads/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient11.2-basic.x86_64 0:11.2.0.4.0-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package
        Arch   Version
                      Repository                                           Size
================================================================================
Installing:
 oracle-instantclient11.2-basic
        x86_64 11.2.0.4.0-1
                      /oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64 179 M
 
Transaction Summary
================================================================================
Install  1 Package
 
Total size: 179 M
Installed size: 179 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64           1/1 
  Verifying  : oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64           1/1 
 
Installed:
  oracle-instantclient11.2-basic.x86_64 0:11.2.0.4.0-1                          
 
Complete!

You can create a Python program that checks your ability to connect to the Oracle database, like the following oracleConnection.py file:

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Print a message.
  print "Connected to the  Oracle " + db.version + " database."
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor. 
  db.close()

You can run this from the Linux command line with the following syntax:

python oracleConnection.py

It should return the following string:

Connected to the Oracle 11.2.0.2.0 database.

Now, you can create a Python program that reads data from the Oracle database. The following oracleString.py file reads a string literal from the pseudo table dual:

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT 'Hello world!' FROM dual")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

You can run this from the Linux command line with the following syntax:

python oracleString.py

It should return the following string:

Hello world!

Now, you can create a Python program that reads actual table data from the Oracle database (assuming you have a copy of my video store database). The following oracleTable.py file reads a string literal from the pseudo table dual:

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT item_title, item_subtitle FROM item")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0], row[1]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

You can run this from the Linux command line with the following syntax:

python oracleTable.py

It should return the following strings (only a subset of the returned values):

("Harry Potter and the Sorcer's Stone", 'Two-Disc Special Edition')
('Harry Potter and the Chamber of Secrets', 'Two-Disc Special Edition')
('Harry Potter and the Prisoner of Azkaban', 'Two-Disc Special Edition')
('Harry Potter and the Chamber of Secrets', None)
('Harry Potter and the Goblet of Fire', 'Widescreen Edition')
('Harry Potter and the Goblet of Fire', 'Two-Disc Special Edition')
('Harry Potter and the Order of the Phoenix', 'Widescreen Edition')
('The Lord of the Rings - Fellowship of the Ring', 'Widescreen Edition')
('The Lord of the Rings - Fellowship of the Ring', 'Platinum Series Special Extended Edition')
('The Lord of the Rings - Two Towers', 'Widescreen Edition')
('The Lord of the Rings - Two Towers', 'Platinum Series Special Extended Edition')
('The Lord of the Rings - The Return of the King', 'Widescreen Edition')
('The Lord of the Rings - The Return of the King', 'Platinum Series Special Extended Edition')
('Star Wars - Episode I', 'The Phantom Menace')
('Star Wars - Episode II', 'Attack of the Clones')
('Star Wars - Episode III', 'Revenge of the Sith')
('Star Wars - Episode IV', 'A New Hope')
('Star Wars - Episode V', 'The Empire Strikes Back')
('Star Wars - Episode VI', 'Return of the Jedi')

As always, I hope this helps others who want to work with Python and the Oracle database.

Written by maclochlainn

October 19th, 2016 at 1:47 am

Oracle Segment Fails

with 2 comments

The instance that I’ve built for my students in a Fedora VM is quite stable except for one feature. The feature is the hibernation process of the base operating system. Sometimes when the base operating system hibernates, it causes the Oracle shared memory segment to fail. When that happens you get the following error:

ERROR:
ORA-01034: ORACLE NOT available
ORA-27101: shared memory realm does NOT exist
Linux-x86_64 Error: 2: No such FILE OR DIRECTORY
Process ID: 0
SESSION ID: 0 Serial NUMBER: 0

I created the master sudoer account as the student user. The oracle user is configured so that you can’t log in to the Linux OS with it. To restart the instance you can do the following in a default Oracle 11g XE installation:

su - root

or, you can do this:

sudo sh

Then as the root user, you can sign on to the oracle user’s account by using the su command without a password, like:

su - oracle

As the user who installed the Oracle instance, you can connect to the database without a password after you source the environment file. The standard Oracle 11g XE environment file can be sources like this:

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

Alternatively, for my students there is a .bashrc file that they can manually source. It contains the following:

# Source global definitions
if [ -f /etc/bashrc ]; then
  . /etc/bashrc
fi
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
 
# Wrap sqlplus with rlwrap to edit prior lines with the
# up, down, left and right keys.
sqlplus()
{
  if [ "$RLWRAP" = "0" ]; then
    sqlplus "$@"
  else
    rlwrap sqlplus "$@"
  fi
}
 
# Set vi as a command line editor.
set -o vi

You can source the oracle user’s .bashrc account, like this:

. .bashrc

After you’ve sourced the environment, you can connect as the internal user with the following syntax:

sqlplus / AS sysdba

Connected as the internal user, run these two commands in sequence:

shutdown IMMEDIATE
startup

Then, you should be able to connect as the student user or another ordinary user with the following syntax:

CONNECT student/student

Hope this helps my students and those who want to know how to restart the Oracle instance.

Written by maclochlainn

October 16th, 2016 at 10:29 pm