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

Mail a screenprint in Oracle APEX

We always build bug-free applications (hehe), but in the extremely rare occasion that the user encounters a bug, they always seem to use words like “it doesn’t” work and nothing more than that.

Well, I like to have more information, so on our site we wanted the user to be able to send some background info and a screen print from within the system.

Personally I like the Team Development framework but for our purpose we needed some lightweight solution. And sending a screen print should be cool, right?

Well, most of the heavy lifting of creating a screen print is done by the library HTML2Canvas

I included an “I want to report a problem” link in the navigation bar entries with an URL-target

  javascript:getScreen();

The javascript function that is called is the following:

function getScreen() {
  if (!!document.createElement("canvas").getContext) {
    html2canvas(window.document.body, {
      onrendered: function(canvas) {
      var dataUrl = canvas.toDataURL("image/jpeg");
      var clobObj = new apex.ajax.clob(
        function(p){
          if (p.readyState == 4){
            var get = new htmldb_Get(null,$v('pFlowId')
                     ,'APPLICATION_PROCESS=mailScreen',0);
            gReturn = get.get();
            alert('Mail has been send');
          }
        });
        clobObj._set(dataUrl );      
      }
    });
  } else {
    var get = new htmldb_Get(null,$v('pFlowId')
              ,'APPLICATION_PROCESS=mailScreen',0);
    gReturn = get.get();
    alert('Mail has been send');
  }  
}

Notice that I check if canvas is supported by the browser. If it isn’t supported we’re not able to create a screenshot and we just send some background information. When canvas is enabled I put the output string in the CLOB_CONTENT-collection.

On our site we are sending the mail to our customer to let them be able to give some more background information. But you could of course also navigate to a new page for that or just email the information directly to you.

The On Demand process handles the mailing part.

declare
  cursor c_variables
  is
    select item_name||' : '
    || apex_util.get_session_state(item_name) AS session_value
    from   apex_application_page_items
    where  application_id = :APP_ID
    and    page_id        = :G_CURRENT_PAGE
  UNION ALL
    select item_name||' : '
    || apex_util.get_session_state(item_name) 
    from   apex_application_items
    where  application_id = :APP_ID
  ;               
  l_id   number;
  l_blob blob;
  l_clob clob;
  l_body clob;
BEGIN
  l_body := to_clob(:APP_USER||', please give some more information before sending it to us'||utl_tcp.crlf);
  l_body := l_body||utl_tcp.crlf;  
  for r_variable in c_variables
  loop
    if ( c_variables%ROWCOUNT = 1 ) 
    then
      l_body := l_body||'Page variables in session state: '||utl_tcp.crlf;  
    end if;
    l_body:= l_body||r_variable.session_value||utl_tcp.crlf;
  end loop;
  --
  /* Add the screen print */
  FOR r_coll IN 
    ( SELECT coll.clob001
      FROM   apex_collections coll
      WHERE  coll.collection_name = 'CLOB_CONTENT'
    ) 
  loop
    dbms_lob.createtemporary(l_clob,false);
    dbms_lob.copy
      ( dest_lob   => l_clob
      , src_lob    => r_coll.clob001
      , amount     => dbms_lob.getlength(r_coll.clob001)
      , src_offset => dbms_lob.instr(r_coll.clob001,',',1,1)+1
      );   
    l_blob := apex_web_service.clobbase642blob(l_clob);
  end loop;
  --
  l_id:= apex_mail.send
      ( p_to  => :app_user||'@mydomain.nl'
      , p_from => 'myapp@mydomain.nl'
      , p_subj => 'Issue for application '
                 ||:APP_ID||' Page '||:APP_PAGE_ID
      , p_body => l_body
      );   
  if dbms_lob.getlength(l_blob) > 0
  then
    apex_mail.add_attachment
     ( p_mail_id    => l_id
     , p_attachment => l_blob
     , p_filename   => 'screenprint.jpg'
     , p_mime_type  => 'image/jpeg'
     );
  end if;
  --
  apex_mail.push_queue;
