Item ID (P9999_USERNAME) is not an item defined on the current page.

If you are hitting this weird problem when trying to login to your APEX app running on Oracle ADB 23ai:

Item ID (P9999_USERNAME) is not an item defined on the current page. 

According to Oracle APEX development team members this seems to be related to an issue with the database result cache mechanism that can be fixed by executing this procedure as SYSDBA (ADMIN user on ADB):

begin dbms_result_cache.flush; end;

You can find the whole story about the problem on this forum thread.

Now, I am not completely clear if this problem was fixed at some point and then popped up again on a more recent version of Oracle 23ai, in my case ADB is version 23.9.0.25.08 and APEX has been recently upgraded to 24.2.8. 

I am glad I quickly found the workaround this morning as it was really driving me crazy.

PS: The same caching bug seems to affect also APEX_EXEC.OPEN_QUERY_CONTEXT, that is if you change the query in parameter p_sql_query, the new query will be ignored and the "cached" will continue to be executed. 

Houston, we've got a problem with JSON PUT method

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In brief, in Oracle 12.2.0.1 there is some problem with PUT method of JSON_OBJECT_T, for some reason numbers are not recognized as such, but they are stored as scalar.
The workaround is to use JSON_OBJECT_T.PARSE in combination with JSON_OBJECT SQL function for entering numbers (see the script below).
Unfortunately, in turn, JSON_OBJECT_T.PARSE won't recognize dates and timestamps as such, but this behavior is explained in the documentation so it doesn't come as a surprise at least.
See also a previous entry regarding the curious handling of dates and timestamps.


Hopefully Oracle will fix this in a future release.

set SERVEROUTPUT ON
declare
  je     json_element_t;
  jo     json_object_t;
  keys   json_key_list;
begin
  jo := new json_object_t;

  dbms_output.put_line('PUT method doesn''t store "value" as number, but as scalar');
  jo.put('a_string','year');
  jo.put('a_number',2019);   -- this is not stored as number but as scalar, casting as number doesn't fix it.
  jo.put('a_boolean', true);
  jo.put('a_date', sysdate);
  jo.put('a_timestamp', cast(systimestamp as timestamp));  -- curiously you need to cast this value to get it as timestamp

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;
  
  dbms_output.put_line('');
  dbms_output.put_line('workaround using PARSE method, correctly stores value as number, it doesn''t recognize a_date as date or a_timestamp as timestamp but as string (this is expected and documented however)');
  jo := json_object_t.parse(json_object('a_string' value 'year', 'a_number' value 2019, 'a_boolean' value false, 'a_date' value sysdate, 'a_timestamp' value systimestamp));

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;

end;
/

The amazing ANSI join syntax quirk

The kind of quirks I love: those that you can find a workaround for without having to wait for a patch.

I am talking Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production running on some unix flavor (I ignore which flavor, I've no access to the unix box).

You execute the following query and it works.

select f.cuaa,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

Then you execute:

insert into mp_part (cua, id_fgra, id_cons, id_part, id_prt, shape_sim, cod_na, fol, part)

select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

SQL Error: No more data to read from socket
it caused a core dump.

Then you try 

create table test as

select f.cua,f.id_fgra, d.id_cons, c.id_part, p.id_prt, 

       sdo_sam.simplify_geometry(p.shape,0.005,2) as shape_sim,

       p.cod_na, p.fol, p.part

  from pcat c

  join cons d on (d.id_fgra = c.id_fgra)

  join cons_part t on (t.id_cons = d.id_cons and t.id_pcat = c.id_pcat)

  join fgra f on (f.id_fgra = d.id_fgra)

  join trkc k on (k.id_prt = c.id_part)

  join part p on (p.id_prt = k.id_prt)

where f.cua = 'AFVTZZ78M11C499O'

 and k.fin > sysdate

 and p.fin_val > sysdate

 and c.init < sysdate

 and c.fin > sysdate

 and c.valid < sysdate;

and it works without a hitch.

It turns out that the problem is with the INSERT SELECT ANSI join syntax combined with a spatial function call in the projection list.

If I rewrite the query with the traditional Oracle syntax, it runs smoothly.

SQLDeveloper 4.2 problem with some bind variables values

I just found out that SQLDeveloper version 4.2.0.17.089 (tested on windows 10) might execute SQL or PL/SQL code containing bind variables with wrong argument values when such values are strings (VARCHAR2) containing just digits with leading zeros (i.e. international phone numbers, VAT codes, UPC codes...) entered at the bind variables prompt.

This seems to happen only with PL/SQL code or certain SQL containing statements like INSERT, not pure SELECTs.

The practical consequences of this problem range from statements ending with unexpected errors or executing a block of PL/SQL with wrong parameter values which could lead to a variety of anomalies like inserting, deleting or updating the wrong rows.

You can easily see the problem by yourself executing the following sample code:

create or replace function function_returning_collection(
   p_arg in varchar2)
   return ORA_MINING_VARCHAR2_NT
as
   l_str_tab       ORA_MINING_VARCHAR2_NT;
begin

   select str
    bulk collect into l_str_tab
    from(
      select '01234567' as str
        from dual
       union all
      select '31234567' as str
        from dual
      union all
      select '01234567' as str
        from dual)
    where str = p_arg;
   
   return l_str_tab; 
end;

create table test_bind (str varchar2(255));

insert into test_bind
select * from table(function_returning_collection(:var));

Enter 01234567 at SQLDeveloper's prompt and it will say "0 rows inserted", then execute just the SELECT portion of the query and it will return two rows instead.

Embedding the number within tick marks at the prompt won't fix the problem, SQLDeveloper is clearly taking the input value verbatim as a string.

I believe the only clean way to fix this in a future release is to allow the user to specify the data type being entered at the prompt, because looking at SQLDeveloper's statement log it's clear that SQLDeveloper is trimming the leading zero and then, owing to the implicit conversion, adding a leading blank, which transforms the initial "01234567" into a "_1234567" ( where "_" represents the blank character).

Note also that SQLDeveloper's online help makes no mistery of this "feature":

Execute Statement executes the statement at the mouse pointer in the Enter SQL Statement box. The SQL statements can include bind variables and substitution variables of type VARCHAR2 (although in most cases, VARCHAR2 is automatically converted internally to NUMBER if necessary);