MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle DBA’ tag

APEX New Workspace

without comments

After you install APEX or upgrade a base APEX, you need to create workspaces. These instructions show you how to create a workspace in APEX 18. You have two options, you can use the base url while specifying the INTERNAL workspace.

  1. You start the process by accessing the Oracle APEX through the standard form by entering the following URL:


    http://localhost:8080/apex

    • Workspace: INTERNAL
    • Username:  ADMIN
    • Password:  installation_system_password

  1. The better approach is to use the APEX administrator login:


    http://localhost:8080/apex/apex_admin

    • Username:  ADMIN
    • Password:  installation_system_password

  1. After logging into the Oracle Application Express (APEX) Administration console, you see the Administration home page.

  1. You click the Create Workspace button to start creating a work space.

  1. You enter a workspace name, ID number (greater than 100,000), and description and click the Next button to move to the next step.

  1. You choose whether to reuse an existing schema, which gives you more control. You then choose a schema from the list of available schemas. You do not use a password or schema size when you reuse a schema. You enter a password that has a capital letter, number, and special character that is not a % when you do not reuse a schema. You also need to choose a size. The default value is 100 megabytes. Click the Next button to move to the next step.

  1. This dialog identifies the workspace administrator. Click the Next button to move to the next step.

  1. This dialog confirms what you have done in the workflow. Click the Next button to move to the next step.

  1. This dialog tells you that you have successfully provisioned a workspace. Click the Done button to complete the workflow.

As always, I hope this helps those trying to figure out how to do something that should not be and is not actually hard to do.

Written by maclochlainn

August 25th, 2018 at 7:10 pm

Posted in Apex,Apex 18,Apex 4,Oracle,Oracle 11g,Oracle APEX

Tagged with ,

APEX 4 to 18 Upgrade

without comments

