As promised in my last post, I'll address the parsing error you get when you attempt to convert the text returned by the NCBI efetch utilities into an XMLType. The Oracle XML parser within the database treats the XML comment "<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">" as an indicator to validate the XML against the DTD located at "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd". In many, maybe even most, cases this is fine.
I recently ran into an Oracle database bug (version 12c). It's definitely a bug, and there may even be a patch for it, maybe. We've all been in this position, though. There's definitely a bug. We've identified something similar in the Oracle support site bug database. There's a patch for it. How long will it take to get the patch installed? Can I even install the patch on an Oracle Database Appliance (or Exadata or whatever other special circumstance)? What happens if the patch doesn't fix the specific issue?
Returning the stated purpose of this blog, "specific solutions to isolated problems," I've run into a few quirks with Oracle REST Data Services recently. The first is simply figuring out exactly what version of ORDS I am running. There are (at least) two components, the mid-tier java (ords.war) and the database metadata and packages (in ORDS_METADATA). Does anyone know the best way to get these?
About a year ago I asked a question about cloning Oracle Databases in the APEX section of the Oracle Community. The question is here (no need to click the link, all of the content is also in this blog post):
I haven't received much traction, so I'm reformulating it as a recommendation. Perhaps I'll get more feedback in this form. I'll also point out an element I think is a bug in APEX...you'll have to read to the end to see that.
You may have encountered ORA-24247: network access denied by access control list (ACL) and wondered "who has access to what from my database?" I extended a query from the Oracle documentation to give me the results I wanted: ACL Name, Username, host, lower port, upper port, and if granted connect and resolve.
with privs as ( SELECT acl, u.username, host, lower_port, upper_port,
I had the need to allow system A to talk to system B via a REST service. The data was sensitive and powers above me requested that system A use OAuth2 to connect to system B. This REST service call does not involve an end user, it's system A pulling a CSV extract from system B. There are many ways to protect this, but the decision was to use OAuth2. Below is a cookbook on how to do this. This example assumes you have ORDS 3.x installed with the ORDS_PUBLIC_USER and ORDS_METADA schemas configured.
There are lots of pluses and minuses to db links, but they are certainly easy and used in the right context they work very well. I admit that I sometimes use them when there is a better technical solution--just because it is so easy and the better solution may not be worth the time.
The case of LOBs over db links can be tricky. You can't select a lob over a db link in SQL or PL/SQL:
I've recently been involved with extending a number of systems that have pre-built data models. I'm generally unhappy with these data models for a variety of reasons. There are many great academic texts on data modeling. I will try to put together a bibliography in an upcoming post. For now, I'll start by discussing the "never delete data" trend. It is generally coupled with the use of a column to indicate that the data should have been deleted (typically a column named VOID) but was instead allowed, indeed required, to linger forever in the table.
Recently, I've been doing a lot with Oracle Application Express (APEX) and web services. At Oracle OpenWorld I came across a presentation on just this topic. Douwe Pieter van den Bos, an Oracle Ace, presented on using APEX with the Oracle SOA Suite. I have used SOA Suite in the past, and quite like it, but recently the web services I've been using are SOAP based services that I have very little influence on. They are not built with or deployed on the Oracle SOA Suite.
It's definitely about the cloud at Oracle OpenWorld 2011. In many ways Oracle has always promoted it's database in the private cloud--long before the term cloud (private or public) became popular. At previous OpenWorlds, Larry Ellison poked fun at the cloud, noting that the notion isn't new. Well, even Oracle must bend to the popularity of the term cloud. I haven't yet heard an official name for the next database release, but I'm betting on Oracle 12c.
My last blog post was a tribute to Scott Spadafore. A year and a half before, I also offered tribute to another close friend and APEX guru, Carl Backstrom. I have had a hard time getting past the loss of these two friends. Such a hard time that this is my first post in over a year--my first since Scott's haiku. I have started to write many posts, but none seemed worthy of moving Scott's haiku down the page. Scott was a pillar of the Oracle
My wife recently took a look at this blog and told me that it would be better with pictures. She also suggested that not every post had to start with: If you are installing/configuring/coding with the Oracle product abc and you get error ORA-nnnnn . . . With that in mind, I share the following.
Just about everyone has already blogged about it, but APEX 4.0 EA is available now. http://tryapexnow.com/ What else is there to say that has not already been said? Not much probably, but I'll point out one new feature: APEX 4.0 is not available in Internet Explorer. I have it on good authority that this feature will only be in the beta and will not make it into the production release--we can hope, though. (Thanks to Neelesh Shah for pointing out this new feature.)
I thought tonight was going to be the night. I purchased a macbook pro about 6 weeks ago. I've slowly been getting familiar with the mac, and realizing that I'll probably still need a PC image at least occasionally. Last night I installed VMware Fusion for the mac (nice that it's called Fusion, that way it almost has something to do with this Oracle blog). Tonight I was to create the image from my work laptop and test it out on the macbook. Alas, try as I might, I still don't have an image. This is my story.
I have a client in the northeast of the US. If you are really good with the Oracle DB, pl/sql and APEX, and live or want to move to the US Northeast, send me your info and I'll pass it along. Please send an email with your resume to me (anton) at work (concept2completion.net).
When I started this blog I decided I would only blog about things you could not find reasonably easily with a simple google search. This post violates that rule. Cloning a schema is something that I do fairly frequently but not frequently enough that I remember the exact syntax. First I googled it every time, later I created a little text file with the commands. Having already gone to the trouble to write a text file, I might as well just paste it in here. I use datapump. In the example below I want to export the SCOTT schema which has all of its data in the USERS tablespace.
I will be speaking at APEXposed 2009 in Atlanta, GA on 10 & 11 November. Some of you have certainly seen the How to Hack an APEX Application presentation. I will be giving a revised version of that. It is difficult to find interesting things because the APEX developers keep adding features to make it harder for developers to get into trouble, but I'll have a few items of interest, plus the old standby's.
If you are migrating a portal repository, possibly from Production back into Dev or vice versa you may run into the following error when running ptlconfig
STEP 1 : Populating Portal seed in OID Connected. Creating Lightweight User Accounts and Groups in OID Portal schema version is: 10.1.2.0.2 Error code : -6502 Error message: ORA-06502: PL/SQL: numeric or value error ERROR: creating lightweight users and groups in OID ... exiting
I was recently presented with a sql challenge. Although I came up with something that works, I'm not convinced it is the best solution. This is a very long post that is mostly question, but possibly a helpful example. The details are below.
I was given a table with the following columns ID -- Primary Key DIVISION -- Identifier of a Division START_DATE -- Date when the Division starts END_DATE -- Date when the Division ends
I recently had a client pose the following question to me.
We have a system that generates pdf's based upon data from our Oracle database. The pdf's are then stored in a BLOB column in a table. We recently realized that at least one of those pdf's was corrupt. Is there a way within Oracle to determine if others are corrupt?
It's been a while since I made a real post, one that has some technical merit. This one just barely qualifies.
There is a nice classic report type "Function Returning a SQL Query" which allows you to write a block of code that returns a select statement. That select statement is the basis for the report. I often use it to simplify building a where clause. It might look something like this:
declare l_q varchar2(32767); -- This is the return variable. It will hold a select statement
I try to avoid advertising in this blog, but I'm going to break that rule as I have had a few inquiries lately on these topics. C2 has new product offerings that provide RSS feeds for Oracle Portal and APEX. For details, contact C2. C2 also has a web traffic capture and reporting tool. This tool can be used on any site, but has a few specific capabilities related to Oracle Portal and Application Express. Again, for details, contact C2.
Just a quick note regarding the 3.2 import process... APEX added a number of enhancements and checks during the import of an application. The downside is that these introduced some negative performance characteristics. If you are importing a medium to large size app you are likely to get either a timeout or an error message: ERR-3331 This page was already submitted and can not be re-submitted.
Oracle has confirmed the problem and a fix is in the works. I'll update this post when the patch is available.
Just a quick note about a little known new feature in APEX 3.2: save state before branching. When creating a branch and setting items with values, you now have the option to either pass those values in the URL or to have APEX set them into session state prior to doing the branch.
We went through the upgrade to 3.2 last night and it was very smooth. We had a couple minor challenges but that is because of our unique environment.
1. We have an SSO configuration. APEX does not specify the owner of the package wwv_flow_custom_auth_sso when it generates the return link for SSO. This means that the APEX_PUBLIC_USER (or whatever user your DAD is configured to use) must have a synonym to wwv_flow_custom_auth_sso. You can also create a public synonym so long as one does not exist for another application.
Application Express 3.2 has been advertised as a Forms Conversion release, but I've recently been looking over an additional category of enhancements that are due to be in the next early adopters release. It probably comes as not surprise to those who know me that the category is security. Below is a partial list of the enhancements as I understand them. Once I get my hands on the EA release I'll provide more significant commentary, but I like what I see so far.
One of the best features of APEX is its ability to manage session state for you. One of the trickiest things you run in to when getting in to advanced development, though, is understanding just when session state is updated and when it is persisted. The easiest way I can describe this is with some examples:
If you are using Oracle Portal and are used to using the Portal Forms Builder, you may want to create parameter forms for your BI reports in the Portal Forms Builder. This article provides a methodology for generating and redirecting to the BI Publisher URL from a Portal Form.
Step 1: Create a package that will do the redirect for you given the appropriate parameters.
-- -- create or replace package bip1 as g_url varchar2(32767);
Many have already posted about the tragic loss to the APEX development team and community. I feel the need to add my own short tribute--though it will certainly take me time to collect my thoughts. For now, simply, "We already miss you, Carl."
We have done a lot of work at C2 over the past few years integrating Portal with APEX (security and themes/ui), APEX with OID/SSO, OID with MS Active Directory (AD), APEX with AD, APEX with SSL LDAP . . . Well, you get the idea. We finally put this experience into a couple of training modules. If you are interested in these topics, click on the C2 Consulting logo at the right.
We have done similar work with all of the above and Oracle Universal Content Management (UCM) but have not integrated that into the course. Let me know if you think that would be worthwhile.