Otherwise reported as “WWV_FLOW_FORM_REGION.NO_PRIMARY_KEY_ITEM” or merely “Internal error”, this bug caused me to waste hours of investigation, twice, because a simple Google search was not returning any results. This post is merely to remedy that situation.
On an APEX 19.1 page with a new Form region combined with an Interactive Grid region, when the user makes a change to a record and Submits the page, they get this error.
This is caused by bug 29612553 – FORM REGION DML PROCESS THROWS INTERNAL ERROR WHEN INTERACTIVE GRID DML PROCESS IS ON THE SAME PAGE and fixed by APEX patch 29369869.
Here is a short story about a little problem that caused me a bit of grief; but in the end had a simple cause and a simple fix.
I had a dynamic action in my APEX 4.1 app that had to run some PL/SQL – which was working fine, except the PL/SQL kept on getting longer and longer and more complex; so quite naturally I wanted it to be encapsulated in a database procedure.
I did so, but it didn’t work: the page ran without error, but it seemed like the dynamic action wasn’t firing. It was supposed to change the value of some display items on the page in response to the change of a radio button item, but now they weren’t changing! There was no debug warnings or logs to give a hint either. I tried using Chrome’s developer tools to trace it but that just showed me a very high-level view of what the client was doing, and didn’t report any errors or warnings.
I reverted to my original code, and it worked fine. Ok, so that means it’s probably a problem with my procedure.
I checked and rechecked my procedure. Didn’t seem to be anything wrong with it. I added a line into the procedure to raise an exception. The APEX page dutifully reported the PL/SQL error in the Ajax call – which means that my procedure was being called successfully. Also, I included the return values in the exception message, and this proved that my procedure was correctly determining the values. They just weren’t being returned to the items on the page.
I tried raising an exception in the apex dynamic action’s PL/SQL Code. That worked. The exception message correctly showed the new values were being returned; they still weren’t being populated on the page.
I tried removing all the items from the Page Items to Return setting; then I gradually added them back in, one by one. I narrowed it down to just one item. If I included that item, none of the items were being updated when the procedure returned. If I excluded that item, all the other items were correctly being updated when the procedure returned. Of course, that wasn’t a solution, because there was a cascade of other dynamic actions that were dependent on that particular item, so it has to be updated.
After lunch and a short walk, it occurred to me: unlike the other parameters, that particular parameter was anchored to a database column defined as CHAR(1). Could that be a problem?
Sure enough, when I changed the parameter’s data type from column%TYPE (which mapped to a CHAR) to just a plain VARCHAR2, everything worked.
Yet another reason to avoid CHAR, I guess.
This is just in case you come across this. It appears to be an Oracle bug. I don’t know if it’s been fixed in a later release. I haven’t isolated a simple repeatable test case yet, so I don’t know if the scenario here includes all the relevant details or not.
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
Scenario:
- MERGE into a table that has one or more virtual columns.
- The MERGE statement has a “LOG ERRORS” clause.
- The error log table is a standard one created using DBMS_ERRLOG.create_error_log.
Other factors that may or may not be involved are:
- the target table is in a different schema
- the merge statement queries another table with the same name in the current schema
- the merge statement includes WHEN MATCHED THEN UPDATE, DELETE WHERE, WHEN NOT MATCHED THEN INSERT cluases
- the merge statement has a WHERE clause on the WHEN NOT MATCHED clause
Result:
ORA-03113: end-of-file on communication channel and/or ORA-03114: not connected to ORACLE
This happens whether the MERGE is executed or just explain-plan’ed – so it shouldn’t be related to the actual data in either the source or destination tables.
Workaround:
After creating the log table, but before the merge, drop the columns from the error log that are virtual columns in the target table.
This query crashes the session when it’s run or parsed.
I haven’t checked if this appears in other versions or not.
SQL> conn scott/tiger@orcl
Connected.
SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production
SQL> explain plan for with q as
2 (select ‘x’ d from dual
3 union all
4 select ‘y’ from dual)
5 select d from q
6 union all
7 select d from q
8 order by dbms_random.value;
explain plan for with q as (select ‘x’ d from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
The crashes happened when I added the “order by dbms_random.value”. The problem seems to be in the optimiser since I get the same results whether I run the query or just explain it. Possibly something to do with the optimiser wrongly assuming the random function is deterministic?
There’s a simple workaround, so I’m not too worried. I just change it to this and it works fine:
with q as
(select ‘x’ d from dual
union all
select ‘y’ from dual)
select d, dbms_random.value o from q
union all
select d, dbms_random.value o from q
order by o;
Jeffrey Kemp
22 November 2007
bug / SQL /
I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.
In the end the only thing that had changed was that a VARCHAR2 which should have been non-null happened to be NULL for one particular row in the table.
Thanks to Connor for the simple test case, listed below.
If you know what might be the cause or reason behind this error, and why it doesn’t occur for dates, I’d be interested.
This was reproduced on Oracle 10.2.0.1.0.
SQL> declare
type t is table of number not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of varchar2(80) not null index by pls_integer;
r t;
begin
select case when rownum < 20 then rownum else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5
SQL> declare
type t is table of date not null index by pls_integer;
r t;
begin
select case when rownum < 20 then sysdate else null end
bulk collect into r from all_Objects
where rownum <= 20;
end;
/
PL/SQL procedure successfully completed.
I came across this at dbdebunk (ON THE NOTHING THAT’S WRONG WITH NULLS with Hugh Darwen, Fabian Pascal). Couldn’t let that go so I tested it under 9i (9.1) and 10g (10.2) and got identical results. The last two statements are logically equivalent, but gives different results!
SQL> create type point as object (x real, y real);/
Type created.
SQL> create table t (p point);
Table created.
SQL> insert into t values (point(null, null));
1 row created.
SQL> insert into t values (point(1, null));
1 row created.
SQL> insert into t values (point(1, 2));
1 row created.
SQL> select * from t;
P(X, Y)
——————————————————————————–
POINT(NULL, NULL)
POINT(1, NULL)
POINT(1, 2)
SQL> select * from t where p=p or not p=p;
P(X, Y)
——————————————————————————–
POINT(1, 2)
SQL> select * from t where not p=p or p=p;
P(X, Y)
——————————————————————————–
POINT(1, NULL)
POINT(1, 2)
According to CM, it looks like a bug. The filter predicates seem to be applied incorrectly by the optimiser:
where ( p=p ) or ( not p=p );
filter(“T”.”SYS_NC00003$”=”T”.”SYS_NC00003$” AND
“T”.”SYS_NC00002$”=”T”.”SYS_NC00002$” OR T.”P”T.”P”)
where ( not p=p ) or ( p=p );
filter(T.”P”T.”P” OR “T”.”SYS_NC00002$”=”T”.”SYS_NC00002$”)
Jeffrey Kemp
11 February 2006
bug / SQL /
If you’re using the SAMPLE clause in Oracle 9i, in combination with a join, be aware of this small gotcha. I found a workaround, thankfully.
select * from (select * from dual), (select * from dual sample (10));
Expected result: should return zero or one row (more or less at random)
Actual result (tested with 9.2.0.6.0): “ORA-30561: SAMPLE option not allowed in statement with multiple table references”
Workaround: put the query with the SAMPLE clause first, i.e.
select * from (select * from dual sample (10)), (select * from dual);
Note: It works fine in 10g (tested under 10.1.0.2.0) since 10g does not restrict how many SAMPLE clauses are in a query.
