Component view in Apex 5.1

So I’m a dinosaur. I don’t care if I’m not one of the cool kids. I like component view, I started with component view, I’m comfortable in component view and I get the “flow” of component view.
Of course I’m using page designer, but when things get “buggy”, I switch in dinosaur-modus.

And than there is Apex 5.1….
Where is my component view?

First, there is Two pane mode
1_switch_to_two_pane
For me that is a big step in the right direction. I’m not always in the position to ask for a 24″ monitor for development. So, less is more in this case.
And look
2_two_pane_layout
Component view!

But it’s not the same. This Component View imposter doesn’t give me the original page for e.g. a region. It jumps to the right section of the page-designer.
Not fair! That’s cheating! …. given that it is also very handy and perhaps my new MO.

But look in the upper right corner.
3_goto_preferences
Here you can set some developer specific preferences, like

4_enable_component

This will change the original panel to
4_legacy_button

Legacy, deprecated, whatever. It’s still there.

Like I said, I will be using Two Pane Mode as default with component view as my prefered way of developing (it could be nice if I could set this somewhere in my preferences),
but it’s comforting to know I can still switch “back”.

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.

 

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.

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.

Oracle PL/SQL integration with Slack

Ever heard from Slack.com? Slack is the (not “a” but “the”) platform for team communication. It’s a sort of mixture of email, messaging and a lot more. Well, just go to their website. They are a lot better in explaining their own product of course.

For me it got interesting when I saw their API.
I came up with the idea of setting up a channel within our team and let our database post messages on this channel when some triggering event happen.

Now I get whatsapp-like messages on my mobile phone, tablets and desktops from our database. These are the things that makes me very happy!

This is how I did it:

Step 1 – Set up your company
Just go to Slack.com and follow the wizard. It is very self-explaining.

Step 2 – Set up a channel
You will get two channels: #random and #general.
For this demo I created a third channel #notifications by just clicking the “Create a channel” link. slack1

Step 3 – Configure the integration
Click on the name of your company and select “Configure integrations”
slack2

Just look at the wealth of services they offer. Scroll down to the “DIY Integrations & Customizations” and add a Slack API.
You will be directed to https://api.slack.com/. Just read it.
For now go to the section “Authentication” and add a token.

This token is what you need to authenticate the database as a client. (Yes, it will authenticate itself as you).

Step 4 – Get the certificate(s)
The API I use is a https-call that will be implemented in PL/SQL. For this we will need to store some certificates in an Oracle Wallet.

I use chrome as a browser. Other browsers have comparable functionality.

  • Go to https://api.slack.com/
  • Right-click the green lock and go to the certificates. This one is in Dutch but you will get the picture.
    slack3
  • Export the GeoTrust Global CA and the GeoTrust SSL CA-G2 certificates to file as Base64 encoded x.509 (*.cer)
    You don’t need the leaf certificate *.slack.com

Step 5 – Create a wallet
Using the Oracle Wallet Manager open your wallet or create a new wallet. You don’t need a certificate request.
slack4Under “Operations” choose Import Trusted certificate and import both *.cer files we created before.
slack5
Save the wallet to e.g. E:\oracle\Wallet, open the Wallet menu again and enable Auto login. If you don’t do this you will have to embed the password of the wallet in your PL/SQL.
slack6But what if you are using Oracle XE and you don’t have Wallet manager?
Than it’s good to know that the wallet is “just” an PKCS#12 file. Take a look at this excellent post https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/ that explains how to use Openssl for creating the

So execute

  openssl pkcs12 -export -in slack.cer -out ewallet.p12 - nokeys 

