ORA-00600: with arguments: [12811], [154970]

We encountered this bug on Windows 12.1.0.2.0 Enterprise Edition.

When dropping a table and you get a ORA-00 600 with arguments: [12811] and [154970], you will have to check your application for the following sequence of events:

– You created the table with an identity column GENERATED BY DEFAULT ON NULL AS IDENTITY
– you have code that tries to alter the value the sequence by an alter table statement in dynamic sql.

When the alter table results in an error, the sequence is dropped and you are stuck with a corrupted database.

Don’t do this in a production database, as we haveūüė¶

CREATE TABLE edwin_test2
   (  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
      MINVALUE 1 MAXVALUE 9999999999999999999999999999 
      INCREMENT BY 1 START WITH 179193 CACHE 20 NOORDER  
      NOCYCLE  CONSTRAINT edw_con2 NOT NULL ENABLE, 
      CREATION_DATE date, 
      CONSTRAINT edwin_test_pk2 PRIMARY KEY (ID)
      USING INDEX  ENABLE
   ) NO INMEMORY ;

declare
  l_max_id number:= 10;
begin
   insert into edwin_test2 
     ( creation_date
     ) 
   values 
     ( sysdate
     );
   commit;
   -- 
   l_max_id := -1;  -- force the error
   --
   -- Let's blow things up...
   execute immediate 
   'alter table edwin_test2  modify id number '
   ||'generated by default on null as identity '
   ||'( start with ' ||l_max_id || ' )';
end;
/

drop table edwin_test2;

You can check if you’re have this corruption by the following script:

declare
  cursor c_seq
    ( cp_name in varchar2
    )
  is
    select 'exists' bla
    from   dba_sequences seq
    where  seq.sequence_name = cp_name
  ;
  r_seq c_seq%rowtype;
begin
  for r_i in 
    ( select col.table_name
      ,      col.data_default as seq
      from   dba_tab_columns col
      where  col.identity_column = 'YES'
      and    not exists ( select 1
                          from   dba_views vw
                          where  vw.view_name = col.table_name
                        )
    )
  loop
    r_i.seq := substr( r_i.seq, instr(r_i.seq,'.')+2);
    r_i.seq := substr( r_i.seq, 1, instr(r_i.seq,'"')-1);   
    --      
    r_seq:= null;
    open c_seq(r_i.seq);
    fetch c_seq into r_seq;
    close c_seq;
    --- 
    dbms_output.put_line
      ( rpad(r_i.table_name,40,' ')
      ||r_i.seq
      ||' '
      ||nvl(r_seq.bla, ' ------ohoh!')
      );
  end loop;
end;               

To handle this corruption, just open a SR with Oracle. They should be able to help you.

No, recreating the sequence by hand doesn’t work. I tried. The sys.seq.FLAGS column has¬†a value of 8 for a hand-created sequence and 40 for an identity column; I see no problem in selecting from the datadictionairy, but updating it is even for me a big NO.

 

ora-01403 in wwv_flow_api.import_begin

Two new databases, two fresh new apex installations. Yeah! One for development, one for test. Let’s create a new Workspace X in every environments.
Everything is ready, let the development begin!

After a while the first iteration of the application was ready and we imported it in the the second environment and everything failed miserably.

The site exploded in an error page giving the dreaded ORA-01403 after the first statement in the install script WWV_FLOW_API.IMPORT_BEGIN.

But after running the script in SQLPlus with spooling enabled I got the following :

ORA-02291: Integriteitsbeperking (APEX_050000.WWV_FLOWS_FK) is geschonden – bovenliggende sleutel
is niet gevonden.
ORA-06512: in “APEX_050000.WWV_FLOW_API”, regel 2750
ORA-06512: in regel 2

That’s dutch for an integrity constraint violation, parent key not found

Digging into dba_constraints it gave me that the workspace was not found.

The provisioning_company_id of wwv_flow_companies for workspace X had a different value than development!

Note to myself: when deploying into a new environment, don’t create the workspaces by hand, but import it from a source installation.

Note to the APEX development team: it wouldn’t kill you when you stop the process with a simple message like ‘Workspace-id XXXXXX not found’ instead of “I didn’t find it” without giving¬†the “it” some meaning.

Node.js scripts for Oracle Cloud Storage Service

Working with Oracle Cloud Storage Service I noticed that it’s not really customer-ready (in my humble opinion).

e.g. Creation of a storage container is not yet supported from the dashboard. You’ll have to create a container using a magical Java library or a REST-API using Curl.

But we are on Windows.

So we don’t have Curl.

And I refused to install Cygwin just for this purpose.

However, node.js is installed in our Windows environment, so I created a small repository of node.js scripts to handle some of the basics of the Oracle Cloud Storage Service.

For everybody who is interested : https://github.com/emoracle/OracleCloudStorage

