Preprocessing External Tables
A question that comes up now and again is there a way in Oracle Database 11g Express Edition to mimic some behavior in the Oracle Standard or Enterprise editions. Many of these questions arise because developers want to migrate a behavior they’ve implemented in Java to the Express Edition. Sometimes the answer is no but many times the answer is yes. The yes answers come with a how.
This article answers the question: “How can I read an operating systems’ file directory with out an embedded Java Virtual Machine (JVM)?” These developers have read or implemented logic like that found in my earlier “Using DBMS_JAVA to Read External Files” article. The answer is simple. You need to use a preprocessing script inside an external table. That’s what you will learn in this article, but if you’re not familiar with external tables you should read this other “External Tables” article.
External tables let you access plain text files with SQL*Loader or Oracle’s proprietary Data Pump files. You typically create external tables with Oracle Data Pump when you’re moving large data sets between database instances.
External tables use Oracle’s virtual directories. An Oracle virtual directory is an internal reference in the data dictionary. A virtual directory maps a unique directory name to a physical directory on the local operating system. Virtual directories were simple before Oracle Database 12c gave us the multitenant architecture. In a multitenant database there are two types of virtual directories. One services the schemas of the Container Database (CDB) and it’s in the CDB’s SYS schema. The other services the schemas of a Pluggable Database (PDB) and it’s in the ADMIN schema for the PDB.
You can create a CDB virtual database as SYSTEM user with the following syntax in Windows:
SQL> CREATE DIRECTORY upload AS 'C:\Data\Upload'; |
or, like this in Linux or Unix:
SQL> CREATE DIRECTORY upload AS '/u01/app/oracle'; |
There are some subtle differences between these two statements. Windows directories or folders start with a logical drive letter, like C:\, D:\, and so forth. Linux and Unix directories start with a mount point like /u01.
As you can read in the “External Tables” article, you need to change the ownership of external files and directories to the oracle user and, default, oracle user’s default dba group. Likewise, you should change the privilege of the containing directory to 755 (owner has read, write, and execute privileges; and group and others have read and execute privileges.
The balance of this article is broken into two pieces configuring a working external table with preprocessing and troubleshooting cartridge errors.
External Tables with Preprocessing Example
There are xxx database steps to creating this example. The first database step requires you create three virtual directories. The syntax for the three statements is:
SQL> CREATE DIRECTORY upload AS '/u01/app/oracle/upload'; SQL> CREATE DIRECTORY LOG AS '/u01/app/oracle/log'; SQL> CREATE DIRECTORY preproc AS '/u01/app/oracle/preproc'; |
The upload directory hosts the files you want to discover for upload. The log directory hosts the log files for the external tables. The preproc directory hosts the executable program, which generates a list of files currently in the upload directory.
After creating the virtual directories or before creating them, you should create the physical directories in the Linux operating system. The virtual directories can only point to something when it actually exists. Moreover, they work like Oracle’s synonyms that point to other objects in the database. The physical files need to be in a directory tree that is navigable by the oracle user and the oracle user and it’s default primary dba group needs to own them.
You can use the following command to change ownership when you’re the root user:
# chown –R oracle:dba /u01/app/oracle |
The second database step requires that you grant privileges on the virtual directories to the student user. You can do that with the following syntax:
SQL> GRANT read ON DIRECTORY upload; SQL> GRANT read, WRITE ON DIRECTORY LOG; SQL> GRANT read, EXECUTE ON DIRECTORY preproc; |
The upload directory requires read-only privileges. The log directory requires read and write privileges. The read privileges let it find files and the write privilege lets it append to log files when they already exist. The preproc directory requires read and execute privileges. The read privilege is the same as that explained earlier. The execute privilege lets you run the preprocessing program file.
The third database step requires creating an external file with preprocessing. The following script creates the sample table:
SQL> CREATE TABLE directory_list 2 ( file_name VARCHAR2(60)) 3 ORGANIZATION EXTERNAL 4 ( TYPE oracle_loader 5 DEFAULT DIRECTORY preproc 6 ACCESS PARAMETERS 7 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 8 PREPROCESSOR preproc:'list2dir.sh' 9 BADFILE 'LOG':'dir.bad' 10 DISCARDFILE 'LOG':'dir.dis' 11 LOGFILE 'LOG':'dir.log' 12 FIELDS TERMINATED BY ',' 13 OPTIONALLY ENCLOSED BY "'" 14 MISSING FIELD VALUES ARE NULL) 15 LOCATION ('list2dir.sh')) 16 REJECT LIMIT UNLIMITED; |
Line 5 designates the default directory as preproc because the location of the executable file should be in the preproc directory. Line 8 designates that there is a preprocessing step, and it identifies the virtual directory and physical file name inside single quotes. Line 15 identifies the source file for the external table, which is an executable program.
Next, you need to create the bash file to get and return a directory list. Before you write that file, you need to understand that preprocessing script files don’t inherit a $PATH environment variable from Oracle.
That probably means you might have tried to create a simple bash shell command like the following in a list2dir.sh file.
ls /u01/app/oracle/upload | find . -type f | ls *csv | sed -e 's/\.\///' |
When you test this file by calling it from SQL, like this:
SQL> SELECT * FROM directory_list; |
It raises the following exception stack:
SELECT * FROM directory_list * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error KUP-04095: preprocessor command /u01/app/oracle/preprocess/list2dir.sh encountered error "/u01/app/oracle/preprocess/list2dir.sh: line 1: ls: No such file or directory |
The reason isn’t immediately clear to some developers. The significant error is:
ls: No such file or directory |
The error message indicates that a call through Oracle’s OCI call interface cannot find the location of the ls program. That occurs because there is no $PATH variable set a list of values that points to the /usr/bin directory where you find the ls program. You need to prepend /usr/bin before the ls, find, and sed programs.
/usr/bin/ls /u01/app/oracle/upload | /usr/bin/find . -type f | /usr/bin/ls *csv | /usr/bin/sed -e 's/\.\///' |
Create a list2dir.sh file in the /u01/app/oracle/preproc directory with the preceding command line. Then, make sure oracle is the owner with a primary dba group and the privileges are 755 on the file. The command to set the privileges is:
# chmod –R 755 /u01/app/oracle/preproc.sh |
Having completed that Linux operating system step you should probably put some files in the upload directory. You can create empty files with the touch command at the linux command line for this example.
The fourth database step lets you query the external table, which runs the preprocessing program and returns its results as values in the table:
SQL> CREATE * FROM directory_list; |
It should return something like this:
FILE_NAME ------------------------------ character.csv transaction_upload2.csv transaction_upload.csv |
As always, this is written to help those solve problems.
External Tables
Oracle Database 9i introduced external tables. You can create external tables to load plain text files by using Oracle SQL*Loader. Alternatively, you can create external tables that load and unload files by using Oracle Data Pump. This article demonstrates both techniques.
You choose external tables that use Oracle SQL*Loader when you want to import plain text files. There are three types of plain text files. They are comma-separated value (CSV), tab-separated value (TSV), and position specific text files.
External tables that use Oracle Data Pump don’t work with plain text files. They work with an Oracle proprietary format. That means you load source files previously created by an Oracle Data Pump export. You typically create external tables with Oracle Data Pump when you’re moving large data sets between database instances.
External tables use Oracle’s virtual directories. An Oracle virtual directory is an internal reference in the data dictionary. A virtual directory maps a unique directory name to a physical directory on the local operating system. Virtual directories were simple before Oracle Database 12c gave us the multitenant architecture. In a multitenant database there are two types of virtual directories. One services the schemas of the Container Database (CDB) and it’s in the CDB’s SYS schema. The other services the schemas of a Pluggable Database (PDB) and it’s in the ADMIN schema for the PDB.
You can create a CDB virtual directory as SYSTEM user with the following syntax in Windows:
SQL> CREATE DIRECTORY upload AS 'C:\Data\Upload'; |
or, like this in Linux or Unix:
SQL> CREATE DIRECTORY upload AS '/u01/app/oracle'; |
There are some subtle differences between these two statements. Windows directories or folders start with a logical drive letter, like C:\, D:\, and so forth. Linux and Unix directories start with a mount point like /u01.
One of the subtle differences is directory and file ownership. You can change ownership for a directory in Windows as the Administrator account. The change makes the directory publically accessible, and that’s probably fine for a test database. After such a change, the Oracle user can find the external file even when parent directories aren’t navigable. Although, a production database on Windows would requires more skill at setting and restricting file permissions.
Linux and Unix directories require that the oracle user can navigate the tree from the mount point to the target physical directory. Also, you must designate the ownership of external files as the same as the Oracle Database user. Assuming a standard install of the Oracle Database 11g XE instance, you would issue the following shell command as the root user to change file ownership and access privileges:
# chown –R oracle:dba /u01/app/oracle/upload # chmod –R 755 /u01/app/oracle/upload |
After you create the virtual directory, you must grant privileges or a role to the user that defines the external table. While data and log files should be separated, this example assumes they co-exist in the same directory.
The following statement grants read privilege for the data file and write privileges for the log files to a CDB user. You should run this statement as the system user.
SQL> GRANT read, WRITE ON DIRECTORY upload TO c##importer; |
or, like this in non-multitenant database or PDB user:
SQL> GRANT read, WRITE ON DIRECTORY upload TO importer; |
The last preparation steps require a plain text file in the physical directory. Let’s create a CSV file of key Avenger characters, and name it the avenger.csv file.
The avenger.csv file holds the following values:
1,'Anthony','Stark','Iron Man' 2,'Thor','Odinson','God of Thunder' 3,'Steven','Rogers','Captain America' 4,'Bruce','Banner','Hulk' 5,'Clinton','Barton','Hawkeye' 6,'Natasha','Romanoff','Black Widow' |
You create the external table after creating the virtual directory, granting read and write privileges on the virtual directory, and creating an external physical file. The syntax for the CREATE TABLE statement of an external table is very similar to the syntax of an ordinary table. The difference between the two types of tables is a clause. An internal table has a STORAGE clause, while an external table has an ORGANIZATION EXTERNAL clause.
The following creates the avenger table as an external table:
SQL> CREATE TABLE avenger 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_loader 8 DEFAULT DIRECTORY upload 9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS TERMINATED BY ',' 15 OPTIONALLY ENCLOSED BY "'" 16 MISSING FIELD VALUES ARE NULL) 17 LOCATION ('avenger.csv')) 18 REJECT LIMIT UNLIMITED; |
Lines 1 through 5 create the columns of the avenger table. Lines 6 through 17 contain the ORGANIZATION EXTERNAL clause. Line 7 designates the external table as managed by the Oracle SQL*Loader utility. Line 8 sets the default virtual directory. Lines 11 through 12 set the bad, discard, and log file location. The bad and discard files keep all that can’t be read. The log file keeps all rows read by a query against the avenger table.
You also have the option of making all reads automatic parallel. You simply add a PARALLEL clause, like this:
19 PARALLEL; |
A simple query with SQL*Plus formatting lets us test whether the avenger table works. The query to display all columns of all rows is:
SQL> COLUMN first_name FORMAT A10 SQL> COLUMN last_name FORMAT A10 SQL> COLUMN character_name FORMAT A15 SQL> SELECT * FROM avenger; |
Yields the following formatted output:
AVENGER_ID FIRST_NAME LAST_NAME CHARACTER_NAME
---------- ---------- ---------- ---------------
1 Anthony Stark Iron Man
2 Thor Odinson God of Thunder
3 Steven Rogers Captain America
4 Bruce Banner Hulk
5 Clinton Barton Hawkeye
6 Natasha Romanoff Black Widow
6 rows selected. |
It’s possible to redefine the avenger table to use either relative or fixed positional columns. You change the ACCESS PARAMETERS clause on lines 9 through 16 to make this change.
The following ACCESS PARAMETERS clause runs across lines 9 through 19 and creates relative position definition:
9 ACCESS PARAMETERS 10 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII 11 BADFILE 'UPLOAD':'avenger.bad' 12 DISCARDFILE 'UPLOAD':'avenger.dis' 13 LOGFILE 'UPLOAD':'avenger.log' 14 FIELDS 15 MISSING FIELD VALUES ARE NULL 16 ( avenger_id CHAR(4) 17 , first_name CHAR(20) 18 , last_name CHAR(20) 19 , character_name CHAR(4))) |
You can change from the relative position, to a fixed position by changing lines 16 through 19. The change for fixed length strings is:
16 ( avenger_id POSITION 1:4 17 , first_name POSITION 5:24 18 , last_name POSITION 25:44 19 , character_name POSITION 45:64)) |
Having worked with the Oracle SQL*Loader version of external tables, lets create one that uses Oracle Data Pump. Assuming we keep the same data structure, drop the avenger table, and create a catalog managed avenger_internal table.
This statement creates the avenger_internal table:
SQL> CREATE TABLE avenger_internal 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)); |
To avoid writing six INSERT statements, you can write one INSERT statement with a query against the SQL*Loader avenger table. The syntax for that INSERT statement is:
SQL> INSERT INTO avenger_internal 2 SELECT * FROM avenger; |
With an internally managed table, you create an avenger_export table that uses Oracle Data Pump like this:
SQL> CREATE TABLE avenger_export 2 ORGANIZATION EXTERNAL 3 ( TYPE oracle_datapump 4 DEFAULT DIRECTORY upload 5 LOCATION ('avenger_export.dmp')) AS 6 SELECT avenger_id 7 , first_name 8 , last_name 9 , character_name 10 FROM avenger_internal; |
The CREATE TABLE statement exports data to the avenger_export.dmp file immediately. You must drop and recreate the avenger_export table to get a fresh extract of the avenger_internal table’s data. You must also remove the previous avenger_export.dmp file before you try to recreate the avenger_export table.
You raise the following error when you fail to remove the previous export file:
CREATE TABLE avenger_export * ERROR AT line 1: ORA-29913: error IN executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-11012: FILE avenger_export.dmp IN /u01/... already EXISTS |
This is a simple example with only four columns. You might think you can use the SELECT * as the SELECT-list of the query on lines 6 through 10. If you’re running Oracle Database 12c, you can use the shorter syntax, but if you’re running Oracle Database 11g you can’t. If you attempt it in an Oracle Database 11g instance, the CREATE TABLE statement returns the following error:
ERROR at line 6:
ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE |
You create an avenger_import table with another twist on this now familiar Oracle SQL syntax. The CREATE TABLE statement is:
SQL> CREATE TABLE avenger_import 2 ( avenger_id NUMBER 3 , first_name VARCHAR2(20) 4 , last_name VARCHAR2(20) 5 , character_name VARCHAR2(20)) 6 ORGANIZATION EXTERNAL 7 ( TYPE oracle_datapump 8 DEFAULT DIRECTORY up2load 9 LOCATION ('avenger_export.dmp')); |
Like the export process, the import process happens immediately when the CREATE TABLE statement runs. A query against the avenger_import table would show you the original six rows we started with in the plain text files.
This article has introduced Oracle external tables. It has shown you how to import plain text files with SQL*Loader. It has also shown you how to export files from tables.
MongoDB Update Statement
While discussing the pros and cons of MongoDB, my students wanted to know how to update a specific element in a collection. Collections are like tables in relational databases.
You create the users collection by inserting rows like this:
db.users.insert(
[
{ contact_account: "CA_20170321_0001"
, first_name: "Jonathan"
, middle_name: "Eoin"
, last_name: "MacGregor"
, addresses:
{
street_address: ["1111 Broadway","Suite 101"]
, city: "Oakland"
, state: "CA"
, zip: "94607"
}
}
, { contact_account: "CA_20170328_0001"
, first_name: "Remington"
, middle_name: "Alain"
, last_name: "Dennison"
, addresses:
{
street_address: ["2222 Broadway","Suite 121"]
, city: "Oakland"
, state: "CA"
, zip: "94607"
}
}
]) |
You can query the results with the db.users.find({}) command, or you can query the formatted results with the following command:
db.users.find({}).pretty() |
You can provide a simple update of middle_name element of a given collection element with the findAndModify() function. The following queries the users collection to find the JSON middle_name element where the contact_account value is equal to the “CA_20170330_0001” string.
db.users.findAndModify(
{ query: { contact_account: "CA_20170328_0001" }
, update: { $set: { middle_name: "Alan" }}
, upsert: false }) |
After changing the middle_name value from “Alain” to “Alan”, you can query the single element of the collection with the following:
db.users.find({ contact_account: "CA_20170328_0001" }).pretty() |
It should return the following:
{
"_id" : ObjectId("5bd7f69ba135dda917665de7"),
"contact_account" : "CA_20170328_0001",
"first_name" : "Remington",
"middle_name" : "Alan",
"last_name" : "Dennison",
"addresses" : {
"street_address" : [
"2222 Broadway",
"Suite 121"
],
"city" : "Oakland",
"state" : "CA",
"zip" : "94607"
}
} |
You can replace the addresses string element value a collection of elements with the following findAndModify() function:
db.users.findAndModify(
{ query: { contact_account: "CA_20170328_0001" }
, update:
{ $set:
{ addresses:
[
{
active_status: true
, start_date : new Date("2018-10-30")
, street_address: ["2222 Broadway","Suite 121"]
, city: "Oakland"
, state: "CA"
, zip: "94607"
}
, {
active_status: false
, start_date: new Date("2017-10-01")
, end_date : new Date("2018-10-29")
, street_address: ["2222 Broadway","Suite 121"]
, city: "Oakland"
, state: "CA"
, zip: "94607"
}
]
}
}
, upsert: false }) |
You can re-query the modified result set with find() function with the same query syntax as used previously. This looks for a specific member element in the collection by matching the contact_account name’s value pair. It is the same as the one used earlier in this blog post.
db.users.find({ contact_account: "CA_20170328_0001" }).pretty() |
It should return the following:
{
"_id" : ObjectId("5bd7f69ba135dda917665de7"),
"contact_account" : "CA_20170328_0001",
"first_name" : "Remington",
"middle_name" : "Alan",
"last_name" : "Dennison",
"addresses" : [
{
"active_status" : true,
"start_date" : ISODate("2018-10-30T00:00:00Z"),
"street_address" : [
"2222 Broadway",
"Suite 121"
],
"city" : "Oakland",
"state" : "CA",
"zip" : "94607"
},
{
"active_status" : false,
"start_date" : ISODate("2017-10-01T00:00:00Z"),
"end_date" : ISODate("2018-10-29T00:00:00Z"),
"street_address" : [
"2222 Broadway",
"Suite 121"
],
"city" : "Oakland",
"state" : "CA",
"zip" : "94607"
}
]
} |
As always, I hope this helps someone.
Linux mongod Service
The installation of MongoDB doesn’t do everything for you. In fact, the first time you start the mongod service, like this as the root user or sudoer user with the command:
service mongod start |
A sudoer user will be prompted for their password, like
A typical MongoDB instance raises the following errors:
Redirecting to /bin/systemctl start mongod.service [student@localhost cit425]$ mongo MongoDB shell version v3.4.11 connecting to: mongodb://127.0.0.1:27017 MongoDB server version: 3.4.11 Server has startup warnings: 2018-10-29T10:51:57.515-0600 I STORAGE [initandlisten] 2018-10-29T10:51:57.515-0600 I STORAGE [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine 2018-10-29T10:51:57.515-0600 I STORAGE [initandlisten] ** See http://dochub.mongodb.org/core/prodnotes-filesystem 2018-10-29T10:51:58.264-0600 I CONTROL [initandlisten] 2018-10-29T10:51:58.264-0600 I CONTROL [initandlisten] ** WARNING: Access control is not enabled for the database. 2018-10-29T10:51:58.264-0600 I CONTROL [initandlisten] ** Read and write access to data and configuration is unrestricted. 2018-10-29T10:51:58.264-0600 I CONTROL [initandlisten] 2018-10-29T10:51:58.265-0600 I CONTROL [initandlisten] 2018-10-29T10:51:58.265-0600 I CONTROL [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 15580 processes, 64000 files. Number of processes should be at least 32000 : 0.5 times number of files. |
You can fix this by following the MongoDB instructions for the Unix ulimit Settings, which will tell you to create a mongod file in the /etc/systemd/system directory. You should create this file as the root superuser. This is what you should put in the file:
[Unit] Description=MongoDB Documentation=man:mongo [Service] # Other directives omitted # (file size) LimitFSIZE=infinity # (cpu time) LimitCPU=infinity # (virtual memory size) LimitAS=infinity # (locked-in-memory size) LimitMEMLOCK=infinity # (open files) LimitNOFILE=64000 # (processes/threads) LimitNPROC=64000 |
Then, you should be able to restart the mongod service without any warnings with this command:
service mongod restart |
As always, I hope this helps somebody.
Fedora 27 Screen Saver
Just back from Oracle OpenWorld and somebody desperately wants to know how to disable the 5 minute default screen saver setting in the KDE environment. OK, you navigate to the Fedora “f” in the lower left hand corner and choose System Settings, like:
In the System Setting menu page, select Desktop Behavior:
In the Desktop Behavior dialog, select Screen Locking. Change the default to something large like 90 for 1 and half hours.
As always, I hope that solves your problem.
Fedora SQL*Developer
After you download SQL Developer 18 on Fedora 27, you can install it with the yum utility, like
yum install -y sqldeveloper-18.2.0.183.1748-1.noarch.rpm |
The installation should generate the following log file:
Last metadata expiration check: 2:26:23 ago on Sat 25 Aug 2018 07:10:16 PM MDT. Dependencies resolved. ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: sqldeveloper noarch 18.2.0.183.1748-1 @commandline 338 M Transaction Summary ================================================================================================ Install 1 Package Total size: 338 M Installed size: 420 M Downloading Packages: Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : sqldeveloper-18.2.0.183.1748-1.noarch 1/1 Running scriptlet: sqldeveloper-18.2.0.183.1748-1.noarch 1/1 Verifying : sqldeveloper-18.2.0.183.1748-1.noarch 1/1 Installed: sqldeveloper.noarch 18.2.0.183.1748-1 Complete! |
After you install SQL Developer, you won’t be able to launch it. Attempts to launch it won’t raise an error message either. The problem is that there is a post-installation step, which requires you to configure the product.conf file.
You can see the error by navigating to the /opt/sqldeveloper directory. You will find the sqldeveloper.sh file in that directory. You will see the error when you run the command as the root user from the command-line interface (CLI), as follows:
/opt/sqldeveloper/sqldeveloper.sh |
Oracle SQL Developer Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved. Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /root/.sqldeveloper/18.2.0/product.conf |
You can find the Oracle home by searching for the rt.jar file as the root user. You use the following find command syntax from the / topmost directory.
find . -name rt.jar |
On Fedora 27, you should see the following absolute file name:
./usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-1.b10.fc27.x86_64/jre/lib/rt.jar |
You discard the /jre/lib portion of the directory path and the rt.jar file name to get the Java home’s fully qualified path. This should update the product.conf file but if you have to change it manually you should edit the following file:
/root/.sqldeveloper/18.2.0/product.conf |
You need to configure the SetJavaHome parameter value in the product.conf file. The SetJavaHome parameter needs to point to the Java home directory on your Fedora instance. It should look like this:
# # By default, the product launcher will search for a JDK to use, and if none # can be found, it will ask for the location of a JDK and store its location # in this file. If a particular JDK should be used instead, uncomment the # line below and set the path to your preferred JDK. # SetJavaHome /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-1.b10.fc27.x86_64 |
It’s possible that an attempt to launch SQL Developer by another user may have copied the product.conf file into a local directory. You should change those manually by editing their respective product.conf files. Assuming you attempted to launch SQL Developer by a student user before you changed the root user’s copy of the SQL Developer’s product.conf file.
APEX New Workspace
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.
- 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
- Workspace:
- The better approach is to use the APEX administrator login:
http://localhost:8080/apex/apex_admin
- Username:
ADMIN - Password:
installation_system_password
- Username:
- After logging into the Oracle Application Express (APEX) Administration console, you see the Administration home page.
- You click the Create Workspace button to start creating a work space.
- You enter a workspace name, ID number (greater than 100,000), and description and click the Next button to move to the next step.
- 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.
- This dialog identifies the workspace administrator. Click the Next button to move to the next step.
- This dialog confirms what you have done in the workflow. Click the Next button to move to the next step.
- 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.
APEX 4 to 18 Upgrade
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:
- Log back into SQL*Plus (as above) and configure the Embedded PL/SQL Gateway (EPG):
SQL> @apex_epg_config.SQL APEX_HOME
[Note:
APEX_HOMEis 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.
MySQL 5.7.* and mysqli
After installing MySQL 5.7.22 and PHP 7.1.17 on Fedora 27, you need to install the mysqli library. You need to verify if the mysqli library is installed. You can do that with the following mysqli_check.php program:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <html> <header> <title>Check mysqli Install</title> </header> <body> <?php if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) { print 'mysqli not installed.'; } else { print 'mysqli installed.'; } ?> </script> </body> </html> |
You test preceding PHP program with the following URL in a browser:
http://localhost/mysqli_check.php |
If the mysqli program isn’t installed, you can install it as follows by opening the yum interactive shell:
[root@localhost html]# yum shell Last metadata expiration check: 1:26:46 ago on Wed 22 Aug 2018 08:05:50 PM MDT. > remove php-mysql No match for argument: php-mysql Error: No packages marked for removal. > install php-mysqlnd > run ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-mysqlnd x86_64 7.1.20-1.fc27 updates 246 k Upgrading: php x86_64 7.1.20-1.fc27 updates 2.8 M php-cli x86_64 7.1.20-1.fc27 updates 4.2 M php-common x86_64 7.1.20-1.fc27 updates 1.0 M php-fpm x86_64 7.1.20-1.fc27 updates 1.5 M php-json x86_64 7.1.20-1.fc27 updates 73 k php-pdo x86_64 7.1.20-1.fc27 updates 138 k php-pgsql x86_64 7.1.20-1.fc27 updates 135 k Transaction Summary ================================================================================================ Install 1 Package Upgrade 7 Packages Total download size: 10 M Is this ok [y/N]: y |
After you type y and the return key, you should see a detailed log of the installation. Click the link below to see the yum installation log detail.
Display detailed console log →
Downloading Packages: (1/8): php-pdo-7.1.20-1.fc27.x86_64.rpm 214 kB/s | 138 kB 00:00 (2/8): php-mysqlnd-7.1.20-1.fc27.x86_64.rpm 325 kB/s | 246 kB 00:00 (3/8): php-json-7.1.20-1.fc27.x86_64.rpm 342 kB/s | 73 kB 00:00 (4/8): php-pgsql-7.1.20-1.fc27.x86_64.rpm 1.1 MB/s | 135 kB 00:00 (5/8): php-common-7.1.20-1.fc27.x86_64.rpm 1.0 MB/s | 1.0 MB 00:01 (6/8): php-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 2.8 MB 00:00 (7/8): php-fpm-7.1.20-1.fc27.x86_64.rpm 1.6 MB/s | 1.5 MB 00:00 (8/8): php-cli-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 4.2 MB 00:01 ------------------------------------------------------------------------------------------------ Total 4.2 MB/s | 10 MB 00:02 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: php-json-7.1.20-1.fc27.x86_64 1/1 Upgrading : php-json-7.1.20-1.fc27.x86_64 1/15 Upgrading : php-common-7.1.20-1.fc27.x86_64 2/15 Upgrading : php-pdo-7.1.20-1.fc27.x86_64 3/15 Upgrading : php-cli-7.1.20-1.fc27.x86_64 4/15 Upgrading : php-7.1.20-1.fc27.x86_64 5/15 Installing : php-mysqlnd-7.1.20-1.fc27.x86_64 6/15 Upgrading : php-pgsql-7.1.20-1.fc27.x86_64 7/15 Upgrading : php-fpm-7.1.20-1.fc27.x86_64 8/15 Running scriptlet: php-fpm-7.1.20-1.fc27.x86_64 8/15 Cleanup : php-7.1.17-1.fc27.x86_64 9/15 Cleanup : php-cli-7.1.17-1.fc27.x86_64 10/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-fpm-7.1.17-1.fc27.x86_64 11/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-pgsql-7.1.17-1.fc27.x86_64 12/15 Cleanup : php-pdo-7.1.17-1.fc27.x86_64 13/15 Cleanup : php-common-7.1.17-1.fc27.x86_64 14/15 Cleanup : php-json-7.1.17-1.fc27.x86_64 15/15 Running scriptlet: php-json-7.1.17-1.fc27.x86_64 15/15 Running as unit: run-ra7f965317617476a93de3931549ab242.service Running as unit: run-r1272914e525d42798b0c3a76d4e2ba67.service Verifying : php-mysqlnd-7.1.20-1.fc27.x86_64 1/15 Verifying : php-pdo-7.1.20-1.fc27.x86_64 2/15 Verifying : php-common-7.1.20-1.fc27.x86_64 3/15 Verifying : php-json-7.1.20-1.fc27.x86_64 4/15 Verifying : php-pgsql-7.1.20-1.fc27.x86_64 5/15 Verifying : php-fpm-7.1.20-1.fc27.x86_64 6/15 Verifying : php-cli-7.1.20-1.fc27.x86_64 7/15 Verifying : php-7.1.20-1.fc27.x86_64 8/15 Verifying : php-cli-7.1.17-1.fc27.x86_64 9/15 Verifying : php-common-7.1.17-1.fc27.x86_64 10/15 Verifying : php-fpm-7.1.17-1.fc27.x86_64 11/15 Verifying : php-json-7.1.17-1.fc27.x86_64 12/15 Verifying : php-pdo-7.1.17-1.fc27.x86_64 13/15 Verifying : php-pgsql-7.1.17-1.fc27.x86_64 14/15 Verifying : php-7.1.17-1.fc27.x86_64 15/15 Installed: php-mysqlnd.x86_64 7.1.20-1.fc27 Upgraded: php.x86_64 7.1.20-1.fc27 php-cli.x86_64 7.1.20-1.fc27 php-common.x86_64 7.1.20-1.fc27 php-fpm.x86_64 7.1.20-1.fc27 php-json.x86_64 7.1.20-1.fc27 php-pdo.x86_64 7.1.20-1.fc27 php-pgsql.x86_64 7.1.20-1.fc27 Last metadata expiration check: 2:02:29 ago on Wed 22 Aug 2018 08:05:50 PM MDT. |
After you install the mysqli library, you exit the yum interactive shell with the quit command as shown:
> quit Leaving Shell The downloaded packages were saved in cache until the next successful transaction. You can remove cached packages by executing 'dnf clean packages'. |
You can now retest by re-running the mysqli_check.php program with the following URL:
http://localhost/mysqli_check.php |
Image processing is not generally installed by default. You should use the following yum command to install the PHP Image processing library:
yum install -y php-gd |
Or, you can use dnf (Dandified yum), like:
dnf install -y php-gd |
Click the link below to see the yum installation log detail.
Display detailed console log →
Dependencies resolved. ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-gd x86_64 7.1.20-1.fc27 updates 89 k Transaction Summary ================================================================================================ Install 1 Package Total download size: 89 k Installed size: 200 k Is this ok [y/N]: y Downloading Packages: php-gd-7.1.20-1.fc27.x86_64.rpm 96 kB/s | 89 kB 00:00 ------------------------------------------------------------------------------------------------ Total 55 kB/s | 89 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-gd-7.1.20-1.fc27.x86_64 1/1 Verifying : php-gd-7.1.20-1.fc27.x86_64 1/1 Installed: php-gd.x86_64 7.1.20-1.fc27 Complete! |
If you encounter an error trying to render an image like this:
Call to undefined function imagecreatefromstring() in ... |
The php-gd package is not enabled. You can verify the contents of the php-gd package with the following rpm command on Fedora or CentOS:
rpm -ql php-gd |
On PHP 7.1, it should return:
/etc/php-zts.d/20-gd.ini /etc/php.d/20-gd.ini /usr/lib/.build-id /usr/lib/.build-id/50 /usr/lib/.build-id/50/11f0ec947836c6b0d325084841c05255197131 /usr/lib/.build-id/b0/10bf6f48ca6c0710dcc5777c07059b2acece77 /usr/lib64/php-zts/modules/gd.so /usr/lib64/php/modules/gd.so |
Then, you might choose to follow some obsolete note from ten or more years ago to include gd.so in your /etc/php.ini file. That’s not necessary.
The most common reason for incurring this error is tied to migrating old PHP 5 code forward. Sometimes folks used logic like the following to print a Portable Network Graphics (png) file stored natively in a MySQL BLOB column:
header('Content-Type: image/x-png'); imagepng(imagecreatefromstring($image)); |
If it was stored as a Portable Network Graphics (png) file, all you needed was:
header('Content-Type: image/x-png'); print $image; |
As always, I hope this helps those looking for a solution.
Docker on Fedora 27
This walks you through the steps to install the Community Edition of Docker on Fedora 27. If you’ve been living under a rock for a few years, Docker is an open source container virtualization software.
Docker or Docker Community Edition is a necessary step if you want to install something like Microsoft’s SQL Server on Fedora because only these are supported platforms:
- Red Hat Enterprise Linux 7.3 or 7.4
- SUSE Linux Enterprise Server V12 SP2
- Ubuntu 16.04
- Docker Engine 1.8+
The first step requires you to add the docker-ce repo to your instance with he curl utility, which should already be installed in most cases. You can check whether the curl utility is available with the which command, like:
which -a curl |
If installed it should return the following:
/usr/bin/curl /bin/curl |
You add the docker-ce repo with the following curl command:
sudo curl -o /etc/yum.repos.d/docker-ce.repo https://download.docker.com/linux/fedora/docker-ce.repo |
You should see something like the following if it was successful:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2544 100 2544 0 0 2544 0 0:00:01 --:--:-- 0:00:01 12913 |
Next, you can install docker-ce with the following yum command or if you prefer use the dnf utility:
sudo yum install -y docker-ce |
It should produce a log file like the one provided, which you can see by clicking the Display detailed console link that expands the page to show you the console details.
Display detailed console →
Docker CE Stable - x86_64 30 kB/s | 5.9 kB 00:00
Last metadata expiration check: 0:00:00 ago on Thu 05 Jul 2018 06:56:36 PM MDT.
Dependencies resolved.
================================================================================================
Package Arch Version Repository Size
================================================================================================
Installing:
docker-ce x86_64 18.03.1.ce-1.fc27 docker-ce-stable 35 M
Installing dependencies:
container-selinux noarch 2:2.65-1.gitbf5b26b.fc27 updates 41 k
Transaction Summary
================================================================================================
Install 2 Packages
Total download size: 35 M
Installed size: 150 M
Downloading Packages:
(1/2): container-selinux-2.65-1.gitbf5b26b.fc27.noarch.rpm 133 kB/s | 41 kB 00:00
(2/2): docker-ce-18.03.1.ce-1.fc27.x86_64.rpm 20 MB/s | 35 MB 00:01
------------------------------------------------------------------------------------------------
Total 14 MB/s | 35 MB 00:02
warning: /var/cache/dnf/docker-ce-stable-8cf73e63b8c72e93/packages/docker-ce-18.03.1.ce-1.fc27.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 621e9f35: NOKEY
Importing GPG key 0x621E9F35:
Userid : "Docker Release (CE rpm) <docker@docker.com>"
Fingerprint: 060A 61C5 1B55 8A7F 742B 77AA C52F EB6B 621E 9F35
From : https://download.docker.com/linux/fedora/gpg
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 1/2
Running scriptlet: container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 1/2
neverallow check failed at /var/lib/selinux/targeted/tmp/modules/100/base/cil:9006
(neverallow base_typeattr_7 unlabeled_t (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/200/container/cil:1489
(allow spc_t unlabeled_t (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/sandboxX/cil:866
(allow sandbox_x_domain exec_type (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/virt/cil:1669
(allow virtd_lxc_t exec_type (file (entrypoint)))
<root>
allow at /var/lib/selinux/targeted/tmp/modules/100/virt/cil:2060
(allow svirt_sandbox_domain exec_type (file (entrypoint)))
Failed to generate binary
/usr/sbin/semodule: Failed!
Running scriptlet: docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Installing : docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Running scriptlet: docker-ce-18.03.1.ce-1.fc27.x86_64 2/2
Running as unit: run-rb873740b51a44061abb2c9e197871e4b.service
Verifying : docker-ce-18.03.1.ce-1.fc27.x86_64 1/2
Verifying : container-selinux-2:2.65-1.gitbf5b26b.fc27.noarch 2/2
Installed:
docker-ce.x86_64 18.03.1.ce-1.fc27 container-selinux.noarch 2:2.65-1.gitbf5b26b.fc27
Complete! |
After installing docker-ce, you can check it with the following systemctl command:
sudo systemctl status docker |
It should show service is disabled (default) and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://docs.docker.com |
This indicates everything installed correctly, now you need to enable the docker service with this systemctl command:
sudo systemctl enable docker |
It should show that the docker service is enabled and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://docs.docker.com |
You start the docker service with this systemctl command:
sudo systemctl start docker |
It should show that the service is enabled and inactive:
● docker.service - Docker Application Container Engine
Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2018-07-05 19:22:25 MDT; 4s ago
Docs: https://docs.docker.com
Main PID: 107768 (dockerd)
Tasks: 16
Memory: 35.8M
CPU: 140ms
CGroup: /system.slice/docker.service
├─107768 /usr/bin/dockerd
└─107772 docker-containerd --config /var/run/docker/containerd/containerd.toml
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.350914727-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.352583385-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.352718174-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.353419088-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.632193965-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.823732094-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.893601936-06:00
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.894351396-06:00
Jul 05 19:22:25 localhost.localdomain systemd[1]: Started Docker Application Container Engine.
Jul 05 19:22:25 localhost.localdomain dockerd[107768]: time="2018-07-05T19:22:25.907087735-06:00
lines 1-22/22 (END) |
A CNTL+C dismisses the open log file. As usual, I hope this helps those trying to accomplish the task for the first time.
Subscribe via RSS