#DOAG2018: my presentations

I had a great time at DOAG 2018, both during the conference and in the evenings. Thanks to everyone who participated in my two sessions: it was a pleasure to share a bit of knowledge with you!

Here are links to my two presentations on Slideshare. Please download the files so you can see the animations.

Best regards,
Stew

https://www.slideshare.net/stewashton/json-in-18c-json-as-it-was-meant-to-be

https://www.slideshare.net/stewashton/make-your-data-dance-pivot-and-group-by-in-oracle-sql

Advertisements

Generate JOINs among related tables

After finding the shortest “foreign key” path between two tables, I’ll try to generate a SELECT statement that joins all the related tables.

(Please don’t tell me that Oracle SQL Developer does this: I know it does, as long as you already know the path and select all the tables yourself! I find a path for you and make sure the column names are unique. Despite these differences, you will probably never need this code if you use SQL Developer.)

  • The final SELECT starts with the bottom of the hierarchy and works its way up.
  • Table aliases are T1, T2 and so on.
  • All columns of all tables are selected (based on ALL_TAB_COLUMNS, not ALL_TAB_COLS).
  • To make columns unique, they are prefixed with the table alias: T1_* and so on.
  • Table names and column names are only enclosed in double quotes when they contain non-alphanumeric characters. Exceptionally, I always enclose the join columns in double quotes just to save myself some trouble…
  • I use ANSI join syntax, and when I do the equality comparison I use parentheses like this:
    on (T1.COL1, T1.COL2) = ((T2.COL1, T2.COL2))
    It’s a bit easier to code. Don’t be surprised to see the parentheses even when there is only one column.

The code is more than 100 lines long. I’ll put my inadequate explanation first, then the code.

  • search_for: the owners and tables I want to find the path between.
  • pur: the constraints I need to start with.
  • relations: add to each constraint row the remote or parent table.
  • best_path: returns a string that contains all the data from all the rows in the path
  • best_path_tables: breaks down the string into the rows and columns of the path I found. I use REGEXP functions instead of JSON functions so this should work from version 11.2.
  • final_select: get all the columns for the final SELECT, accessing ALL_TAB_COLUMNS
  • final_from: get the FROM and JOIN clauses, accessing ALL_CONS_COLUMNS.

I’ll sign off before posting the code. I would say “hope this helps”, but I feel more like saying

Hope this works!

