Archive for the ‘Oracle 12c’ Category
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.
PostgreSQL Identity Columns
It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.
Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.
MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:
CREATE TABLE auto ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , text_field VARCHAR(30) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; |
Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.
If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:
CREATE TABLE auto ( auto_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) , text_field VARCHAR2(30) , CONSTRAINT auto_pk PRIMARY KEY (auto_id)); |
You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.
CREATE TABLE auto ( id SERIAL CONSTRAINT auto_pk PRIMARY KEY , text_field VARCHAR(30)); ALTER SEQUENCE auto_id_seq RESTART WITH 1001; |
You can see the table and assigned sequence with the following command in PostgreSQL:
\d+ auto |
It should display:
Table "public.auto"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('auto_id_seq'::regclass) | plain | |
text_field | character varying(30) | | extended | |
Indexes:
"auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no |
As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.
SQL Logic Overkill, again …
It’s interesting to watch people try to solve problems. For example, the student is required to use a scalar subquery in a SQL lab exercise that I wrote. It should be a simple fix. The problem is structured with an incorrect foreign key value in an external CSV file and the restriction that you can not replace the value in the external CSV file. I hoped that students would see the easiest option was to write a scalar subquery in the SELECT clause to replace the value found in the external file. There’s even a hint about how to use a scalar subquery.
Students who are new to SQL can take very interesting approaches to solve problems. The flexibility of SQL can lead them to solve problems in interesting ways. While the following solution worked to solve the problem, it’s wrong on two levels:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | INSERT INTO TRANSACTION (SELECT transaction_s1.NEXTVAL , tr.transaction_account , CASE WHEN NOT tr.transaction_type = (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'TRANSACTION_TYPE' AND common_lookup_type = 'CREDIT') THEN cl.common_lookup_id END AS transaction_type , tr.transaction_date , (tr.transaction_amount / 1.06) AS transaction_amount , tr.rental_id , tr.payment_method_type , tr.payment_account_number , tr.created_by , tr.creation_date , tr.last_updated_by , tr.last_update_date FROM transaction_reversal tr CROSS JOIN common_lookup cl WHERE cl.common_lookup_table = 'TRANSACTION' AND cl.common_lookup_column = 'TRANSACTION_TYPE' AND cl.common_lookup_type = 'CREDIT'); |
The CASE statement on lines 4 through 12 substitutes a value only when the source value is not a match. That means if the source file is ever correct a null value would become the transaction_type column value, which would make the statement fail because the transaction_type column is NOT NULL constrained in the target transaction table. Therefore, the logic of the student’s approach requires adding an ELSE clause to the CASE statement for the event that the source file is ever corrected. The modified CASE statement would be =the following:
4 5 6 7 8 9 10 11 12 13 14 | , CASE WHEN NOT tr.transaction_type = (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'TRANSACTION_TYPE' AND common_lookup_type = 'CREDIT') THEN cl.common_lookup_id ELSE tr.transaction_type END AS transaction_type |
The second element of student thought at issue is the CROSS JOIN to the in-line view. It does one thing right and another wrong. It uses the unique key to identify a single row, which effectively adds all the columns for that one row to all rows returned from the external transaction_reversal table. The CROSS JOIN is a correct approach to adding values for computations to a query when you need those columns for computations. The problem with this CROSS JOIN logic may not be immediately obvious when you write it in ANSI SQL 1992 syntax, but it should become obvious when you replace the inline view with a Common Table Expression (CTE) in ANSI SQL 1999 syntax, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | INSERT INTO TRANSACTION (WITH cte AS (SELECT * FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'TRANSACTION_TYPE' AND common_lookup_type = 'CREDIT') SELECT transaction_s1.NEXTVAL , tr.transaction_account , cte.common_lookup_id AS transaction_type , tr.transaction_date , (tr.transaction_amount / 1.06) AS transaction_amount , tr.rental_id , tr.payment_method_type , tr.payment_account_number , tr.created_by , tr.creation_date , tr.last_updated_by , tr.last_update_date FROM transaction_reversal tr CROSS JOIN cte); |
Unfortunately, you would discover that Oracle Database 11g does not support the use of an ANSI SQL 1999 WITH clause inside as the source for an INSERT statement. Oracle Database 12c does support the use of the ANSI SQL 1999 WITH clause inside a subquery of an INSERT statement. That’s an “Oops!” for Oracle 11g because that means the Oracle database fails to meet the ANSI SQL 1999 compliance test. 😉 Great that they fixed it in Oracle 12c. While the nested query would work in Oracle as an ordinary query (outside of an INSERT statement). It raises the following error when you embed it in an INSERT statement:
ERROR AT line 20: ORA-32034: unsupported USE OF WITH clause |
The WITH clause does highlight a key problem with the idea of a CROSS JOIN in this situation. You don’t need all the columns from the common_lookup table. You only need the common_lookup_id column. That make the CROSS JOIN approach suboptimal if it worked.
The complex logic in the original approach is wasted. That’s true because the common_lookup_id value can be supplied to each row as the value from a scalar subquery. The scalar query runs once and the result is placed in the return set for each row. You implement the scalar subquery in the SELECT clause, like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | INSERT INTO TRANSACTION (SELECT transaction_s1.NEXTVAL , tr.transaction_account , (SELECT common_lookup_id FROM common_lookup WHERE common_lookup_table = 'TRANSACTION' AND common_lookup_column = 'TRANSACTION_TYPE' AND common_lookup_type = 'CREDIT') AS transaction_type , tr.transaction_date , (tr.transaction_amount / 1.06) AS transaction_amount , tr.rental_id , tr.payment_method_type , tr.payment_account_number , tr.created_by , tr.creation_date , tr.last_updated_by , tr.last_update_date FROM transaction_reversal tr); |
There really was no intent or logical outcome where the value from the original CASE statement would be different than the subquery’s common_lookup_id value. That fact makes adding an ELSE clause useless, and the solution viable though inefficient. Also, there was no need for the additional columns from the common_lookup table because they are unused. The subquery on lines 4 through 8 provides the optimal solution and improved efficiency.
Developers should ask themselves two questions when they write SQL:
- If my logic is so elegant why do I need it to be so elegant?
- Is there a simpler solution to provide the desired result set?
If there aren’t good answers to both questions, they should re-write it. I hope the examples answer questions and help folks solve problems.
Oracle SQL Strip Quotes
Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:
SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message" FROM dual; |
It will print:
Hello World! |
As always, I hope this helps those looking for a solution.
Reset Oracle Password
This blog entry shows you how to reset the system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the student user is the sudoer user.
After you sign on to the student user account, you open a Terminal session and you should see the following:
[student@localhost python]$ |
The oracle user account should be configured to prevent a login. So, you should use the su command or sudo command to open a terminal shell as the root user.
[student@localhost python]$ sudo sh [sudo] password for student: |
As the root user, you can login as the oracle user with the following command:
su - oracle |
and, you should see the following prompt. You can see the present working directory (pwd) with the pwd command:
-bash-4.2$ pwd /u01/app/oracle |
You need to source the oracle_env.sh shell file created by the installation of the Oracle Database during the installation. You have two approaches to source the environment file, the first approach is with a dot (.), like
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh |
or, this
source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh |
The oracle_env.sh file contains the following:
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe export ORACLE_SID=XE export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export PATH=$ORACLE_HOME/bin:$PATH |
Now, you can connect to the Oracle Database as the internal user with the following command:
sqlplus / as sysdba |
Once connected as the internal user, you can reset the system user’s password to “cangetin” with this command:
ALTER USER system IDENTIFIED BY cangetin; |
At this point, you can also stop and start the database. You stop the database with this command:
shutdown immediate |
You can then start the database with this command:
startup |
After setting the system user password, sign out of SQL*Plus. Then, you can type two exits to return to the student user account, like this:
-bash-4.2$ exit logout sh-4.2# exit exit [student@localhost python]$ |
As always, I hope this helps those who need to reset the system password when they don’t know what it was to begin with.
Oracle Diagnostic Queries
It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.
For this example, you create the following table_struct object type and a table_list collection type:
/* Drop the types from most to least dependent. */ DROP TYPE table_list; DROP TYPE table_struct; /* Create the record type structure. */ CREATE OR REPLACE TYPE table_struct IS OBJECT ( table_name VARCHAR2(30) , column_cnt NUMBER , row_cnt NUMBER ); / /* Create the collection of a record type structure. */ CREATE OR REPLACE TYPE table_list IS TABLE OF table_struct; / |
The following listing function now reads all table names from the user_tables view. A subordinate cursor reads the user_tab_columns view for the number of columns in a table. A Native Dynamic SQL (NDS) cursor counts the number of rows in each tables found in the .
/* Create the listing function. */
CREATE OR REPLACE
FUNCTION listing RETURN table_list IS
/* Variable list. */
lv_column_cnt NUMBER;
lv_row_cnt NUMBER;
/* Declare a statement variable. */
stmt VARCHAR2(200);
/* Declare a system reference cursor variable. */
lv_refcursor SYS_REFCURSOR;
lv_table_cnt NUMBER;
/* Declare an output variable. */
lv_list TABLE_LIST := table_list();
/* Declare a table list cursor that excludes APEX tables. */
CURSOR c IS
SELECT table_name
FROM user_tables
WHERE table_name NOT IN
('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS'
,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS'
,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY'
,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
,'DEMO_ORDER_ITEMS','DEMO_STATES');
/* Declare a column count. */
CURSOR cnt
( cv_table_name VARCHAR2 ) IS
SELECT table_name
, COUNT(column_id) AS cnt_columns
FROM user_tab_columns
WHERE table_name = cv_table_name
GROUP BY table_name;
BEGIN
/* Read through the data set of non-environment variables. */
FOR i IN c LOOP
/* Count the columns of a table. */
FOR j IN cnt(i.table_name) LOOP
lv_column_cnt := j.cnt_columns;
END LOOP;
/* Declare a statement. */
stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
/* Open the cursor and write set to collection. */
OPEN lv_refcursor FOR stmt;
LOOP
FETCH lv_refcursor INTO lv_table_cnt;
EXIT WHEN lv_refcursor%NOTFOUND;
lv_list.EXTEND;
lv_list(lv_list.COUNT) := table_struct(
table_name => i.table_name
, column_cnt => lv_column_cnt
, row_cnt => lv_table_cnt );
END LOOP;
END LOOP;
RETURN lv_list;
END;
/ |
The following query pulls the processed data set as the function’s result:
COL table_name FORMAT A20 HEADING "Table Name" COL column_cnt FORMAT 9,999 HEADING "Column #" COL row_cnt FORMAT 9,999 HEADING "Row #" SELECT table_name , column_cnt , row_cnt FROM TABLE(listing); |
It returns the following result set:
Table Name Column # Row # -------------------- -------- ------ SYSTEM_USER 11 5 COMMON_LOOKUP 10 49 MEMBER 9 10 CONTACT 10 18 ADDRESS 10 18 STREET_ADDRESS 8 28 TELEPHONE 11 18 RENTAL 8 4,694 ITEM 14 93 RENTAL_ITEM 9 4,703 PRICE 11 558 TRANSACTION 12 4,694 CALENDAR 9 300 AIRPORT 9 6 ACCOUNT_LIST 8 200 15 rows selected. |
As always, I hope this helps those trying to work with the Oracle database.
Multitenant Architecture 12c
I thought this illustration was an interesting view of Oracle Database 12c’s Multitenant Architecture. It posted on ToadWorld.com today in a new article by Deiby Gomez and I thought it might be interesting for others.
You can see how to provision a pluggable database in this article on provisioning a pluggable database. As always, I hope it helps.
Oracle EBS 12.2 & Ruby
As does seem to occur from time-to-time, I’m out there in the weeds again and sorting out a solution that fits a customer’s need. They wanted to know if they could write Oracle EBS 12.2 Concurrent Manager Programs in Ruby. They don’t want to write Java.
I checked the documentation, which as is too common, didn’t answer the question. I’m sure if I downloaded the PDF and searched it for one of the languages I knew Oracle supported, I would have found the list of supported languages.
It was simply quicker to query the Oracle EBS 12.2 FND_LOOKUPS table like so:
SELECT lookup_type , lookup_code , SUBSTR(meaning,1,30) AS meaning FROM fnd_lookups WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE' ORDER BY meaning; |
It returns the list of possible types of Oracle EBS 12.2 Concurrent Manager Programs:
LOOKUP_TYPE LOOKUP_CODE MEANING -------------------------- ------------ ------------------------------ CP_EXECUTION_METHOD_CODE X FlexRpt CP_EXECUTION_METHOD_CODE F FlexSql CP_EXECUTION_METHOD_CODE H Host CP_EXECUTION_METHOD_CODE S Immediate CP_EXECUTION_METHOD_CODE K Java Concurrent Program CP_EXECUTION_METHOD_CODE J Java Stored Procedure CP_EXECUTION_METHOD_CODE M Multi Language Function CP_EXECUTION_METHOD_CODE P Oracle Reports CP_EXECUTION_METHOD_CODE I PL/SQL Stored Procedure CP_EXECUTION_METHOD_CODE E Perl Concurrent Program CP_EXECUTION_METHOD_CODE B Request Set Stage Function CP_EXECUTION_METHOD_CODE L SQL*Loader CP_EXECUTION_METHOD_CODE Q SQL*Plus CP_EXECUTION_METHOD_CODE R SQL*Report CP_EXECUTION_METHOD_CODE Z Shutdown Callback CP_EXECUTION_METHOD_CODE A Spawned |
That gave me some of the answer. You can’t call Ruby programs directly. However, Perl lets you use Inline::Ruby. You can use Inline:Ruby to call your Ruby programs. So, if you use Perl to wrap Ruby you don’t have to use Java.
DB_LINK w/o tnsnames.ora
A question popped up, which I thought was interesting. How can you create a DB_LINK in Oracle without the DBA changing the tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.
- Do the following with the
tnspingutility:tnsping mohawk
It should return this when the server’s
hostnameismohawkand domain name istechtinker.com:TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 26-JUL-2016 16:55:58 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mohawk.techtinker.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (10 msec)
- You can now create a
DB_LINKin another Oracle instance without atnsnames.oraentry by referencing the type of server connection and service name with the following syntax (please note that you should remove extraneous white space):CREATE DATABASE LINK test CONNECT TO student IDENTIFIED BY student USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mohawk.techtinker.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))'
In an older database version, you may need to refer to the
SID, like this:CREATE DATABASE LINK test CONNECT TO student IDENTIFIED BY student USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mohawk.techtinker.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)))'
Then, you can query a contact table in the remote instance like this:
SELECT COUNT(*) FROM contact@test;
As always, I hope this helps somebody trying to solve a problem.
Can’t Display 256 Colors
If you’re reading this post, you most likely are trying to run the Oracle Database 11g or 12c runInstaller program, and it’s failing a critical dependency check and displaying an error like the one below. If so, choose n because if you choose y it won’t launch the Oracle Installer.
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 30824 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3967 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,
Continue? (y/n) [n] n |
The first thing to check is whether you’ve the $TERM environment variable. It’ll be set in your env list but may not be set in your .bashrc file. You can see whether it’s set by running the following command:
echo $TERM |
It should return a value, like this:
xterm-256color |
If you didn’t get that value, use the env command to lookup the $TERM. The correct value can be found by running the env command like this:
env | grep -i term |
Add $TERM environment variable to your .bashrc file and source it after the change or reboot the user’s session:
export TERM=xterm-256color |
If it still doesn’t work, some posts ask you to run xclock but you don’t generally install the xhost clients. Those articles assumes you’ve installed the xorg-x11-apps package library. That’s more or less a choice you made when installing the Linux OS. You can check for the presence of the library with the following command as the root user:
rpm -qa xorg-x11-apps |
If the command fails to return a result from the search of Red Hat Package Manager (RPM) libraries, you haven’t installed it. You can install it as the root superuser with this syntax:
yum install -y xorg-x11-apps |
It should display the following result when successful:
Loaded plugins: langpacks Resolving Dependencies --> Running transaction check ---> Package xorg-x11-apps.x86_64 0:7.7-6.el7 will be installed --> Processing Dependency: libXaw.so.7()(64bit) for package: xorg-x11-apps-7.7-6.el7.x86_64 --> Running transaction check ---> Package libXaw.x86_64 0:1.0.12-5.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================= Package Arch Version Repository Size ================================================================================= Installing: xorg-x11-apps x86_64 7.7-6.el7 ol7_latest 304 k Installing for dependencies: libXaw x86_64 1.0.12-5.el7 ol7_latest 190 k Transaction Summary ================================================================================= Install 1 Package (+1 Dependent package) Total download size: 494 k Installed size: 1.2 M Downloading packages: (1/2): libXaw-1.0.12-5.el7.x86_64.rpm | 190 kB 00:00:00 (2/2): xorg-x11-apps-7.7-6.el7.x86_64.rpm | 304 kB 00:00:00 --------------------------------------------------------------------------------- Total 690 kB/s | 494 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libXaw-1.0.12-5.el7.x86_64 1/2 Installing : xorg-x11-apps-7.7-6.el7.x86_64 2/2 Verifying : libXaw-1.0.12-5.el7.x86_64 1/2 Verifying : xorg-x11-apps-7.7-6.el7.x86_64 2/2 Installed: xorg-x11-apps.x86_64 0:7.7-6.el7 Dependency Installed: libXaw.x86_64 0:1.0.12-5.el7 Complete! |
After installing the xorg-x11-apps library packages, you can retry running the Oracle installer. You should now see the following successful message set:
Starting Oracle Universal Installer... Checking Temp space: must be greater than 500 MB. Actual 30809 MB Passed Checking swap space: must be greater than 150 MB. Actual 3967 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-06-01_01-50-54AM. Please wait ... |
As always, I hope this helps my students and anybody looking for a solution to a less than explicit error message.
Subscribe via RSS