APEX 5 – The title of a modal dialog

Is it bug or is it a feature? You tell me.

When you create a modal page in Apex 5, the title of the dialog is created before any change to session state. Dynamic titles, based on session state will therefore be funky.

Take this example.

1. Create a new page
2. Select as page type Form > Form on a Table with Report
3. next, next … until the Form page and set the property of the page mode to “Modal Dialog”
4. etc ect, finish

This creates a multirecord report with edit links.
When you click an¬†edit link a dialog appears with the default title “Form on DEPT” (let’s say we had chosen DEPT as table).

But now I want to include the DEPTNO¬†of the record in title. Set the property Title e.g. to “DEPT &P2_DEPTNO.”

Go to the report, click a record…. the deptno¬†in the title is empty…
Go to the report, click another record… the title is not the deptno¬†of the record you clicked, it is the former deptno.

Like I’ve said, is it a bug or a feature?

What happens:

The Apex team implemented the dialog page as an iframe within a jQuery dialog.
The links that are generated in the report look something like:

javascript:apex.navigation.dialog('f?p=x:x:x::NO::P1_DEPTNO:x
,{title:'DEPT ',height:'500',width:'720',maxWidth:'960',modal:true,dialog:null}
,'t-Dialog--standard',apex.jQuery('#Rx'));

And that is the problem in a nutshell. When the links in the report is generated, a JavaScript object is also generated for the dialog with e.g. the title as a property. This title is based on the current session state. However, the first argument, the link for the iframe, will alter session state to a new value. But that value is not propagated to the title of the dialog.

How to alter (solve?) this behaviour?

On the global page 0 I have created a Dynamic action, on Page load, executing the following JavaScript:

(function($){
   if ( $("body").hasClass("t-Dialog-page")) {
     $(".ui-dialog-title", parent.document).html($("title").html());
   }
})(jQuery); 

When in a dialog page, take the title of the page and replace the title of the parenting dialog.

The only issue I have with this solution is that sometimes you can see the title change. But still, it is better that nothing.

gReport.search in APEX 5

yes, gReport was undocumented, but it was sooo handy when you wanted to refresh your interactive report from within javascript.

Just google for it and you will find dozens of examples using gReport.search(‘SEARCH’);

And then you upgraded to APEX 5

Because now we can have multiple IR’s on one page and the team made the IR a jquery widget, gReturn¬†no longer exists.

It’s a widget, so this has worked for me:

 

$('#your_static_id_of_the_report_ir').data('apex-interactiveReport')._search();

So If your static Id is “batchRuns” it becomes:

  $('#batchRuns_ir').data('apex-interactiveReport')._search();

And of course, it’s not documented, you’re not supposed to use the private method, but until the APEX team creates a public method to refresh my IR given the static Id of the region, I will be using this.

Until the next upgrade…

 

Be careful with dual and functions

Because I wanted an UUID I played around with sys_guid() and stumbled upon something that was new for me.

select substr(l,1,8)   ||'-'||substr(l,9,4)||'-'
     ||substr(l, 13, 4)||'-'||substr(l,17, 4)||'-'
     ||substr(l,21, 12) as final
,     l
from
( select sys_guid() as l
  from dual
) sub

The statement gave two different results!
So sub.l is not equal to sub.final minus the minus signs.

But when I do this:

select substr(l,1,8)   ||'-'||substr(l,9,4)||'-'
     ||substr(l, 13, 4)||'-'||substr(l,17, 4)||'-'
     ||substr(l,21, 12) as final
,     l
from
( select sys_guid() as l
  from other_table_with_1_row
) sub

The results are equal.

My hypothesis was that the fast dual rewrite replaces every occurrence of sub.l with sys_guid(). So, sys_guid is handled six times as a constant, but of course it isn’t.

drop sequence edwin_seq;

create sequence edwin_seq start with 1 increment by 1 nocache;

drop function edwin_f;

create function edwin_f
return number
is
begin
  return edwin_seq.nextval;
end;  

select l + l as sum
,      l
from 
(  select edwin_f as l
   from   other_table_with_1_row
) sub

The resultset is that the sub.sum equals 2 and sub.l equals to 1.
But now do the same thing with dual:

drop sequence edwin_seq;

create sequence edwin_seq start with 1 increment by 1 nocache;

drop function edwin_f;

create function edwin_f
return number
is
begin
  return edwin_seq.nextval;
end;  

select l + l as sum
,      l
from 
(  select edwin_f as l
   from   dual
) sub

Now sub.sum equals 3 and sub.l also equals to 3.
This is because the internal algorithm handles the function as a constant and the resultset is calculated as

first-column  = edwin_f + edwin_f = 1 + 2 = 3
second-column = edwin_f = 3

Every time the function is called the next value of the sequence is returned.

So, be careful with the use of dual in combination with functions that do not return constant values.