end;

And again, that’s it!

Oracle Apex Gantt collapse all tasks

When implementing the project Gantt chart in Apex, I thought it was annoying that nested tasks default expanded.
I wanted them to be collapsed by default.

For this I added a listener to the draw event of the AnyGantt object to collapse the Gantt chart:

  AnyGantt._charts.chart__0.addEventListener('draw',function(){
    AnyGantt._charts.chart__0.collapseAll();
  });

This snippet should be executed on load (Dynamic Action, Page JavaScript property, whatever).

In your case, chart__0 could be chart__N. You have to figure it out in your case.

Just a small blog post, but it could be handy at times.

Drag and drop an excel file to an APEX collection.

On apex-plugin.com I found an excellent process type plugin Excel2Collection for uploading an excel file
into a collection. A nice piece of work from Anton Scheffer.

However, in this age of HTML5 our customer wanted to drag and drop his CSV-file instead of uploading it with a button.
So I took the plugin from Anton and tweaked it such that with older browser you get the original upload button, but with
the more current browsers you will be able to drag and drop your file.

First of all you have to follow the instructions that come with the plugin (create a filebrowser item, create a process etc.)
But then….

1. You need a container to drop your file.
I created an HTML Text Region with the source

<div id="dropbox">
<span id="droplabel">Drop file here...</span>
</div>

2. You need some styling. I used

#dropbox {
  width: 425px;
  height: 300px;
  border: 2px solid #DDD;
  -moz-border-radius: 8px;
  -webkit-border-radius: 8px;
  background-color: #FEFFEC;
  text-align: center;
  color: #BBB;
  font-size: 2em;
  font-family: Arial, sans-serif;
}

#droplabel {
  position: relative;
  top: 40%;
}

3. This step depends on what you want to do with your collection. here, we are just showing the results.
To show the collection you can create a classic report, based on the collection you will be filling:
eg. to show the contents of a CSV file with three columns:

  select c001
  ,      c002
  ,      c003
  from   apex_collections
  where  collection_name = 'EDWINS_COLLECTION'

The name of the collection is of course the same name you entered in the attribute of the plugin.

4. The magic I used in the footer of the page

if (window.File && window.FileList && window.FileReader) {
  // Our functionality is supported by the browser
  var dropbox = document.getElementById("dropbox");
  $("#P1_FILE").hide(); //The Id of the filebrowser item
  $('#P1_SUBMIT_BUTTON').hide();
  //
  // If the browser doesn't support draganddrop we have to
  // show the filebrowser and a submit button
  // when supported, don't show them
  //
  dropbox.addEventListener("dragenter", dragEnter, false);
  dropbox.addEventListener("dragexit", dragExit, false);
  dropbox.addEventListener("dragover", dragOver, false);
  dropbox.addEventListener("drop", drop, false);
}
else {
  $("#dropbox").hide(); //No drag and drop supported. Hide the container
}