with search_for (owner, table_name) as (
  select 'OBE', 'OEHR_REGIONS' from dual union all
  select 'OBE', 'OEHR_ORDER_ITEMS' from dual
)
, pur as (
  select OWNER,   CONSTRAINT_NAME, TABLE_NAME,
       R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.constraint_name,
       a.r_owner, b.table_name r_table_name, a.r_constraint_name
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
, best_path as (
  select path
  from (
    select row_number() over( order by
      case when (r_owner, r_table_name) 
            != ((connect_by_root(owner), connect_by_root(table_name)))
        then level end
      ) rn, 
      sys_connect_by_path(
      owner  ||'/'||table_name  ||'/'||  constraint_name||'/'||
      r_owner||'/'||r_table_name||'/'||r_constraint_name,'<') path
    from relations a
    where (r_owner, r_table_name) in (select * from search_for)
    start with (owner, table_name) in (select * from search_for)
    connect by nocycle (owner, table_name) = ((prior r_owner, prior r_table_name))
    and (owner, table_name) not in (select * from search_for)
  )
  where rn = 1
)
, best_path_tables as (
  select level lvl,
    'T'||level al,
    'T'||(level+1) r_al,
    regexp_substr(path, '<(.+?)/',            1, level,'i',1) owner,
    regexp_substr(path, '<(.+?/){1}(.+?)/',   1, level,'i',2) table_name,
    regexp_substr(path, '<(.+?/){2}(.+?)/',   1, level,'i',2) constraint_name,
    regexp_substr(path, '<(.+?/){3}(.+?)/',   1, level,'i',2) r_owner,
    regexp_substr(path, '<(.+?/){4}(.+?)/',   1, level,'i',2) r_table_name,
    regexp_substr(path, '<(.+?/){5}([^/<]+)', 1, level,'i',2) r_constraint_name
  from best_path
  connect by level <= regexp_count (path, '<')
)
, final_select as (
  select lvl, pref ||
  listagg(al||'.'||qu||column_name||qu||' '||qu||al||'_'||column_name||qu,',')
    within group(order by column_id) str
  from (
    select -99 lvl, 'select ' pref, al, column_name, column_id,
      case when not regexp_like (column_name, '^[A-Z][A-Z0-9$#_]*$') then '"' end qu
    from best_path_tables join all_tab_columns using(owner, table_name)
    where lvl = 1
    union all
    select lvl-99, ',', r_al, column_name, column_id,
      case when not regexp_like (column_name, '^[A-Z][A-Z0-9$#_]*$') then '"' end qu
    from best_path_tables b
    join all_tab_columns a on (b.r_owner, b.r_table_name) = ((a.owner, a.table_name))
  )
  group by lvl, pref
)
, final_from as (
  select 0 lvl, 'from '||oq||owner||oq||'.'||tq||table_name||tq||' '|| al str
  from (
    select a.*,
      case when not regexp_like (owner, '^[A-Z][A-Z0-9$#_]*$') then '"' end oq,
      case when not regexp_like (table_name, '^[A-Z][A-Z0-9$#_]*$') then '"' end tq
      from best_path_tables a
      where lvl = 1
  ) a
  union all
  select lvl, 'join '||oq||r_owner||oq||'.'||tq||r_table_name||tq||' '|| r_al || ' on (' ||
  (
    select listagg(al||'."'||column_name||'"',',') within group(order by position)
    from all_cons_columns 
    where (owner, table_name, constraint_name) = ((a.owner, a.table_name, a.constraint_name))
  )
  || ') = ((' ||
  (
    select listagg(r_al||'."'||column_name||'"',',') within group(order by position)
    from all_cons_columns 
    where (owner, table_name, constraint_name) = ((a.r_owner, a.r_table_name, a.r_constraint_name))
  )
  || '))'
  from (
    select a.*,
      case when not regexp_like (r_owner, '^[A-Z][A-Z0-9$#_]*$') then '"' end oq,
      case when not regexp_like (r_table_name, '^[A-Z][A-Z0-9$#_]*$') then '"' end tq
      from best_path_tables a
  ) a
)
select str from (
  select * from final_select
  union all
  select * from final_from
)
order by lvl;
select T1.ORDER_ID T1_ORDER_ID,T1.LINE_ITEM_ID T1_LINE_ITEM_ID,T1.PRODUCT_ID T1_PRODUCT_ID,T1.UNIT_PRICE T1_UNIT_PRICE,T1.QUANTITY T1_QUANTITY,T1.ORDER_ITEM_ID T1_ORDER_ITEM_ID
,T2.ORDER_ID T2_ORDER_ID,T2.ORDER_DATE T2_ORDER_DATE,T2.ORDER_MODE T2_ORDER_MODE,T2.CUSTOMER_ID T2_CUSTOMER_ID,T2.ORDER_STATUS T2_ORDER_STATUS,T2.ORDER_TOTAL T2_ORDER_TOTAL,T2.SALES_REP_ID T2_SALES_REP_ID,T2.PROMOTION_ID T2_PROMOTION_ID
,T3.EMPLOYEE_ID T3_EMPLOYEE_ID,T3.FIRST_NAME T3_FIRST_NAME,T3.LAST_NAME T3_LAST_NAME,T3.EMAIL T3_EMAIL,T3.PHONE_NUMBER T3_PHONE_NUMBER,T3.HIRE_DATE T3_HIRE_DATE,T3.JOB_ID T3_JOB_ID,T3.SALARY T3_SALARY,T3.COMMISSION_PCT T3_COMMISSION_PCT,T3.MANAGER_ID T3_MANAGER_ID,T3.DEPARTMENT_ID T3_DEPARTMENT_ID
,T4.DEPARTMENT_ID T4_DEPARTMENT_ID,T4.DEPARTMENT_NAME T4_DEPARTMENT_NAME,T4.MANAGER_ID T4_MANAGER_ID,T4.LOCATION_ID T4_LOCATION_ID
,T5.LOCATION_ID T5_LOCATION_ID,T5.STREET_ADDRESS T5_STREET_ADDRESS,T5.POSTAL_CODE T5_POSTAL_CODE,T5.CITY T5_CITY,T5.STATE_PROVINCE T5_STATE_PROVINCE,T5.COUNTRY_ID T5_COUNTRY_ID
,T6.COUNTRY_ID T6_COUNTRY_ID,T6.COUNTRY_NAME T6_COUNTRY_NAME,T6.REGION_ID T6_REGION_ID
,T7.REGION_ID T7_REGION_ID,T7.REGION_NAME T7_REGION_NAME
from OBE.OEHR_ORDER_ITEMS T1
join OBE.OEHR_ORDERS T2 on (T1."ORDER_ID") = ((T2."ORDER_ID"))
join OBE.OEHR_EMPLOYEES T3 on (T2."SALES_REP_ID") = ((T3."EMPLOYEE_ID"))
join OBE.OEHR_DEPARTMENTS T4 on (T3."DEPARTMENT_ID") = ((T4."DEPARTMENT_ID"))
join OBE.OEHR_LOCATIONS T5 on (T4."LOCATION_ID") = ((T5."LOCATION_ID"))
join OBE.OEHR_COUNTRIES T6 on (T5."COUNTRY_ID") = ((T6."COUNTRY_ID"))
join OBE.OEHR_REGIONS T7 on (T6."REGION_ID") = ((T7."REGION_ID"))

Get all the Foreign Key Relationships

An old asktom question was recently highlighted on Twitter: All Parent – Child tables in the database. I contributed a new solution, but here is one I think is more complete.

The Requirement: foreign key relationships

The questioner wanted to show the hierarchy of “parent-child” relationships, starting from the top ancestor and going down to the last descendant. He asked for the entire database.

By “parent-child” we mean foreign key relationships: the “parent” has a primary key constraint or a unique constraint, and the “child” has a foreign key constraint that references the parent key.

I assume I should omit any table that is neither parent nor child. I also add an IS_CYCLE column: if = 1, a CONNECT_BY_ISCYCLE condition was met. Finally, I show whether the referenced key is Primary or Unique.

Building the solution

Since we are working on the whole database, DBA_CONSTRAINTS is the appropriate source. For just my schema, I can use USER_CONSTRAINTS instead. For readability, I’ll restrict the output to the HR schema.

The basic information we need includes the owner, constraint name, constraint type and table name, together with the referenced constraint and its owner. I include only constraints of type ‘P’ (Primary), ‘U’ (Unique), and ‘R’ (foreign key Reference). In recent versions, I can restrict my query to owners that are not “Oracle maintained”.

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
select * from pur;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NAME
HR COUNTRY_C_ID_PK P COUNTRIES
HR COUNTR_REG_FK R COUNTRIES HR REG_ID_PK
HR DEPT_ID_PK P DEPARTMENTS
HR DEPT_LOC_FK R DEPARTMENTS HR LOC_ID_PK
HR DEPT_MGR_FK R DEPARTMENTS HR EMP_EMP_ID_PK
HR EMP_EMP_ID_PK P EMPLOYEES
HR EMP_DEPT_FK R EMPLOYEES HR DEPT_ID_PK
HR EMP_JOB_FK R EMPLOYEES HR JOB_ID_PK
HR EMP_MANAGER_FK R EMPLOYEES HR EMP_EMP_ID_PK
HR EMP_EMAIL_UK U EMPLOYEES
HR JOB_ID_PK P JOBS
HR JHIST_EMP_ID_ST_DATE_PK P JOB_HISTORY
HR JHIST_DEPT_FK R JOB_HISTORY HR DEPT_ID_PK
HR JHIST_EMP_FK R JOB_HISTORY HR EMP_EMP_ID_PK
HR JHIST_JOB_FK R JOB_HISTORY HR JOB_ID_PK
HR LOC_ID_PK P LOCATIONS
HR LOC_C_ID_FK R LOCATIONS HR COUNTRY_C_ID_PK
HR REG_ID_PK P REGIONS

 

Now we need to add the table the referenced constraints belong to. I took the easy path and did a self join.

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  --and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
select * from relations
OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE
HR COUNTRIES HR REGIONS P
HR DEPARTMENTS HR EMPLOYEES P
HR DEPARTMENTS HR LOCATIONS P
HR EMPLOYEES HR DEPARTMENTS P
HR EMPLOYEES HR EMPLOYEES P
HR EMPLOYEES HR JOBS P
HR JOB_HISTORY HR DEPARTMENTS P
HR JOB_HISTORY HR EMPLOYEES P
HR JOB_HISTORY HR JOBS P
HR LOCATIONS HR COUNTRIES P

 

Note that EMPLOYEES references itself.

UPDATE: I added a commented line at the end of RELATIONS. If you want to eliminate self-references, uncomment that line.

Now we need to identify the starting points for the hierarchical query. These are tables that are referenced, but do not reference any other table – though they can reference themselves. We’ll add those to the RELATIONS result set:

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  -- and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
, with_parents as (
  select * from relations
  union
  select r_owner, r_table_name, null, null, null
  from relations 
  where (r_owner, r_table_name) not in (
    select owner, table_name
    from relations
    where (owner, table_name) != ((r_owner, r_table_name))
  )
) select * from with_parents
OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE
HR COUNTRIES HR REGIONS P
HR DEPARTMENTS HR EMPLOYEES P
HR DEPARTMENTS HR LOCATIONS P
HR EMPLOYEES HR DEPARTMENTS P
HR EMPLOYEES HR EMPLOYEES P
HR EMPLOYEES HR JOBS P
HR JOBS
HR JOB_HISTORY HR DEPARTMENTS P
HR JOB_HISTORY HR EMPLOYEES P
HR JOB_HISTORY HR JOBS P
HR LOCATIONS HR COUNTRIES P
HR REGIONS

 

Finally, we can start from the rows where R_OWNER is null and go down the hierarchy:

with pur as (
  select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME,
    R_OWNER, R_CONSTRAINT_NAME
  from dba_constraints
  where constraint_type in('P','U','R')
  and owner in (select username from dba_users where oracle_maintained = 'N')
)
, relations as (
  select a.owner, a.table_name, a.r_owner,
    b.table_name r_table_name,
    b.constraint_type r_constraint_type
  from pur a join pur b
  on (a.R_OWNER, a.R_CONSTRAINT_NAME) = ((b.OWNER, b.CONSTRAINT_NAME))
  -- and (a.OWNER, a.table_name) != ((b.OWNER, b.table_name))
)
, with_parents as (
  select * from relations
  union
  select r_owner, r_table_name, null, null, null
  from relations 
  where (r_owner, r_table_name) not in (
    select owner, table_name
    from relations
    where (owner, table_name) != ((r_owner, r_table_name))
  )
)
select level LVL, OWNER, TABLE_NAME, R_OWNER, R_TABLE_NAME, R_CONSTRAINT_TYPE,
  connect_by_iscycle is_cycle
from with_parents
start with r_owner is null
connect by nocycle (r_owner, r_table_name) = ((prior owner, prior table_name))
order siblings by owner, table_name;
LVL OWNER TABLE_NAME R_OWNER R_TABLE_NAME R_CONSTRAINT_TYPE IS_CYCLE
1 HR JOBS 0
2 HR EMPLOYEES HR JOBS P 1
3 HR DEPARTMENTS HR EMPLOYEES P 1
4 HR JOB_HISTORY HR DEPARTMENTS P 0
3 HR JOB_HISTORY HR EMPLOYEES P 0
2 HR JOB_HISTORY HR JOBS P 0
1 HR REGIONS 0
2 HR COUNTRIES HR REGIONS P 0
3 HR LOCATIONS HR COUNTRIES P 0
4 HR DEPARTMENTS HR LOCATIONS P 0
5 HR EMPLOYEES HR DEPARTMENTS P 1
6 HR JOB_HISTORY HR EMPLOYEES P 0
5 HR JOB_HISTORY HR DEPARTMENTS P 0

 

Hope this helps…

More on Comparing Columns across Rows

My previous post got several replies about other ways to compare columns across rows. Here’s my take on all of them.

Test data (again)

create table t as
with data as (
  select level n from dual connect by level <= 3
)
, tests as (
  select row_number() over (order by a.n, b.n) test_id,
  nullif(a.n, 3) a, 
  nullif(b.n, 3) b,
  case a.n when b.n then 'Same' else 'Different' end status
  from data a, data b
)
select test_id, tbl, n, status
from tests
unpivot include nulls (n for tbl in(A,B));

select * from t order by 1,2;
TEST_ID TBL N STATUS
1 A 1 Same
1 B 1 Same
2 A 1 Different
2 B 2 Different
3 A 1 Different
3 B Different
4 A 2 Different
4 B 1 Different
5 A 2 Same
5 B 2 Same
6 A 2 Different
6 B Different
7 A Different
7 B 1 Different
8 A Different
8 B 2 Different
9 A Same
9 B Same

 

My solution (again)

It looks like the most concise test for a difference is COUNT(N) BETWEEN 1 and COUNT(DISTINCT N).

select t.*,
count(distinct n) over(partition by test_id) cnt_distinct,
count(n) over(partition by test_id) cnt,
case when count(n) over(partition by test_id)
  between 1 and count(distinct n) over(partition by test_id)
  then 'Different' else 'Same' end new_status
from t
order by 4,5,6,1,2;
TEST_ID TBL N STATUS CNT_DISTINCT CNT NEW_STATUS
3 A 1 Different 1 1 Different
3 B Different 1 1 Different
6 A 2 Different 1 1 Different
6 B Different 1 1 Different
7 A Different 1 1 Different
7 B 1 Different 1 1 Different
8 A Different 1 1 Different
8 B 2 Different 1 1 Different
2 A 1 Different 2 2 Different
2 B 2 Different 2 2 Different
4 A 2 Different 2 2 Different
4 B 1 Different 2 2 Different
9 A Same 0 0 Same
9 B Same 0 0 Same
1 A 1 Same 1 2 Same
1 B 1 Same 1 2 Same
5 A 2 Same 1 2 Same
5 B 2 Same 1 2 Same

 

Using the DUMP() function (Andrej Pashchenko)

Since the DUMP() function returns a non-null result in every case, we can simply test

COUNT( DISTINCT DUMP(n) ) OVER (PARTITION BY TEST_ID)

If the result is 1, N is the same, otherwise it is different.

This solution is more concise, but in my tests it ran 10 times slower. The DUMP() function chews up some CPU.

Using SYS_OP_MAP_NONNULL() (Tony Hasler)

As with DUMP(), we can test

COUNT( DISTINCT SYS_OP_MAP_NONNULL(n) ) OVER (PARTITION BY TEST_ID)

Again a concise solution. Surprisingly, it runs twice as slow as my solution – and despite one mention in the documentation I doubt that this function is supported.

Simplifying the comparison

Zahar Hilkevich suggested simply comparing the two counts:

select t.*,
count(distinct n) over(partition by test_id) cnt_distinct,
count(n) over(partition by test_id) cnt,
case when count(n)          over(partition by test_id)
        = count(distinct n) over(partition by test_id)
  then 'Different' else 'Same' end new_status
from t
order by 4,5,6,1,2;
TEST_ID TBL N STATUS CNT_DISTINCT CNT NEW_STATUS
3 A 1 Different 1 1 Different
3 B Different 1 1 Different
6 A 2 Different 1 1 Different
6 B Different 1 1 Different
7 A Different 1 1 Different
7 B 1 Different 1 1 Different
8 A Different 1 1 Different
8 B 2 Different 1 1 Different
2 A 1 Different 2 2 Different
2 B 2 Different 2 2 Different
4 A 2 Different 2 2 Different
4 B 1 Different 2 2 Different
9 A Same 0 0 Different
9 B Same 0 0 Different
1 A 1 Same 1 2 Same
1 B 1 Same 1 2 Same
5 A 2 Same 1 2 Same
5 B 2 Same 1 2 Same

 

Notice that in test case 9, when both values are null, Zahar’s test says they are “different”. This is not a problem in the context of displaying different values, since the output values will be null no matter what.

Conclusion

Thanks to those who mentioned other more concise solutions. Despite my taste for concision, I think the solution I proposed is correct and efficient. The choice is yours!

My #ilOUG18 presentations

I just finished presenting at the Israeli OUG conference. The conference was a great experience, from the Sunday visit of Jerusalem to the two days of presentations. Thanks to the organizers for inviting me and taking great care of everyone!

Slideshare does not allow me to upload a new version of my slides, so here are links to the versions I presented during 2017. I made no important changes for Israel. If you were at the conference and you want the very latest version, please check on the ILOUG website.

Safe trips home to everyone!

Please download to see the animations, they will help point you to the right elements as you move through the slides.

Enjoy and best regards, Stew

My #ukoug_tech17 presentation on Advanced Row Pattern Matching

I just finished presenting on this topic, had a great time and got some memorable praise. Thanks again to all who attended for your benevolent attention – and your correct answers to my questions!

As promised, the absolutely freshest copy of the slides is now on the Conference website and on slideshare:

For those interested, here is the prequel that is not “advanced”:

Please download to see the animations, they will help point you to the right elements as you move through the slides.

Enjoy and best regards, Stew

ODC Appreciation Day: Mike Hichwa’s team #ThanksODC

I don’t know Oracle’s internal organization, but I get the impression that people like Joel Kallman, Jeff Smith and Kris Rice work for or with Mike Hichwa. By naming him, I get to show appreciation for a bunch of technologies, and for the people behind them.

SQL Developer

95% of my time spent on Oracle is spent inside SQL Developer, and I probably use only 5% of its features. It’s free, gets terrific free “support” from an ODC forum and from Jeff Smith’s blogumentation.

Database Virtual Box Appliance

As a developer, not a DBA, I gave up rolling my own database VM and now happily use Kris Rice’s prebuilt appliance, with just a few tweaks for a Macbook Pro with a french keyboard.

APEX

Another “freebie” that blows me away. I never got a chance to use it in my job, so last year I volunteered to develop a web application for my daughter’s medical school. With no training, no knowledge of Javascript and a jump-start from Richard Martens (thanks again!), I made every deadline and delivered every requirement including some I added myself!

APEX provides so much functionality that web developers would otherwise have to reinvent, both for users and for the developers themselves. Again, I probably use 5% or less of the features provided.

The Attitude

Having worked for an IT supplier and IT customers, I favor a win / win attitude. Way too often, Oracle Corporation projects an “I win, you lose” mentality: Oracle competes with everybody, including its customers, the DBAs who make its products work and the developers who make its products worthwhile:

  • Base your technical architecture on our non-contractual licensing rules or face an audit.
  • Buy Autonomous and reduce labor costs for operational DBAs.
  • Buy InMemory and get your results so much faster “without changing a single line of code”.

Since I do all my Oracle-related activity for free, it’s frustrating to promote a technology from one of the least-loved IT companies around.

That is why the attitude of all the folks behind these “no extra cost” tools makes me feel so good. They clearly want people who use their stuff to win big and have fun doing it. Communication with them is a two-way street with fast lanes, so their “products” evolve quickly and in useful directions.

To all you folks, whether I’ve met you or read you or just enjoyed the fruits of your labor, my appreciation today and every day…