Oracle Multitenant feature, Application Roots, and APEX / ORDS

 I have had an interesting journey with Oracle database 19, and Oracle APEX and Oracle ORDS, using Oracle Multitenant feature (Container database with virtual pluggable databases).  

Container Database

The advantages of Multitenant are you can easily consolidate databases into one database instance (CDB$ROOT), with each consolidated database becoming a "Pluggable Database," (PBD) each appearing as a standalone database to users and programmers. 

Application Containers

Application Containers are basically an application-specific container that functions similar to a Container Database.  You can have multiple Application Containers in a CDB$ROOT.  The Application Container can have various applications installed in it, potentially at different software versions.  It is also useful for large regional databases.

My Adventure

My goal was to have development, system, and production databases in separate Application Containers, along with a test database in another Application Container for testing new releases.  We use Oracle APEX and Oracle ORDS applications in our system.

Bumps in the Road

The installation guide for APEX recommends installing APEX in each PDB for most scenarios. We wanted to install APEX and ORDS in the application containers.  So, I hit a lot of bumps along the way, and I will share what I learned.

Alert Log

PDB_PLUG_IN_VIOLATIONS

DBMS_PDB.SYNC_PDB

The SYNC_PDB PL/SQL package procedure is not documented in the PL/SQL Package Types manual, but it is mentioned here and there in other documents; not much what it does, just times to use it.

Sage Advice

Use the latest versions of APEX and ORDS when possible.  

Useful Links

Following are links to Oracle documentation for the various versions  I am using.

• Oracle 19 Multitenant Administrator's Guide
• Oracle APEX Installation Guide 24.2 - Installing in an Application Container
• Oracle ORDS Installation Guide 24.4 - Multitenant Architecture

Saw a nice option in formatting Oracle explain plans for Oracle 12c for plans that use adaptive plans. Courtesy of Oracle Database 12c: Adaptive Execution Plans with Tom Kyte

 select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));

Those options show for adaptive plans exactly which steps were skipped by the optimizer based on row cardinality. Check out the YouTube video above for an example, at 3:41 https://youtu.be/9o9iuxNBciQ?t=221 You may need to do one of the following:

• Add the hint /*+ gather_plan_statistics */ to the statement
• Set the parameter 'statistics_level' is set to 'ALL', at session or system level. Caution: if you set it at the system level, overall performance will be degraded.

Oracle APEX and /*+ result_cache */ of form variables

I was having a tough time with a bug in an Oracle APEX application. I was filling a shuttle-list with a query, and I added the hint /*+ result_cache */ for performance, and thought that Oracle would be peeking at the bind values being used in the query. Wrong! In regular vanilla queries, Oracle will look at bind variables to see if it can use the result cache, but APEX magic prevents that.

Code sample causing issues:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like v('P215_PROJECT_ID_NUM_SEARCH') 

Well once I took out the hint, all worked as planned!

If you are not familiar with result caching on Oracle, it can make queries that return a small number of rows from large tables run extremely fast.

Actually, if I had coded the query like the following, using bind variable syntax (:P215_PROJECT_ID_NUM_SEARCH) instead of V('') syntax to refer to my form fields, I could probably have used the result_cache hint, and since the V('') syntax is actually calling a PL/SQL procedure, it could be slower than using the bind syntax:

select /*+ result_cache */ PROJECT_ID_NUM as vc1
      from #OWNER#.ru_projects_mvw
      where PROJECT_ID_NUM like :P215_PROJECT_ID_NUM_SEARCH 

Excel VBA "ERROR 2015" returned from Application.GetSaveAsFilename

I was scratching my head over the following Excel Macro code:

Dim vFileName as Variant

...
On Error GoTo ErrorHandler    
vFileName = Application.GetSaveAsFilename(strTaskOrder & ".pdf", _
    "PDF Files (*.pdf), *.pdf", _
    "Select PDF Name for " & strTaskOrder)
On Error GoTo 0

What was interesting is no runtime error was generated, (so I could not use the OnError code; printing the contents of vFileName in the Immediate window was "Error 2015" and the typename(vFileName) was returning "Error", but it was not an "Err" object. But what would work, (thanks to StackOverflow answer to similar issue) I was able to catch whether this was happening:

If IsError(vFileName) Then
    MsgBox "error"
End If

Well the actual problem was that I was omitting a parameter; the FilterIndex (1) of the File Filter; the correct call was:

vFileName = Application.GetSaveAsFilename(strTaskOrder & ".pdf", _
    "PDF Files (*.pdf), *.pdf", _
    1,
    "Select PDF Name for " & strTaskOrder)

Lesson learned: Double-check your parameters!

Federal Tax Cuts Insanity

Well, it looks like the politicians in Washington DC have lost all touch with reality, in order to reach a compromise on the income tax breaks. Seems the compromise includes the worst ideas from both parties. A year-long cut in Social Security taxes will cost $120 billion, but somehow President Obama said there would be no effect on Social Security benefits nor long-term solvency. How could that possibly be?

Another problem is the extension of unemployment benefits, being financed by borrowing, instead of spending cuts. When will the politicians learn that they must stop spending money that they don't have?

Oracle JDBC NullPointerException

Got an interesting Oracle JDBC error with Oracle 10.2.0.4 database and thin JDBC driver. I invoked the method java.sql.Statement.setFetchSize(20000) on a java.sql.PreparedStatement object, and when I invoked the executeQuery() method on the object, I got the following stack trace:

java.lang.NullPointerException
       at oracle.jdbc.driver.DBConversion._CHARBytesToJavaChars(DBConversion.java:974)
       at oracle.jdbc.driver.DBConversion.CHARBytesToJavaChars(DBConversion.java:892)
       at oracle.jdbc.driver.T4CCharAccessor.unmarshalOneRow(T4CCharAccessor.java:199)
       at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:919)
       at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:843)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:630)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
       at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1072)
       at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
       at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
       at MyOracleProgram.invokeQuery(MyOracleProgram.java:682)

Well I tried setting the value to be 1000 for the parameter:
setFetchSize(1000)
and problem solved!

The default FetchSize for this version of Oracle appears to be 10, and if you know you will usually be getting back more than 10 rows, by all means set it to to a higher value, but if you try something bigger than 1,000 be careful!

Judging from the name of the method oracle.jdbc.driver.DBConversion._CHARBytesToJavaChars(DBConversion.java:974) in the stack trace, I bet there is a array for the result set with a hard-coded maximum array size of something bigger than 1000, but less than 20000. It would be nice if the oracle jdbc driver would catch this.

---

Update: Oracle support noticed I was using the ojdbc14.jar file and using Java JDK 1.6; I had to get the ojdbc6.jar file from the Oracle 11.2 JDBC distribution which is certified for JDK 1.6. Works just fine now, with Oracle 10.2 database, and JDK 1.6.

Health care costs out of control: Solution 3: Don't rush

In the rush to get a health care bill into law, a single amendment to it by Senator Debbie Stabenow was passed by a voice vote. Then later the Only after the amendment passed did the Congressional Budget Office realize it made a mistake in the scoring and under counted the one amendment to the tune of $600 million.

Quoting from the above link "It took Senator John Cornyn (R-TX) to point it out. And now he is offering an amendment of his own to make sure this does not happen again." Basically he suggested congress couldn't vote on something until it had been publicly available for a certain amount of time. His amendment was defeated. Where is the transparency at that the leaders claim this health care reform will have, if no one can look at the proposed law?

Even more telling, is that senators are willing to force Medicaid on those who can't afford it, but are unwilling to accept the same coverage themselves if this bill passes! The New York Times details this unwillingness to be subject to themselves, what they think is good for all us.