Type Getters & Setters
Object Types with Getters and Setters
This article is for you when you know the basics about how you work Oracle’s object types. It teaches you how to write effective getters, setters, comparators, and static methods. Please read my Object Types & Bodies Basic article if you’re not sure how to work with object types.
Getters access an object instance and return values from an instance variable. Along with getters, you have setters. Setters let you assign a new value to an instance variable. Formally, getters are accessor methods and setters are mutator methods. PL/SQL implements getters as functions and setters as procedures. After all a PL/SQL procedure is like a function that returns a void data type in Java.
The Object Types & Bodies Basic article introduces a people_obj object type. This article extends the behavior of the people_obj type. Extends is a funny word because it can have different meanings in object-oriented programming. Here, extends means to add functionality.
The first things we’ll add are getters and setters for all the attributes of the object instance. We need to add them to the object type and body because Oracle implements objects like it does packages. The object type defines the published functions and procedures. The object body implements the published functions and procedures.
Here’s the new people_obj type with getters and setters:
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 , MEMBER FUNCTION get_people_id RETURN NUMBER 13 , MEMBER FUNCTION get_first_name RETURN VARCHAR2 14 , MEMBER FUNCTION get_middle_name RETURN VARCHAR2 15 , MEMBER FUNCTION get_last_name RETURN VARCHAR2 16 , MEMBER PROCEDURE set_first_name (pv_first_name VARCHAR2) 17 , MEMBER PROCEDURE set_middle_name (pv_first_name VARCHAR2) 18 , MEMBER PROCEDURE set_last_name (pv_first_name VARCHAR2)) 19 INSTANTIABLE NOT FINAL; 20 / |
The new getters and setters are on lines 12 through 18. The closing parenthesis for the list of attributes, functions, and procedures moves from line 11 to line 18. While there are four attributes in the people_obj type and four getters for those attributes, there are only three setters. The reason for the difference is simple. The people_id attribute is a unique identifier. You should never change the value of a unique identifier.
Next, lets implement the object body. I’m opting to show the complete object body because some readers may not check out the earlier article. Here’s the people_obj body:
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 people_obj; 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 40 /* Get people ID attribute. */ 41 MEMBER FUNCTION get_people_id RETURN NUMBER IS 42 BEGIN 43 RETURN self.people_id; 44 END get_people_id; 45 46 /* Get first name attribute. */ 47 MEMBER FUNCTION get_first_name RETURN VARCHAR2 IS 48 BEGIN 49 RETURN self.first_name; 50 END get_first_name; 51 52 /* Get middle name attribute. */ 53 MEMBER FUNCTION get_middle_name RETURN VARCHAR2 IS 54 BEGIN 55 RETURN self.middle_name; 56 END get_middle_name; 57 58 /* Get last name attribute. */ 59 MEMBER FUNCTION get_last_name RETURN VARCHAR2 IS 60 BEGIN 61 RETURN self.last_name; 62 END get_last_name; 63 64 /* Set first name attribute. */ 65 MEMBER PROCEDURE set_first_name 66 ( pv_first_name VARCHAR2 ) IS 67 BEGIN 68 self.first_name := pv_first_name; 69 END set_first_name; 70 71 /* Set middle name attribute. */ 72 MEMBER PROCEDURE set_middle_name 73 ( pv_middle_name VARCHAR2 ) IS 74 BEGIN 75 self.middle_name := pv_middle_name; 76 END set_middle_name; 77 78 /* Set last name attribute. */ 79 MEMBER PROCEDURE set_last_name 80 ( pv_last_name VARCHAR2 ) IS 81 BEGIN 82 self.last_name := pv_last_name; 83 END set_last_name; 84 END; 85 / |
The get_people_id member function on lines 41-44 returns the unique identifier for the object instance. The get_first_name member function on lines 47-50 returns the first_name attribute. The get_middle_name member function on lines 53-56 returns the middle_name attribute. The get_last_name member function on lines 59-62 returns the last_name attribute. Each of these getters returns an instance attribute. The self reserved word identifies the current instance of the object type.
The set_first_name member procedure on lines 65-69 assigns a value to the first_name attribute. The set_middle_name procedure on lines 72-76 assigns a value to the middle_name attribute. The set_last_name member procedures on lines 79-83 assigns a value to the last_name attribute. The constructor functions create instances of the people_obj and return them to the calling scope. Each of these setters assigns a value to an instance attribute.
Comparative functions are limited to the MAP and ORDER member functions. The MAP function only works with the CHAR, DATE, NUMBER, or VARCHAR2 data type. You could implement a MAP function against the last_name attribute but not the collection of the three variable length strings. You would implement an ORDER member function to compare the collection of strings.
You can define an equals MAP function in the people_obj object type like:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , MAP MEMBER FUNCTION equals RETURN VARCHAR2) 20 INSTANTIABLE NOT FINAL; 21 / |
After creating the people_obj object type, you can implement the following MAP function:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 85 /* Implement an equals MAP function. */ 86 MAP MEMBER FUNCTION equals RETURN VARCHAR2 IS 87 BEGIN 88 RETURN self.last_name; 89 END equals; 90 91 END; 92 / |
The MAP function is inadequate when you compare multiple attributes. You can implement an ORDER MEMBER function with the following syntax in the people_obj object type.
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , ORDER MEMBER FUNCTION equals 20 (pv_people PEOPLE_OBJ) RETURN NUMBER) 21 INSTANTIABLE NOT FINAL; 22 / |
The ORDER function is more complete than the MAP function. You can implement a last name, first name, and middle name ORDER function as follows:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 85 /* Implement an equals MAP function. */ 86 ORDER MEMBER FUNCTION equals 87 (pv_people PEOPLE_OBJ) RETURN NUMBER IS 88 BEGIN 89 IF NVL(self.last_name,'A') > NVL(pv_people.last_name,'A') THEN 90 RETURN 1; 91 ELSIF NVL(self.last_name,'A') = NVL(pv_people.last_name,'A') AND 92 NVL(self.first_name,'A') > NVL(pv_people.first_name,'A') THEN 93 RETURN 1; 94 ELSIF NVL(self.last_name,'A') = NVL(pv_people.last_name,'A') AND 95 NVL(self.first_name,'A') = NVL(pv_people.first_name,'A') AND 96 NVL(self.middle_name,'A') > NVL(pv_people.middle_name,'A') THEN 97 RETURN 1; 98 ELSE 99 RETURN 0; 100 END IF; 101 END equals; 102 END; 103 / |
The equals ORDER function on lines 86 through 101 checks for a three conditions. First, it checks whether the instance’s last_name is greater than the parameter object’s last_name. Second, it checks whether the last names are equal and the instance’s first_name is greater than the parameter object’s first_name. Finally, it checks whether the last and first names are equal and the middle_name is greater than the parameter object’s middle_name value.
Unfortunately, it’s hard to test this comparison without adding a to_string function. The to_string function prints the formatted name. You can add the to_string function to the object type like so:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 19 , MAP MEMBER FUNCTION equals RETURN VARCHAR2 21 , MEMBER FUNCTION to_string RETURN VARCHAR2) 20 INSTANTIABLE NOT FINAL; 21 / |
Line 21 shows the declaration of the to_string function, and the following code snippet shows you the implementation of the to_string function:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 103 /* Create a to_string function. */ 104 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 105 BEGIN 106 RETURN self.last_name || ', ' || self.first_name || ' ' || 107 self.middle_name; 108 END to_string; 109 110 END; 111 / |
After assembling all the parts, we can test whether the ORDER comparative function works. The following anonymous block program declares a people_list collection that holds instances of the people_obj object type.
SQL> DECLARE 2 /* Declare an object type. */ 3 TYPE people_list IS TABLE OF people_obj; 4 5 /* Declare three object types. */ 6 lv_obj1 PEOPLE_OBJ := people_obj('Fred',NULL,'Maher'); 7 lv_obj2 PEOPLE_OBJ := people_obj('John',NULL,'Fedele'); 8 lv_obj3 PEOPLE_OBJ := people_obj('James',NULL,'Fedele'); 9 lv_obj4 PEOPLE_OBJ := people_obj('James','Xavier','Fedele'); 10 11 /* Declare a list of the object type. */ 12 lv_objs PEOPLE_LIST := people_list( lv_obj1, lv_obj2 13 , lv_obj3, lv_obj4); 14 15 /* Swap A and B. */ 16 PROCEDURE swap 17 ( a IN OUT PEOPLE_OBJ 18 , b IN OUT PEOPLE_OBJ ) IS 19 /* Declare a third variable. */ 20 c PEOPLE_OBJ; 21 BEGIN 22 /* Swap values. */ 23 c := b; 24 b := a; 25 a := c; 26 END swap; 27 28 BEGIN 29 /* Nested loop comparison. */ 30 FOR i IN 1..lv_objs.COUNT LOOP 31 FOR j IN 1..lv_objs.COUNT LOOP 32 IF lv_objs(i).equals(lv_objs(j)) = 0 THEN 33 swap(lv_objs(i), lv_objs(j)); 34 END IF; 35 END LOOP; 36 END LOOP; 37 38 /* Print the reordered list. */ 39 FOR i IN 1..lv_objs.COUNT LOOP 40 dbms_output.put_line(lv_objs(i).to_string()); 41 END LOOP; 42 END; 43 / |
The people_obj instances on lines 6 through 9 are out of order in the starting collection. The local swap procedure reorders them on lines 30 through 36. You would see the following output from the preceding anonymous block:
Fedele, James Fedele, James Xavier Fedele, John Maher, Fred |
All of our work in this paper so far shows you how to work with implementing functions and procedures in instances of object types. PL/SQL object types support MEMBER functions and procedures to work with object instances. PL/SQL object types also support STATIC functions and procedures. You use STATIC functions and procedures when you want to write and call a module in an object type that works like a function or procedure in a package.
You can call a STATIC function or procedure without creating an instance of an object. Creating an instance of the object type is a key use of STATIC functions. This approach is very much like how Oracle implements temporary BLOB and CLOB columns.
Here’s the snippet of additional code required in the people_obj object type:
SQL> CREATE OR REPLACE 2 TYPE people_obj IS OBJECT 3 ( people_id NUMBER ... 21 , MEMBER FUNCTION to_string RETURN VARCHAR2 22 , STATIC FUNCTION get_people_obj 23 ( pv_people_id NUMBER) RETURN people_obj) 20 INSTANTIABLE NOT FINAL; 21 / |
The get_people_obj function is a STATIC function and it takes a single number to return a name. It accomplishes this by using a parameterized cursor. You would implement the get_people_obj function like so:
SQL> CREATE OR REPLACE 2 TYPE BODY people_obj IS ... 109 /* Create a get_people_obj function. */ 110 STATIC FUNCTION get_people_obj 111 ( pv_people_id NUMBER ) RETURN PEOPLE_OBJ IS 112 113 /* Implement a cursor. */ 114 CURSOR get_people_obj 115 ( cv_people_id NUMBER ) IS 116 SELECT first_name 117 , middle_name 118 , last_name 119 FROM contact 120 WHERE contact_id = cv_people_id; 121 122 /* Create a cursor variable. */ 123 lv_contact get_people_obj%ROWTYPE; 124 125 /* Create a temporary instance of people_obj. */ 126 lv_people_obj PEOPLE_OBJ; 127 BEGIN 128 /* Open, fetch and close cursor. */ 129 OPEN get_people_obj(pv_people_id); 130 FETCH get_people_obj INTO lv_contact; 131 lv_people_obj := people_obj( first_name => lv_contact.first_name 132 , middle_name => lv_contact.middle_name 133 , last_name => lv_contact.last_name); 134 CLOSE get_people_obj; 135 RETURN lv_people_obj; 136 END get_people_obj; 137 138 END; 139 / |
The get_people_obj function takes a single numeric parameter. The numeric parameter passes the primary key value for the contact table. Then, the STATIC function returns an instance of the people_obj object type. It accomplishes that feat by using the numeric value as a lookup key in the contact table, as you can see in the get_people_obj cursor on lines 114 through 120. The STATIC method opens, fetches a single row, and closes on lines 129 through 135.
Now you can call the get_people_obj function in a query and return an instance of people_obj. You can also use the to_string method to view the output, as follows:
SQL> SELECT people_obj.get_people_obj(1003).to_string() 2 FROM dual; |
It prints:
PEOPLE_OBJ.GET_PEOPLE_OBJ(1003).TO_STRING() --------------------------------------------- Vizquel, Oscar |
This article has shown you how to write effective getters, setters, comparators, and static methods. It also has shown how to test and work with Oracle object types and bodies.
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.
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.
Subscribe via RSS