While preparing my new instance for class, which uses Oracle 11g XE and Fedora 27, I got caught by the Oracle instructions. I should have got caught but when you’re in a hurry sometimes you don’t slow down enough to read it properly. Actually, for me it was the uppercase APEX_HOME that threw me for a moment. It looks too much like an environment variable. Step 5 of the upgrading instructions says:

  1. Log back into SQL*Plus (as above) and configure the Embedded PL/SQL Gateway (EPG): 

    SQL> @apex_epg_config.SQL APEX_HOME

    [Note: APEX_HOME is the directory you specified when unzipping the file. For example, with Windows 'C:\'.]

Like an idiot, I typed it in literally without reading the note. That gave me this beautifully non-constructive error message:

DECLARE
*
ERROR AT line 1:
ORA-22288: FILE OR LOB operation FILEOPEN failed
No such FILE OR DIRECTORY
ORA-06512: AT "SYS.XMLTYPE", line 296
ORA-06512: AT line 16

I tried to launch APEX for a more meaningful error message, and it displayed:

Then, I used Google to find a few very old and not very helpful solutions because I wasn’t slowing down to read them. However, clearly if there are only old solutions the problem must be what I typed. I checked my old APEX 4 to APEX 5 blog post and then I understood the APEX_HOME. The documentation should really use APEX_UPGRADE_UNZIP_PATH to avoid having to read the detailed note.

After changing the generic APEX_PATH parameter to the physical directory directory where I stored the unzipped file content /u01/app/oracle/apex, like this:

SQL> @apex_epg_config.SQL /u01/app/oracle/apex

and, it worked as designed.

It important to note that the APEX upgrade works perfectly. Outstanding work by a well motivated and thorough development team. I can only quibble with making Step 5 simpler. As always, I hope this helps others.

Written by maclochlainn

August 25th, 2018 at 4:49 pm

Posted in Apex 18,Apex 4,Oracle,Oracle 11g

Tagged with

Fedora Install unixODBC

without comments

Encountered a problem while running the RODBC library from the R prompt as the root user, as follows:

> install.packages('RODBC')

It failed with the following library dependency:

checking for unistd.h... yes
checking sql.h usability... no
checking sql.h presence... no
checking for sql.h... no
checking sqlext.h usability... no
checking sqlext.h presence... no
checking for sqlext.h... no
configure: error: "ODBC headers sql.h and sqlext.h not found"
ERROR: configuration failed for package ‘RODBC’
* removing ‘/usr/lib64/R/library/RODBC’
 
The downloaded source packages are in
	‘/tmp/RtmpdT1gay/downloaded_packages’
Updating HTML index of packages in '.Library'
Making 'packages.html' ... done
Warning message:
In install.packages("RODBC") :
  installation of package ‘RODBC’ had non-zero exit status

I installed unixODBC-devel and unixODBC-gui-qt libraries to fix the library dependencies with the following command as the root user:

yum install -y unixODBC*

It should show you the following when it installs the unixODBC-devel and unixODBC-gui-qt libraries:

Loaded plugins: langpacks, refresh-packagekit
You need to be root to perform this command.
[student@localhost ~]$ su - root
Password: 
Last login: Fri Apr 20 21:18:56 PDT 2018 on pts/1
[root@localhost ~]# yum install -y unixODBC*
Loaded plugins: langpacks, refresh-packagekit
cassandra/signature                                         |  819 B  00:00     
cassandra/signature                                         | 2.9 kB  00:00 !!! 
fedora/20/x86_64/metalink                                   | 3.3 kB  00:00     
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
updates/20/x86_64/metalink                                  | 3.1 kB  00:00     
Package unixODBC-2.3.2-4.fc20.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package unixODBC-devel.x86_64 0:2.3.2-4.fc20 will be installed
---> Package unixODBC-gui-qt.x86_64 0:0-0.8.20120105svn98.fc20 will be installed
--> Processing Dependency: libQtNetwork.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64
--> Processing Dependency: libQtGui.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64
--> Processing Dependency: libQtCore.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64
--> Processing Dependency: libQtAssistantClient.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64
--> Running transaction check
---> Package qt.x86_64 1:4.8.6-30.fc20 will be installed
--> Processing Dependency: qt-common = 1:4.8.6-30.fc20 for package: 1:qt-4.8.6-30.fc20.x86_64
--> Processing Dependency: qt-settings for package: 1:qt-4.8.6-30.fc20.x86_64
---> Package qt-assistant-adp.x86_64 0:4.6.3-6.fc20 will be installed
---> Package qt-x11.x86_64 1:4.8.6-30.fc20 will be installed
--> Processing Dependency: libmng.so.1()(64bit) for package: 1:qt-x11-4.8.6-30.fc20.x86_64
--> Processing Dependency: libclucene.so.3()(64bit) for package: 1:qt-x11-4.8.6-30.fc20.x86_64
--> Running transaction check
---> Package clucene09-core.x86_64 0:0.9.21b-13.fc20 will be installed
---> Package libmng.x86_64 0:1.0.10-12.fc20 will be installed
---> Package qt-common.noarch 1:4.8.6-30.fc20 will be installed
---> Package qt-settings.noarch 0:20-18.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package              Arch       Version                      Repository   Size
================================================================================
Installing:
 unixODBC-devel       x86_64     2.3.2-4.fc20                 updates      55 k
 unixODBC-gui-qt      x86_64     0-0.8.20120105svn98.fc20     fedora      624 k
Installing for dependencies:
 clucene09-core       x86_64     0.9.21b-13.fc20              updates     300 k
 libmng               x86_64     1.0.10-12.fc20               fedora      166 k
 qt                   x86_64     1:4.8.6-30.fc20              updates     4.7 M
 qt-assistant-adp     x86_64     4.6.3-6.fc20                 fedora      257 k
 qt-common            noarch     1:4.8.6-30.fc20              updates     5.8 k
 qt-settings          noarch     20-18.fc20                   updates      19 k
 qt-x11               x86_64     1:4.8.6-30.fc20              updates      12 M
 
Transaction Summary
================================================================================
Install  2 Packages (+7 Dependent packages)
 
Total download size: 18 M
Installed size: 56 M
Downloading packages:
(1/9): libmng-1.0.10-12.fc20.x86_64.rpm                     | 166 kB  00:01     
(2/9): clucene09-core-0.9.21b-13.fc20.x86_64.rpm            | 300 kB  00:01     
(3/9): qt-4.8.6-30.fc20.x86_64.rpm                          | 4.7 MB  00:00     
(4/9): qt-common-4.8.6-30.fc20.noarch.rpm                   | 5.8 kB  00:00     
(5/9): qt-settings-20-18.fc20.noarch.rpm                    |  19 kB  00:00     
(6/9): qt-assistant-adp-4.6.3-6.fc20.x86_64.rpm             | 257 kB  00:00     
(7/9): qt-x11-4.8.6-30.fc20.x86_64.rpm                      |  12 MB  00:01     
(8/9): unixODBC-devel-2.3.2-4.fc20.x86_64.rpm               |  55 kB  00:00     
(9/9): unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64.rpm  | 624 kB  00:01     
--------------------------------------------------------------------------------
Total                                              4.1 MB/s |  18 MB  00:04     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : libmng-1.0.10-12.fc20.x86_64                                 1/9 
  Installing : qt-settings-20-18.fc20.noarch                                2/9 
  Installing : 1:qt-common-4.8.6-30.fc20.noarch                             3/9 
  Installing : 1:qt-4.8.6-30.fc20.x86_64                                    4/9 
  Installing : clucene09-core-0.9.21b-13.fc20.x86_64                        5/9 
  Installing : 1:qt-x11-4.8.6-30.fc20.x86_64                                6/9 
  Installing : qt-assistant-adp-4.6.3-6.fc20.x86_64                         7/9 
  Installing : unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64              8/9 
  Installing : unixODBC-devel-2.3.2-4.fc20.x86_64                           9/9 
  Verifying  : clucene09-core-0.9.21b-13.fc20.x86_64                        1/9 
  Verifying  : unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64              2/9 
  Verifying  : 1:qt-x11-4.8.6-30.fc20.x86_64                                3/9 
  Verifying  : 1:qt-4.8.6-30.fc20.x86_64                                    4/9 
  Verifying  : qt-settings-20-18.fc20.noarch                                5/9 
  Verifying  : 1:qt-common-4.8.6-30.fc20.noarch                             6/9 
  Verifying  : unixODBC-devel-2.3.2-4.fc20.x86_64                           7/9 
  Verifying  : qt-assistant-adp-4.6.3-6.fc20.x86_64                         8/9 
  Verifying  : libmng-1.0.10-12.fc20.x86_64                                 9/9 
 
Installed:
  unixODBC-devel.x86_64 0:2.3.2-4.fc20                                          
  unixODBC-gui-qt.x86_64 0:0-0.8.20120105svn98.fc20                             
 
Dependency Installed:
  clucene09-core.x86_64 0:0.9.21b-13.fc20                                       
  libmng.x86_64 0:1.0.10-12.fc20                                                
  qt.x86_64 1:4.8.6-30.fc20                                                     
  qt-assistant-adp.x86_64 0:4.6.3-6.fc20                                        
  qt-common.noarch 1:4.8.6-30.fc20                                              
  qt-settings.noarch 0:20-18.fc20                                               
  qt-x11.x86_64 1:4.8.6-30.fc20                                                 
 
Complete!

After installing the unixODBC-devel and unixODBC-gui-qt libraries, I installed the RODBC library from the R prompt, having launched the R environment as the root user:

> install.packages('RODBC')

Installing the RODBC library should install cleanly and generate the following output:

Installing package into ‘/usr/lib64/R/library’
(as ‘lib’ is unspecified)
trying URL 'http://cran.cnr.berkeley.edu/src/contrib/RODBC_1.3-15.tar.gz'
Content type 'application/x-gzip' length 1163967 bytes (1.1 MB)
==================================================
downloaded 1.1 MB
 
* installing *source* package ‘RODBC’ ...
** package ‘RODBC’ successfully unpacked and MD5 sums checked
checking for gcc... gcc -m64 -std=gnu99
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc -m64 -std=gnu99 accepts -g... yes
checking for gcc -m64 -std=gnu99 option to accept ISO C89... none needed
checking how to run the C preprocessor... gcc -m64 -std=gnu99 -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking sql.h usability... yes
checking sql.h presence... yes
checking for sql.h... yes
checking sqlext.h usability... yes
checking sqlext.h presence... yes
checking for sqlext.h... yes
checking for library containing SQLTables... -lodbc
checking for SQLLEN... yes
checking for SQLULEN... yes
checking size of long... 8
configure: creating ./config.status
config.status: creating src/Makevars
config.status: creating src/config.h
** libs
gcc -m64 -std=gnu99 -I/usr/include/R -DNDEBUG -I. -I/usr/local/include    -fpic  -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches  -m64 -mtune=generic  -c RODBC.c -o RODBC.o
gcc -m64 -std=gnu99 -shared -L/usr/lib64/R/lib -Wl,-z,relro -o RODBC.so RODBC.o -lodbc -L/usr/lib64/R/lib -lR
installing to /usr/lib64/R/library/RODBC/libs
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
  converting help for package ‘RODBC’
    finding HTML links ... done
    RODBC-internal                          html  
    RODBC-package                           html  
    odbc                                    html  
    odbcClose                               html  
    odbcConnect                             html  
    odbcDataSources                         html  
    odbcGetInfo                             html  
    odbcSetAutoCommit                       html  
    setSqlTypeInfo                          html  
    sqlColumns                              html  
    sqlCopy                                 html  
    sqlDrop                                 html  
    sqlFetch                                html  
    sqlQuery                                html  
    sqlSave                                 html  
    sqlTables                               html  
    sqlTypeInfo                             html  
** building package indices
** installing vignettes
** testing if installed package can be loaded
* DONE (RODBC)
Making 'packages.html' ... done
 
The downloaded source packages are in
	‘/tmp/RtmpdT1gay/downloaded_packages’
Updating HTML index of packages in '.Library'
Making 'packages.html' ... done

I hope that helps anybody who runs into the library dependency problems.

Written by maclochlainn

April 20th, 2018 at 10:43 pm

Posted in Fedora,LAMP,Linux,Oracle,Oracle 11g,Oracle 12c,R,Red Hat

Tagged with ,

External Tables + Merge

without comments

This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.

Step #1 : Create a virtual directory

You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a /u01/app/oracle/upload file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user.

The syntax for these steps is:

CREATE DIRECTORY upload AS '/u01/app/oracle/upload';
GRANT READ, WRITE ON DIRECTORY upload TO student;

Step #2 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /u01/app/oracle/upload directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step.

Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data.

'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292',
'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1531',
'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635',
'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686',
'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0682',

Step #3 : Reconnect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

CONNECT student@xe

Step #4 : Run the script that creates tables and sequences

Copy the following into a create_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

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

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP 
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP 
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create normalized kingdom table.
CREATE TABLE kingdom
( kingdom_id    NUMBER
, kingdom_name  VARCHAR2(20)
, population    NUMBER);
 
-- Create a sequence for the kingdom table.
CREATE SEQUENCE kingdom_s1;
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             NUMBER
, knight_name           VARCHAR2(24)
, kingdom_allegiance_id NUMBER
, allegiance_start_date DATE
, allegiance_end_date   DATE);
 