function dragEnter(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragExit(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragOver(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function drop(evt) {
  var files, count;
  evt.stopPropagation();
  evt.preventDefault();

  files = evt.dataTransfer.files;
  count = files.length;
  if (count > 0)
    handleFiles(files);
}

function handleFiles(files) {
  var file, reader;
  file = files[0];
  $("#droplabel").html("Processing " + file.name);
  reader = new FileReader();
  reader.readAsDataURL(file);
  reader.onloadend = handleReaderLoadEnd;
}

function handleReaderLoadEnd(evt) {
  // This callback will insert the base64 results of the filereader
  // in the CLOB001 column of the CLOB_CONTENT collection
  var clobObj = new apex.ajax.clob( function(p){
    if (p.readyState == 4){
      var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=dummy',0);
      gReturn = get.get();
      apex.submit('TEST');
     //Fire the submit to get the proces plugin working
    }
  });
  clobObj._set(evt.target.result);
}

So when the file is dropped in the container area, the CLOB001 column is filled with the base64 string of the file.
Now alter the code of the plugin from:

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
      raise e_no_doc;
  end;

to

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
     log( 'looking in CLOB_CONTENT'  );
     begin
       /* 
         We didn't use the filebrowser, but the container
       */
       select apex_web_service.clobbase642blob
                (dbms_lob.substr( col.clob001
                                ,  dbms_lob.getlength(col.clob001)
                                , dbms_lob.instr(col.clob001,',',1,1) + 1 
                                )
                )
       into   t_document
       from   apex_collections col
       where  col.collection_name = 'CLOB_CONTENT'
       and    col.clob001 is not null ;
       --
       log ('Found');
     exception
       when no_data_found
       then
         log('Not found');
         raise e_no_doc;
     end ;
  end;

and you are done.

Building a chrome extension for Oracle APEX Builder

As a developer I like to create my own tools to make my work easier. In our current Oracle APEX project we have build a custom authorization mechanism, based on page numbers. A drawback the development team faced was that we had to register every single page in our custom authorization tables. So I came to think that if I could extend the APEX builder with some custom code on the page builder page, it would make our life a lot easier. When working on a new page we could register the page with a simple click.

The technique I describe in this blog can be used to extract information from the APEX Builder and call some stored procedure in the database to do some magic with it.

I wanted to build an extension for Chrome. With some googling I stumbled upon Chrome extensions. As always, the documentation of these Google API’s is excellent.

I find that building a very, very simple extension for Chrome is relatively easy. Of course you can always be overcomplicating things, but following the KISS principle I was able to build an extension that served my purpose.

The idea behind my extension is, that when the extension is clicked, some JavaScript is fired that “gets” the application id and page id from the current tab and sends it to an API on the database to register the page. But the last API can of course do all kinds of magic with the application and page id.

This is what I did:

Create a directory to host the files for the extension. E.g.

D:\ChromeExtensions\AddToAuth

The extension needs a manifest file with the name manifest.json.
My manifest file became

{
  "name": "Add a page to Auth",
  "version": "1.0",
  "manifest_version": 2,

  "description": "With this extension one can add a page from APEX builder to Auth",
  "icons":{"128":"db.png"},

   "browser_action": {
    "name": "Add a pagina to Auth",
    "default_icon": "db.png"
  },

  "background": { "scripts": ["background.js"] },
  "permissions": [
    "http://*/*"
  ]
}

See Formats: Manifest Files for a full description of the fields of a manifest file.

If you look at the manifest two things pop up

  • db.png This is a 128×128 png file that is shown in the browser
  • background.js This is the event page of the extension.

When the extension – a browserAction – is clicked I have to fire some JavaScript. So, the background.js would have to add a listener to the event.
background.js

chrome.browserAction.onClicked.addListener(function(tab) {
  chrome.tabs.executeScript(null, {file:"worker.js"});
});

When the browserAction is clicked, the worker.js is executed
worker.js

var APPL_XPATH = '//*[@id="apex-breadcrumbs"]/a[3]';
var PAGE_XPATH = '//*[@id="apex-breadcrumbs"]/span[4]';

var
  application,
  local,
  page,
  tabQuery,
  xhr;

function getNumber(pXpath, pItem) {

  var
    nextResult,
    lReturn,
    xpathResult;

  xpathResult = document.evaluate(pXpath, document.body, null, XPathResult.ANY_TYPE, null);
  nextResult = xpathResult.iterateNext();

  while (nextResult) {
    if (nextResult.textContent.indexOf(pItem) !== -1) {
      lReturn = parseInt(nextResult.textContent.substr(pItem.length), 10);
    }
    nextResult = xpathResult.iterateNext();
  }
  return lReturn;
}

tabQuery = location.search.split('=')[1].split(':');
local = 'http://' + location.host + '/' + location.pathname.split('/')[1] + '/';

if (tabQuery[0] === '4000') {    /* Builder   */
  if (tabQuery[1] === '4150') {  /* Edit page */
    /* 
      Get the pagenumber from the body
    */
    page = getNumber(PAGE_XPATH, 'Page');

    if (page) {
      /* 
        The page is found. Now get the applicationnumber
      */
      application = getNumber(APPL_XPATH, 'Application');
    }
    if (application && page) {
      /* 
        Both items have been found. Call the stored procedure to do some magic.
      */
      xhr = new XMLHttpRequest();
      xhr.open("GET", local + 'AUTH_API.ADD_PAGE?p_application_id=' + application + '&p_page_id=' + page, true);
      xhr.send();
      alert('application: ' + application + ' page: ' + page + ' is added');
    }
  }
}

When the query part of the URL is like 4000:4150 we know that we are on the page edit page.Then we use some XPATH on the body of the page to get the application id and page id. The XPATH expressions are stored in two constant vars. The XPATH expressions you see are working for the current APEX 4.2 version; to get these expressions is very easy using Chrome:

  • In APEX Builder, go to the page edit page.
  • In the breadcrumbs go to the Page xx part.
  • Inspect the element using the context menu in chrome.
  • In the elements tab use the context menu Copy Xpath

The last step to take is to send the two id’s to the database.
For this, I just use a HTTP-request to a stored packaged procedure AUTH_API.ADD_PAGE with two parameters p_application_id and p_page_id.

In our architecture (OHS with mod_plsl and a standard dads.conf) for the development database we are allowed to fire stored procedures this way. In our development
database the wwv_flow_epg_include_mod_local is non-restrictive:

create or replace function wwv_flow_epg_include_mod_local
  ( procedure_name in varchar2
  ) return boolean
is
begin
  return true; 
end wwv_flow_epg_include_mod_local;

The connecting user, defined in the dads.conf, -e.g. APEX_PUBLIC_USER-, has to have proper grants on the stored procedure that is being called by the HTTP-GET.

Importing the extension.
- Go to the Extra > extentions part of the menu
- Enable developmentmodus
- Use the load button to load the directory where you put all your files.

The new browserAction should show up in the browser.

That’s it. With this you can build an extension in chrome to parse a page in APEX Builder and send the information to some API in the database.

Using Knockout.js with Oracle Apex

If you’re one of the cool kids in the JavaScript block, you might have heard of Knockout.js. With Knockout you can e.g. use declarative binding to DOM elements and use it to dynamically refresh your UI. Just go through the Tutorials on the site. They are excellent!

Sounds like Dynamic Actions in Oracle Apex, right? But if you are starting to hate the dense forest and – in my humble opinion – bugginess of Dynamic Actions and are looking around for alternatives, I would suggest a look into Knockout.

I will give a very, very simple example to dynamically show a message after inserting a value in a text item. Some of the basic Apex concepts I will not discuss.

First of all, you will have to download the latest version of knockout.js and link it to your page c.q. application. You could also link to a Content Delivery Network (CDN) like cdnjs.com.

The Item where you have to enter the text in has to be bound to the viewmodel. You can use the attributes part of the HTML element.
element

In my example I have a P60_ELI text field which I bind to the observable ELI.

The text that automagically will appear will be shown in another Text Field P60_ELI_VERBRUIK, bound to the knockout id ELI_VERBRUIK.
element2

To put it all together you have to apply the bindings with some small JavaScript in the page:

function getVerbruik(p_x, p_y) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=SomeOnDemandProcedure',0); 
  //get.add('X',p_x); you could pass the input to the process
  //get.add('Y',p_y);
  gReturn = get.get();    
  return gReturn; 
}  

function viewModel() {
  var self = this;  
  self.ELI = ko.observable();
  self.ELI_VERBRUIK = ko.computed( function() { 
                                     return getVerbruik('ELI', self.ELI() ); /* passing through the value that is entered in the observable ELI field, i.e. the P60_ELI item */
                                   }
                                 );
}
/*
  Bind the right viewModel (you could have created several) to the elements on the page. 
*/ 
$(function(){
  ko.applyBindings(new viewModel());
});
 

The function getVerbruik will give some text back from the database using an ON-DEMAND process. You can of course just give back text, but I would suggest to return some JSON. Just because you can.