and place this wallet in a directory. E.g. C:\Oracle\Wallet ( remember your password in this case. You will need it when you set your wallet in PL/SQL

Step 6 – Create a procedure in the database
The following PL/SQL implements the chat.postmessage API from slack to send a message to the #notifications channel. It uses the wallet in E:\oracle\Wallet with autologin enabled:

declare
  p_url            varchar2(2000):= 'https://slack.com/api/chat.postMessage';
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
  l_token          varchar2(1000)  := 'thetokenfromtheslackapisite';
  l_param          varchar2(32676) := 'channel=#notifications'||chr(38)
                                    ||'text=Hello world from the database';
BEGIN
  utl_http.set_detailed_excp_support ( true );  
  utl_http.set_wallet('file:E:\oracle\wallet');
  -- 
  l_http_request  := utl_http.begin_request
                       ( url=>p_url||'?token='||token
                       , method => 'POST'
                       );
  utl_http.set_header
    ( r      =>  l_http_request
    , name   =>  'Content-Type'
    , value  =>  'application/x-www-form-urlencoded'
    );				
  utl_http.set_header 
    ( r      =>   l_http_request
    , name   =>   'Content-Length'
    , value  =>   length(l_param)
    );
  utl_http.write_text
    ( r      =>   l_http_request
    , data   =>   l_param
    );
  -- 
  l_http_response := UTL_HTTP.get_response(l_http_request);
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body 
    THEN
      utl_http.end_response(l_http_response);
  END;
EXCEPTION
  WHEN OTHERS 
  THEN
    utl_http.end_response(l_http_response);
    RAISE;
END;

Happy testing!

Tables with row archiving in Oracle 12c … bit 47

This week I followed the Oracle 12c New Features course in the Netherlands by Harald van Breederode. In this course the new row archiving feature was mentioned. Very interesting feature, but if you want to look up which table has row archiving enabled, it seems that the only thing you can do is to look up all tables with an ORA_ARCHIVE_STATE column present.

That seems odd to me, because the feature is a property of the table, so CDB/DBA_TABLES should have a column indicating the feature. The following PL/SQL block, executed as sys in a 12.1.0.1.0 mirrors the investigation I did.

declare
  /* 
    Create a table in another schema.
    When you don't, the alter table will give an 
    ORA-12988: cannot drop column from table owned by SYS.
    and we are dropping the hidden column ORA_ARCHIVE_STATE 
    from our new table when we alter the row archival

    output:

    Version: 12.1.0.1.0
    Before : 140755205095424 Bits unset: 29 34 47
    After  :     17716740096 Bits unset: 29 34

    EDWIN           no archival
    EDWIN_BENCHMARK with archival    
  */
  cursor c_prop
  is
    select t.property 
    from   tab$ t
    ,      obj$ o
    where  o.obj# = t.obj#
    and    o.name = 'EDWIN'
  ;
  l_after   tab$.property%type;
  l_before  tab$.property%type;
  l_version v$instance.version%type;
  -- 
  function BITS_UNSET
    ( p_n number 
    ) return varchar2
  is
    l_power  number;
    l_return varchar2(4000);
  begin
    for l_i in 0..126
    loop
      l_power := power(2,l_i);
      -- 
      if ( l_power > p_n )
      then
        exit;
      end if;
      --
      if bitand(p_n,l_power) != 0
      then
        l_return := l_return||' '||l_i;
      end if;
    end loop;
    return trim(l_return);
  end;        
begin
  select version into l_version from v$instance;
  dbms_output.put_line('Version: '||l_version);
  --
  begin execute immediate 'drop table scott.edwin'; exception when others then null; end;
  begin execute immediate 'drop table scott.edwin_benchmark'; exception when others then null; end;
  --
  execute immediate 'create table scott.edwin_benchmark (kol number) row archival'; 
  execute immediate 'create table scott.edwin (kol number) row archival'; 
  --
  open c_prop;
  fetch c_prop into l_before;
  close c_prop;
  dbms_output.put_line('Before : '||l_before||' Bits unset: '||BITS_UNSET(l_before));
  --
  execute immediate 'alter table scott.edwin no row archival';
  --
  open c_prop;
  fetch c_prop into l_after;
  close c_prop;
  dbms_output.put_line('After  : '||l_after||' Bits unset: '||BITS_UNSET(l_after));  
  --
  for r_check in 
    ( select o.name
      ||     decode( bitand(t.property, power(2,47))
                   , 0, ' no archival'
                   , ' with archival'
                   ) as text
      from   tab$ t
      ,      obj$ o
      where  o.obj# = t.obj#
      and    o.name in ('EDWIN','EDWIN_BENCHMARK')
    )
  loop
   dbms_output.put_line(r_check.text);      
  end loop;             
end;    
/

So, the flag is bit 47 in the property column of tab$.

I guess we can wait for a future upgrade of 12c where they unlock this information. In the mean time, you can use the information above for a custom view….or just look for the existence of ORA_ARCHIVE_STATE.

Kernel32.dll with Oracle 12c

In OS commands in Oracle using Kernel32.dll I explained a way to link the kernel32.dll library to execute host commands.

But what if you had to migrate to the latest 12c version (64 bits) and you don’t want to rebuild your application to use the new features at the same time.

Well, it seems that it is still working the way I explained, but with one addition:

In %ORACLE_HOME%\hs\admin\extproc.ora set EXTPROC_DLLS to ANY (or any value that is appropriate for you).

Without it you will get an ORA-28595: Extproc agent : Invalid DLL Path