Archive for the ‘Oracle 11g’ Category
Type & Body Basics
Object Types and Bodies Basics
Oracle Database 10g gave us a new way to write PL/SQL – object types. Object types are different from standard PL/SQL functions, procedures, and packages. While you can pin packages in memory, object types go one step further. You can instantiate them, which means you can start them, assign values to their variables, and put them into your PGA’s memory. Object types provide you with new challenges writing programs in the Oracle database.
Oracle Database 12c makes using object types simpler. That’s because Oracle Database 12c supports type evolution. Type evolution lets you change an object type when it has dependents. An object type’s dependents can be a table, another object type, function, procedure, or package. Oracle Database 12c also lets you white list the callers of an object type.
You define object types with variables and methods, like you define packages. Object type methods are either functions or procedures. You can implement object type functions and procedures as instance or static methods. An instance method works on the object type’s variable, whereas, static methods work like ordinary functions and procedures. That means static methods can’t access object type variables.
You learn how to define and implement basic object types and bodies in this article. This article shows you how to use and deploy objects and shows you how to implement the specialized CONSTRUCTOR functions.
The following declares a basic people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20)); 7 / |
The CREATE OR REPLACE is SQL syntax creates an object type, like you would create a PL/SQL function, procedure, or package. Lines 2 through 6 declare a four element people_obj object type, and the semicolon on line 6 acts as a statement terminator. The forward slash on line 7 executes the CREATE TYPE statement.
To most developers the foregoing syntax appears to declare a record data structure. There’s more to it than that. The CREATE TYPE syntax also creates an implicit constructor function. You can call the people_obj constructor with a list of parameter that matches both the list of element names and their data types. The call syntax supports both named and positional notation.
You can test the people_obj object type with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj(1,'John','Paul','Jones'); 3 BEGIN 4 dbms_output.put_line( people.first_name || ' ' 5 ||people.middle_name || ' ' 6 ||people.last_name); 7 END; 8 / |
Line 2 declares a variable of the object type with positional notation, and then it assigns an instance of the people_obj object type. On the right side of the assignment operator, a call to the constructor function creates an instance of the people_obj object type. Object construction has the highest order of precedence, which means it always creates the people_obj instance first.
Lines 4 through 6 print the values of the first, middle, and last name elements. These values are the instance values held by the peoplevariable. It prints:
John Paul Jones |
The following example shows you how to call the default people_obj constructor with named notation:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , middle_name => 'Paul' 4 , last_name => 'Jones' 5 , people_id => 2); 6 BEGIN ... 10 END; 11 / |
The named notation on lines 2 through 5 let us vary the order of the object attributes. Oracle raises the following exception if you pare the list of call parameters by removing one of them.
PLS-00306: wrong number or types of arguments in call to 'PEOPLE_OBJ' |
You can add one or more override constructor functions to the people_obj object type. The first override constructor example has two call parameters, and they are the first_name and last_name parameters.
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj 8 ( first_name VARCHAR2 9 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 10 INSTANTIABLE NOT FINAL; 11 / |
Lines 7 through 9 declare the override constructor function. This override constructor function doesn’t provide a value for the people_id attribute. The concept of an object having a unique identifier, or ID, is part of good object-oriented design practices.
An Oracle sequence can help us guarantee the unique ID. You can create a people_obj_s sequence for the people_obj with the following syntax:
SQL> CREATE SEQUENCE people_obj_s; |
You can use the people_obj_s sequence in the override constructor to generate the unique ID. The following code implements the modified people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 CONSTRUCTOR FUNCTION people_obj 4 ( first_name VARCHAR2 5 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 6 7 /* Set a counter variable using a sequence. */ 8 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 9 10 BEGIN 11 /* Create the instance with the default constructor. */ 12 self := people_obj( people_id => lv_people_obj_s 13 , first_name => first_name 14 , middle_name => NULL 15 , last_name => last_name ); 16 /* Return the current instance. */ 17 RETURN; 18 END people_obj; 19 END; 20 / |
Line 8 declares a local lv_people_obj_s variable, and it assigns the next value from the people_obj_s sequence. The local variable is necessary because you can’t put a call to the .NEXTVAL pseudo column inside a call to an object type constructor function.
The self key word on line 12 represents the instance of an object. You call the default constructor on lines 12 through 15. The default constructor takes a local variable, two parameter values, and a null value.
You can test the new people_obj with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , last_name => 'Jones'); 4 BEGIN 5 dbms_output.put_line( '['|| people.people_id ||'] ' 6 ||'['|| people.first_name ||'] ' 7 ||'['|| people.middle_name ||'] ' 8 ||'['|| people.last_name ||']'); 9 END; 10 / |
It prints
[1] [John] [] [Jones] |
Clearly, the handling of the middle_name attribute is suboptimal. Actually, it’s more or less a joke. However, it does give us an opportunity to show how to handle optional parameters in a constructor function.
You would change the people_obj object type by adding a parameter to the override constructor function, like
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj 8 ( first_name VARCHAR2 9 , middle_name VARCHAR2 DEFAULT NULL 10 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 11 INSTANTIABLE NOT FINAL; 12 / |
There are only two changes to the implementation of the people_obj object body. One changes the list of parameters in the constructor function. The other replaces the null assignment with a parameter value from the overriding constructor function.
Here’s the implementation of the new people_obj object body:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 CONSTRUCTOR FUNCTION people_obj 4 ( first_name VARCHAR2 5 , middle_name VARCHAR2 DEFAULT NULL 6 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 7 8 /* Set a counter variable using a sequence. */ 9 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 10 11 BEGIN 12 /* Create the instance with the default constructor. */ 13 self := people_obj( people_id => lv_people_obj_s 14 , first_name => first_name 15 , middle_name => middle_name 16 , last_name => last_name ); 17 /* Return the current instance. */ 18 RETURN; 19 END people_obj; 20 END; 21 / |
Line 5 specifies the middle_name parameter as an optional parameter. The optional parameter in the middle of the list can present a problem when you make call to it with positional notation. A call with named notation on the other hand works without a hitch. Line 15 replaces the null value with the middle_name parameter from the constructor function.
You can test the modified people_obj with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'John' 3 , last_name => 'Jones'); 4 5 BEGIN 6 dbms_output.put_line( '['|| people.people_id ||'] ' 7 ||'['|| people.first_name ||'] ' 8 ||'['|| people.middle_name ||'] ' 9 ||'['|| people.last_name ||']'); 10 END; 11 / |
It prints
[1] [John] [] [Jones] |
If you modify the constructor call on lines 2 through 4, as follows:
2 people PEOPLE_OBJ := people_obj( first_name => 'James' 3 , middle_name => 'Wilson' 4 , last_name => 'Jones'); |
It prints
[1] [John] [Wilson] [Jones] |
There are still several problems with the current people_obj object type. The largest shortfall is that there’s no traditional default constructor. In many object-oriented language, a default constructor is a null argument constructor. A null argument constructor let’s you position logic that all other constructors can leverage.
A sequence value is an example of logic that you can share across constructor functions. The following version of the people_obj object type declares a standard no argument constructor function:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER 4 , first_name VARCHAR2(20) 5 , middle_name VARCHAR2(20) 6 , last_name VARCHAR2(20) 7 , CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT 8 , CONSTRUCTOR FUNCTION people_obj 9 ( first_name VARCHAR2 10 , middle_name VARCHAR2 DEFAULT NULL 11 , last_name VARCHAR2 ) RETURN SELF AS RESULT) 12 INSTANTIABLE NOT FINAL; 13 / |
Line 7 holds the declaration of a no argument constructor. The following people_obj object type implements a no argument constructor. The object body also makes access to the sequence a feature available to all overriding constructors.
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS 3 4 /* Default constructor. */ 5 CONSTRUCTOR FUNCTION people_obj RETURN SELF AS RESULT IS 6 7 /* Set a counter variable using a sequence. */ 8 lv_people_obj_s NUMBER := people_obj_s.NEXTVAL; 9 10 BEGIN 11 /* Assign a sequence value to the instance. */ 12 self.people_id := lv_people_obj_s; 13 14 /* Return a constructed instance. */ 15 RETURN; 16 END; 17 18 /* Override constructor. */ 19 CONSTRUCTOR FUNCTION people_obj 20 ( first_name VARCHAR2 21 , middle_name VARCHAR2 DEFAULT NULL 22 , last_name VARCHAR2 ) RETURN SELF AS RESULT IS 23 24 /* Create a empty default instance. */ 25 people PEOPLE_OBJ := people_obj(); 26 27 BEGIN 28 /* Create the instance with the default constructor. */ 29 people.first_name := first_name; 30 people.middle_name := middle_name; 31 people.last_name := last_name; 32 33 /* Assign a local instance this instance. */ 34 self := people; 35 36 /* Return the current instance. */ 37 RETURN; 38 END people_obj; 39 END; 40 / |
The implementation of the no argument constructor is on lines 5 through 16. It uses the .NEXTVAL pseudo column to secure the next sequence value as a unique ID. Then, the constructor function returns a uniquely identified but otherwise empty object instance.
Line 25 creates a people_obj instance inside the declaration block of the overriding constructor. Inside the execution block, the overriding parameters are assigned to the attributes of the local instance. Ultimately, the local instance is assigned to the current instance and returned to any caller of the overriding constructor.
You call the modified overriding function with the following anonymous block:
SQL> DECLARE 2 people PEOPLE_OBJ := people_obj( first_name => 'Samuel' 3 , middle_name => 'Langhorne' 4 , last_name => 'Clemens'); 5 BEGIN 6 dbms_output.put_line( '['|| people.people_id ||'] ' 7 ||'['|| people.first_name ||'] ' 8 ||'['|| people.middle_name ||'] ' 9 ||'['|| people.last_name ||']'); 10 END; 11 / |
It prints
[3] [Samuel] [Langhorne] [Clemens] |
This article has shown you how to define and implement basic object types and bodies. It also has shown you how to work with default, no argument, and overriding constructor functions.
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.
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.
Fedora Install unixODBC
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.
External Tables + Merge
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.
Windows 10 Laptops
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.
Type Dependency Tree
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.
Substitutable Columns
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.
- 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
FINALdefault by making the base typeNOT FINAL. The example useBASE_Tas the generalized type or data structure of a substitutable column:CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; /
- After you create your base data structure, you create a specialized subtype. The following example creates a
PERSON_Ttype and accepts the default ofFINAL, 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)); /
- With a generalized
BASE_Ttype and a specializedPERSON_Tsubtype, you create aCUSTOMERtable with a substitutableCUSTOMER_NAMEcolumn. TheCUSTOMER_NAMEcolumn uses the generalizedBASE_Tdata type. You should also create aCUSTOMER_Ssequence 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;
- You can now populate the table with instances of the
BASE_Ttype or thePERSON_Tsubtype. The following inserts three rows into theCUSTOMERtable. 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'));
- 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 - This error message may lead you to call the
CUSTOMER_NAMEcolumn in a subquery and use theTABLEfunction 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. TheTABLEfunction 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 - The non-nested table error message should lead you to wrap the call to the
TREATfunction in a call to theCOLLECTfunction, 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 - 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 - 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 JOINto 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 - 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"(+))
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.
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.
Subscribe via RSS