-- Create a sequence for the knight table.
CREATE SEQUENCE knight_s1;
 
-- Create external import table.
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR2(20)
, population            NUMBER
, knight_name           VARCHAR2(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BAFFLE      'UPLOAD':'kingdom_import.bad'
      DISCARDFILE 'UPLOAD':'kingdom_import.dis'
      LOGFILE     'UPLOAD':'kingdom_import.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('kingdom_import.csv'))
REJECT LIMIT UNLIMITED;

Step #5 : Test your access to the external table

There a number of things that could go wrong with setting up an external table, such as file permissions. Before moving on to the balance of the steps, you should test what you’ve done. Run the following query from the student account to check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6
7
8
9

COL kingdom_name FORMAT A8 HEADING "Kingdom|Name"
COL population   FORMAT 99999999 HEADING "Population"
COL knight_name  FORMAT A30 HEADING "Knight Name"
SELECT   kingdom_name
,        population
,        knight_name
,        TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date
,        TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date
FROM     kingdom_knight_import;

Step #6 : Create the upload procedure

Copy the following into a create_upload_procedure.sql file within a directory of your choice. Then, run it as the student account.

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

-- Create a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_kingdom IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO kingdom target
  USING (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) SOURCE
  ON (target.kingdom_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET kingdom_name = SOURCE.kingdom_name
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( kingdom_s1.nextval
  , SOURCE.kingdom_name
  , SOURCE.population);
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO knight target
  USING (SELECT   kn.knight_id
         ,        kki.knight_name
         ,        k.kingdom_id
         ,        kki.allegiance_start_date AS start_date
         ,        kki.allegiance_end_date AS end_date
         FROM     kingdom_knight_import kki INNER JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population LEFT JOIN knight kn 
         ON       k.kingdom_id = kn.kingdom_allegiance_id
         AND      kki.knight_name = kn.knight_name
         AND      kki.allegiance_start_date = kn.allegiance_start_date
         AND      kki.allegiance_end_date = kn.allegiance_end_date) SOURCE
  ON (target.kingdom_allegiance_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET allegiance_start_date = SOURCE.start_date
  ,          allegiance_end_date = SOURCE.end_date
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( knight_s1.nextval
  , SOURCE.knight_name
  , SOURCE.kingdom_id
  , SOURCE.start_date
  , SOURCE.end_date);
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Step #7 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

EXECUTE upload_kingdom;

Step #8 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

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

-- Check the kingdom table.
SELECT * FROM kingdom;
 
-- Format Oracle output.
COLUMN knight_id             FORMAT 999 HEADING "Knight|ID #"
COLUMN knight_name           FORMAT A23 HEADING "Knight Name"
COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|Allegiance|ID #"
COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date"
COLUMN allegiance_end_date   FORMAT A11 HEADING "Allegiance|End Date"
SET PAGESIZE 999
 
-- Check the knight table.
SELECT   knight_id
,        knight_name
,        kingdom_allegiance_id
,        TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date
,        TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date
FROM     knight;

It should display the following information:

KINGDOM_ID KINGDOM_NAME         POPULATION
---------- -------------------- ----------
         1 Narnia                    42100
         2 Narnia                    77600
         3 Camelot                   15200
 
                                  Kingdom
Knight                         Allegiance Allegiance  Allegiance
  ID # Knight Name                   ID # Start Date  End Date
------ ----------------------- ---------- ----------- -----------
     1 Peter the Magnificent            2 20-MAR-1272 19-JUN-1292
     2 Edmund the Just                  2 20-MAR-1272 19-JUN-1292
     3 Susan the Gentle                 2 20-MAR-1272 19-JUN-1292
     4 Lucy the Valiant                 2 20-MAR-1272 19-JUN-1292
     5 Peter the Magnificent            1 12-APR-1531 31-MAY-1531
     6 Edmund the Just                  1 12-APR-1531 31-MAY-1531
     7 Susan the Gentle                 1 12-APR-1531 31-MAY-1531
     8 Lucy the Valiant                 1 12-APR-1531 31-MAY-1531
     9 King Arthur                      3 10-MAR-0631 12-DEC-0686
    10 Sir Lionel                       3 10-MAR-0631 12-DEC-0686
    11 Sir Bors                         3 10-MAR-0631 12-DEC-0635
    12 Sir Bors                         3 10-MAR-0640 12-DEC-0686
    13 Sir Galahad                      3 10-MAR-0631 12-DEC-0686
    14 Sir Gawain                       3 10-MAR-0631 12-DEC-0686
    15 Sir Tristram                     3 10-MAR-0631 12-DEC-0686
    16 Sir Percival                     3 10-MAR-0631 12-DEC-0686
    17 Sir Lancelot                     3 30-SEP-0670 12-DEC-0682

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

Written by maclochlainn

March 11th, 2018 at 9:16 pm

Posted in Oracle,Oracle 11g,Oracle 12c,pl/sql,sql

Tagged with ,

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

Posted in Oracle,Oracle 11g,Oracle 12c,Windows 10

Tagged with ,

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

Posted in Oracle,Oracle 11g,Oracle 12c,Oracle XE,pl/sql,sql

Tagged with ,

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.

  6. 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
  7. 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
  8. 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
  9. 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
  10. 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

Posted in Oracle,Oracle 11g,Oracle 12c,sql,SQL*Plus

Tagged with ,

PostgreSQL Identity Columns

without comments

It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.

Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.

MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:

CREATE TABLE auto
( id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, text_field   VARCHAR(30)  NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.

If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:

CREATE TABLE auto
( auto_id     NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001)
, text_field  VARCHAR2(30)
, CONSTRAINT  auto_pk PRIMARY KEY (auto_id));

You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.

CREATE TABLE auto
( id          SERIAL      CONSTRAINT auto_pk PRIMARY KEY
, text_field  VARCHAR(30));
 
ALTER SEQUENCE auto_id_seq RESTART WITH 1001;

You can see the table and assigned sequence with the following command in PostgreSQL:

\d+ auto

It should display:

                                                      Table "public.auto"
   Column   |         Type          |                     Modifiers                     | Storage  | Stats target | Description 
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
 id         | integer               | not null default nextval('auto_id_seq'::regclass) | plain    |              | 
 text_field | character varying(30) |                                                   | extended |              | 
Indexes:
    "auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no

As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.

Written by maclochlainn

August 4th, 2017 at 12:52 am

Posted in MySQL,Oracle 12c,PostgreSQL

Tagged with , , , , ,

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

Posted in Apex,Apex 4,Apex 5,Oracle,Oracle 11g,Oracle APEX,Oracle Apex 4,Oracle Apex 5

Tagged with ,

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

Posted in Oracle,Oracle 11g,Oracle 12c,Oracle XE,sql,Uncategorized

Tagged with ,

« Older Entries
Newer Entries »