Archive for December, 2017
Apple iTunes Bug
Over the years, this bug never gets fixed. I know it must irritate more people than just me. Unlike those who live in urban communities with great download speeds and relatively inexpensive Internet providers, I live in an area held hostage by expensive CableOne Internet service. Net neutrality won’t fix my issue.
The Apple iTunes bug occurs after you download a movie and the cloud symbol disappears. At first, it may appear as designed, with only one image displayed, like Papillon and Passengers is shown below:
From time to time, Apple iTunes gets confused (polite speak for an intermittent bug) and creates a new iCloud image side-by-side with the downloaded version of the movie. You can see that with the image of The Adventures of Robin Hood:
It annoys me and it takes time to fix. The present solution is to delete the downloaded file image and then re-download it if you must have a local copy (the tedious lives of those outside of large metropolitan areas). Naturally, for those of us outside of large metropolitan areas with monthly restrictions on the size of downloads, Apple’s solution is not a viable workaround. Unfortunately, Apple appears disinclined to figure out what causes the problem or fixing it in the existing iTunes code base. When I called Apple’s iTune support it took a third level engineer to agree that the problem even exists. 😉
Oracle 12c and PHP
This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.
CDB Connection:
This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:
<?php // Attempt to connect to your database. $c = @oci_connect("video", "video", "localhost/orcl"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
PDB Connection:
This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:
<?php // Attempt to connect to your database. $c = @oci_connect("video", "video", "localhost/videodb"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.
You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:
sqlplus video@video/video |
Hope this helps those trying to sort it out.
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