After watching my screencast, the customer I was working with to help build the quick demo for the Oracle Life Sciences User Group tomorrow was following my steps meticulously, but still running into an error at runtime. Everything about our environments was identical except one thing: he was using a production version of Oracle 10g version 10.1.0.2, and I happened to have an internal build of Oracle 10g version 10.2 on mine. Hating the thought that some minor niggle would prevent him from showcasing JDeveloper and ADF as part of his presentation, I again fired up my trusty Oracle Web Conferencing so he could show me the problem in his environment.
He was trying to execute the following query using an ADF view object, leveraging the Oracle 10g BLAST functionality of the Oracle Data Mining Option which help scientists analyze sequences of DNA and proteins for best matches...
select *
from TABLE(BLASTN_MATCH (
:0, -- query_sequence (Zero-th VO Bind Variable)
CURSOR(SELECT seq_id, seq_data FROM ecoli10), -- seqdb_cursor
1, -- subsequence_from
-1, -- subsequence_to
0, -- FILTER_LOW_COMPLEXITY
0, -- MASK_LOWER_CASE
10, -- EXPECT_VALUE
0, -- OPEN_GAP_COST
0, -- EXTEND_GAP_COST
0, -- MISMATCH_COST
0, -- MATCH_REWARD
11, -- WORD_SIZE
0, -- X_DROPOFF
0)) -- FINAL_X_DROPOFF
t where t.score > 25
It turned out that the error he was getting at runtime was't related to ADF at all, but instead was an error from his database:
ORA-22905: cannot access rows from a non-nested table item
I set out to use a DOS shell on his machine over the Oracle Web Conference to see if I could reproduce at the SQL*Plus command line -- factoring ADF out of the picture. The BLASTN_MATCH() function is declared to return a type DMSYS.DMBMOS, which is defined to be a TABLE OF DMSYS.DMBMO objects. When we tried the same query without the bind variable, hard-coding a scalar string value into the query, it worked fine. Somehow, introducing the bind variable confused the 10.1 database so that its SQL parser no longer knew the function returned a table-typed expression. In my 10.2 database, both static and bind-variable versions worked without an error. I found Bug 4227553 which sounded relevant to the problem, relating to getting an ORA-22905 in similar circumstances, which indicated it was fixed in Oracle 10.2 and suggested that we add an explicit CAST() function into the query to avoid the error. We follow this suggestion and changed the query to look like this instead:
select *
from TABLE(CAST(BLASTN_MATCH (
:0, -- query_sequence (Zero-th VO Bind Variable)
CURSOR(SELECT seq_id, seq_data FROM ecoli10), -- seqdb_cursor
1, -- subsequence_from
-1, -- subsequence_to
0, -- FILTER_LOW_COMPLEXITY
0, -- MASK_LOWER_CASE
10, -- EXPECT_VALUE
0, -- OPEN_GAP_COST
0, -- EXTEND_GAP_COST
0, -- MISMATCH_COST
0, -- MATCH_REWARD
11, -- WORD_SIZE
0, -- X_DROPOFF
0) AS DMSYS.DMBMOS)) -- FINAL_X_DROPOFF
t where t.score > 25
and we were back in business! One last little step we had to do was to connect as DMSYS and GRANT EXECUTE on the DMBMOS type to PUBLIC so that the SQL parser would allow us to refer to it in the CAST() statement when logged on as another user like SCOTT. After figuring out this database-level issue, the quick ADF application built following my screencast worked great and the show could go on tomorrow. I even got a adhoc, over-the-phone tutorial on protein sequences in the process!
4:10:57 PM 
|
|
