Changing the status of an Oracle Apex application with pl/sql

I needed this. I needed this badly. And for once the Oracle Apex forum gave me the answer for my need..

create or replace procedure set_application_status
  ( p_application in apex_applications.application_id%type
  , p_status      in varchar2
  )
is
begin
  if p_status not in ( 'AVAILABLE'        , 'AVAILABLE_W_EDIT_LINK', 'DEVELOPER_ONLY'
                     , 'RESTRICTED_ACCESS', 'UNAVAILABLE'          , 'UNAVAILABLE_PLSQL'
	             , 'UNAVAILABLE_URL')
  then
    raise_application_error(-20000, 'Status '||p_status||' is not supported');
  end if;
  --
  for r_i in ( select app.workspace_id
               ,      app.application_id
               from   apex_applications app
               where  app.application_id = p_application
	     )
  loop
    wwv_flow_api.set_security_group_id
      ( p_security_group_id => r_i.workspace_id
      );
    wwv_flow_api.set_flow_status
      ( p_flow_id     => r_i.application_id
      , p_flow_status => p_status
      );
    commit;
  end loop;
end;

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.

 

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.

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

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.
  
• 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.
Under “Operations” choose Import Trusted certificate and import both *.cer files we created before.

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.
But 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!

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!

Building a printserver in PL/SQL for APEX with PLPDF

When our customer purchased a licence for PLPDF I started to think about building a simple printserver for APEX in pure PL/SQL.

That should be simple, right? Well, let’s see.

The first step I took was to investigate the full HTTP request APEX makes to its printerengine.

For this I used node.js. Yes, I could have used any sniffer available and APEX also gives you the XML, but building it myself in node.js was more fun. The catcher writes the request to a textfile named body.txt.

File: index.js

var server = require("./server");
var router = require("./router");

server.start(router.route);

File: server.js
var http = require("http"),
  url = require("url");

var constants = {"Port": "80"};

function start(route) {
  http.createServer(function (request, response) {
    route(url.parse(request.url).pathname, request, response);
  }).listen(constants.Port, function () {
    console.log("Listening on " + constants.Port);
  });
}

exports.start = start;

File: router.js
var fs = require('fs');

function route(pathname, request, response) {
  console.log('In de router: ');
  console.log('Path: ' + pathname);
  console.log('Method:' + request.method);
  //console.log(request);
  var body = '';

  request.on('data', function (chunck) {
    body += chunck;
  });

  request.on('end', function () {
    fs.writeFile('body.txt', body, function (err) {
      if (err) {
        return console.log(err);
      }
      response.writeHead(200, {"Content-Type": "text/plain"});
      response.write(request.toString());
      response.end();
    });
  });
}

exports.route = route;

I started the catcher via

  node index.js

and ajusted the Apex Instance settings as admin to the following:
Print Server : Oracle BI Publisher  –> Apache FOP will also work
Print Server protocol : HTTP
Print Server Host Adress: The hostname of my machine
Print Server Port : 80 The Port where the catcher is listening
Print Server Script : /<dad>/converttopdf

After that I created an application with a Report Query and a simple page with a button to print. How to do that is out of the scope of this blog.

After pressing the print-button the catcher wrote the follwing information:

Path: /<dad>/converttopdf
Method: POST
xml=<?xml version="1.0" encoding="UTF-8"?>
<DOCUMENT>
   <DATE>01-FEB-13</DATE>
   <USER_NAME*****USER_NAME>
   <APP_ID*****APP_ID>
   <APP_NAME>*****</APP_NAME>
   <PAGE_ID>1</PAGE_ID>
   <TITLE>*****</TITLE>
   <REGION ID="some number">
      <ROWSET>
         <ROW>
            <NAME>*****</NAME>
            <EAN_GLN>*****</EAN_GLN>
         </ROW>
         ...
      </ROWSET>
   </REGION>
</DOCUMENT>
&template=<?xml version ....
..
</xsl:stylesheet>
&_xtype=xsl-fo&_xf=pdf

So, APEX used a POST method to /<dad>/converttopdf using a body with the following four parameters
xml, template, _xtype, _xf

So, I should be able to create a procedure converttopdf using these named parameters…. not. _xtype and _xf are not allowed as parameternames in PL/SQL. Of course you could do something like

create or replace procedure converttopdf
  ( "xml" in varchar2
  , "template" varchar2
  , "_xtype" in varchar2
  , "_xf" in varchar2
  );

but I tried it and it didn’t work eventually.

So I tried some old school flexible naming:

create or replace procedure converttopdf
  (   name_array  IN  owa.vc_arr
  ,   value_array IN  owa.vc_arr
  )

and altered the Print Server Script setting to:
/<dad>/!converttopdf –> notice the exclamation

Now make sure that APEX_PUBLIC_USER can “see” the procedure (grants, synonyms) and that APEX allows you to call the new procedure in the URL by altering the

apex_xxxx.wwv_flow_epg_include_mod_local function

Now, the only thing we shoud do is altering Print Server Host Adress to the hostname of the webserver.

And it works! When hitting the print button you will get the XML that’s generated by APEX in your procedere. With PLPDF its relatively easy to generate the pdf
in a blob and send it back with the respons:

create or replace procedure converttopdf
(   name_array  IN  owa.vc_arr
,   value_array IN  owa.vc_arr
)
is
  /* Proof of concept */
  l_blob          blob;
  l_font          plpdf_type.t_row_font;
  l_heading_font  plpdf_type.t_row_font;
  l_row_aligns    plpdf_type.t_row_aligns;
  l_row_borders   plpdf_type.t_row_borders;
  l_row_datas     plpdf_type.t_row_datas;
  l_row_fonts     plpdf_type.t_row_fonts;
  l_row_maxlines  plpdf_type.t_row_maxlines;
  l_row_widths    plpdf_type.t_row_widths;
  l_header_widths plpdf_type.t_row_widths;
  l_titel         varchar2(2000);
  l_xml           xmltype;
  l_xml_chr       varchar2(32767);
  --
  procedure reset_row
  is
  begin
    l_row_datas.delete;
    l_row_widths.delete;
    l_row_borders.delete;
    l_row_aligns.delete;     
    l_row_fonts.delete;    
    l_row_maxlines.delete;     
  end; 
  --
  procedure add_column
    ( p_naam in varchar2
    , p_id   in number
    )
  is
  begin
    if p_naam is not null
    then
      l_row_datas(p_id)    := p_naam;
      if NOT l_header_widths.exists(p_id)
      then
        l_row_widths(p_id)   := length(p_naam)*5;
        l_header_widths(p_id) := l_row_widths(p_id);
      else
        l_row_widths(p_id)   := l_header_widths(p_id);
      end if;
      l_row_borders(p_id)  := 1;
      l_row_aligns(p_id)   := 'L';     
      l_row_fonts(p_id)    := l_font;    
      l_row_maxlines(p_id) := 10;     
    end if;  
  end ;
begin
  l_xml_chr := value_array(1);
  l_xml := xmltype(l_xml_chr);
  --
  l_heading_font.family := 'Arial';
  l_heading_font.style  := '';
  l_heading_font.fsize  := 10;
  -- 
  plpdf.init;
  plpdf.NewPage;
  plpdf.SetLeftMargin(5);
  plpdf.SetPrintFont
    ( p_family => l_heading_font.family
    , p_style  => 'B'
    , p_size   => l_heading_font.fsize
    );
  select extractvalue(l_xml,'/DOCUMENT/TITLE') into l_titel from dual;  
  l_titel := coalesce(l_titel,'No title supplied');
  plpdf.PrintCell
    ( p_txt => 'Rapport '||l_titel||' of '
    ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') 
    );
  plpdf.LineBreak();
  plpdf.LineBreak();  
  FOR r_rows IN
    ( SELECT rownum as id
      ,      extract(column_value,'/ROW/*[1]').getRootElement()  as column_name_1
      ,      extractvalue(column_value,'/ROW/*[1]')              as column_value_1
      ,      extract(column_value,'/ROW/*[2]').getRootElement()  as column_name_2
      ,      extractvalue(column_value,'/ROW/*[2]')              as column_value_2
      ,      extract(column_value,'/ROW/*[3]').getRootElement()  as column_name_3
      ,      extractvalue(column_value,'/ROW/*[3]')              as column_value_3
      ,      extract(column_value,'/ROW/*[4]').getRootElement()  as column_name_4
      ,      extractvalue(column_value,'/ROW/*[4]')              as column_value_4
      ,      extract(column_value,'/ROW/*[5]').getRootElement()  as column_name_5
      ,      extractvalue(column_value,'/ROW/*[5]')              as column_value_5
      ,      extract(column_value,'/ROW/*[6]').getRootElement()  as column_name_6
      ,      extractvalue(column_value,'/ROW/*[6]')              as column_value_6
      ,      extract(column_value,'/ROW/*[7]').getRootElement()  as column_name_7
      ,      extractvalue(column_value,'/ROW/*[7]')              as column_value_7
      ,      extract(column_value,'/ROW/*[8]').getRootElement()  as column_name_8
      ,      extractvalue(column_value,'/ROW/*[8]')              as column_value_8
      ,      extract(column_value,'/ROW/*[9]').getRootElement()  as column_name_9
      ,      extractvalue(column_value,'/ROW/*[9]')              as column_value_9
      ,      extract(column_value,'/ROW/*[10]').getRootElement()  as column_name_10
      ,      extractvalue(column_value,'/ROW/*[10]')              as column_value_10
      FROM   TABLE(xmlsequence(l_xml.extract('//DOCUMENT/REGION/ROWSET/ROW'))) t
    )
  LOOP
    if r_rows.id = 1
    then
      reset_row;
       plpdf.SetPrintFont
        ( p_family => l_heading_font.family
        , p_style  => 'B'
        , p_size   => l_heading_font.fsize
        );
      add_column(initcap(r_rows.column_name_1),1); add_column(initcap(r_rows.column_name_6),6);
      add_column(initcap(r_rows.column_name_2),2); add_column(initcap(r_rows.column_name_7),7);
      add_column(initcap(r_rows.column_name_3),3); add_column(initcap(r_rows.column_name_8),8);
      add_column(initcap(r_rows.column_name_4),4); add_column(initcap(r_rows.column_name_9),9);
      add_column(initcap(r_rows.column_name_5),5); add_column(initcap(r_rows.column_name_10),10);
      --
      plpdf.Row_Print4
        ( p_data    => l_row_datas
        , p_border  => l_row_borders
        , p_width   => l_row_widths
        , p_align   => l_row_aligns
        , p_font    => l_row_fonts
        , p_maxline => l_row_maxlines
        ); 
      --
      plpdf.SetPrintFont
        ( p_family => l_heading_font.family
        , p_style  => null
        , p_size   => l_heading_font.fsize
        );         
    end if;
    --
    reset_row;
    add_column(r_rows.column_value_1,1); add_column(r_rows.column_value_6,6);
    add_column(r_rows.column_value_2,2); add_column(r_rows.column_value_7,7);
    add_column(r_rows.column_value_3,3); add_column(r_rows.column_value_8,8);
    add_column(r_rows.column_value_4,4); add_column(r_rows.column_value_9,9);
    add_column(r_rows.column_value_5,5); add_column(r_rows.column_value_10,10);                        
    --
    plpdf.Row_Print4
    ( p_data    => l_row_datas
    , p_border  => l_row_borders
    , p_width   => l_row_widths
    , p_align   => l_row_aligns
    , p_font    => l_row_fonts
    , p_maxline => l_row_maxlines
    );
  END LOOP;
  --  
  plpdf.SendDoc(l_blob);
  -- 
  htp.init;
  owa_util.mime_header('application/pdf', false);
  htp.p('Content-Disposition: attachment; filename="'||l_titel||'.pdf"');
  htp.p('Content-Length: ' || dbms_lob.getlength(l_blob));
  owa_util.http_header_close;
  wpg_docload.download_file(l_blob);
exception
  when others
  then
    htp.p(sqlerrm);
end;

What are the drawbacks?

Just look at the definition of the parameters of converttopdf. The array can just hold up to 32K values.
If you try to print something that’s more than 32K in size it won’t work. But for some simple reports for which the input data
doesn’t become larger that 32K, this concept does work.

Rollback after commit using flashback

Normally when someone asks if it is possible to rollback a single, complete transaction after a commit I have to say no. But this time I was confronted myself with this requirement. We are migrating an Oracle Forms client-server application to Oracle Apex and in one of the forms the transaction-handling was as follows:

1. Execute some complex pl/sql that updates/inserts into several tables
2. After completion the user can switch between several screens within the same session to look at several parameters.
3. If necessary the user can rollback the complete transaction.

Well, in C/S the third step is just a simple ROLLBACK command, but in Apex life is not that simple.

In Apex a commit is issued after every pl/sql block – in our case a Dynamic Action- so step 3 is not that trivial any more. As we saw it we had 2 options:

• Write some complex pl/sql that reverses the complex logic of the first step
• Something else.

I went for “Something else”. The database version is an Oracle 10g EE, so I didn’t have all the new flashback features at hand, but it was enough to make something that works.

I created the following package like this:

create or replace package asl_transaction
as
  procedure capture_transaction
    ( p_session_id number
    );
  procedure reverse_transaction
    ( p_session_id number
    );
  procedure confirm_transaction
    ( p_session_id number
    );    
  procedure purge_old_transactions ;
end;
/
create or replace package body asl_transaction
as

type t_cache 
is
  table of rowid
  index by varchar2(2000)
;
g_cache t_cache;

procedure capture_transaction
  ( p_session_id number
  )
is
begin
  insert into apx_transaction
    ( session_id
    , xid
    , creation_date
    )
  select p_session_id
  ,      xid
  ,      sysdate
  from  v$transaction
  where addr = ( select taddr
                 from v$session
                 where sid = ( select sid
                               from v$mystat
                               where rownum = 1  ));
end;

procedure push_rowid
  ( p_rowid rowid
  )
is
begin
  g_cache(p_rowid) := p_rowid;
end;

function check_rowid
  ( p_rowid in rowid
  ) return rowid
is
begin
  if g_cache.exists(p_rowid)
  then
    return g_cache(p_rowid);
  else
    return p_rowid;
  end if;
end;

procedure reverse_transaction
  ( p_session_id number
  )
is
  l_new_rowid rowid;
begin
  savepoint inlineTransaction;
  for r_transactie in ( select xid 
                        from   apx_transaction 
                        where  session_id = p_session_id
                      )
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid         = r_transactie.xid
                   and    qry.table_name != 'ASL_APX_TRANSACTION'
                 )
    loop
      if upper(r_dml.undo_sql) like '%INSERT INTO%'
      then
        push_rowid(r_dml.row_id);
        l_new_rowid := null;
        execute immediate r_dml.undo_sql
        ||' returning rowid into :b1' returning into l_new_rowid;        
        g_cache(r_dml.row_id) := l_new_rowid;
      else
        l_new_rowid := check_rowid(r_dml.row_id);
        if ( l_new_rowid != r_dml.row_id)
        then
          r_dml.undo_sql := replace(r_dml.undo_sql,r_dml.row_id, l_new_rowid);
        end if;
        execute immediate r_dml.undo_sql;        
      end if;
    end loop;
  end loop;
  confirm_transaction(p_session_id);
end;

procedure confirm_transaction
  ( p_session_id number
  )
is
begin
  delete from apx_transaction where session_id = p_session_id;  
end;

procedure purge_old_transactions
is
begin
  delete from apx_transaction where creation_date < sysdate - 1;  
end;

end;
/

I did not include the DDL for the table APX_TRANSATION. I think you will be able to reverse engineer the statement.
Before you commit your transaction, so in Apex before the end of your PL/SQL block, you call

ASL_TRANSACTION.CAPTURE_TRANSACTION(:SESSION_ID).

The code that is executed by the former Rollback-button now has to be

ASL_TRANSACTION.REVERSE_TRANSACTION(:SESSION_ID).

In my opinion there also has to be some Confirm button that has to call

ASL_TRANSACTION.CONFIRM_TRANSACTION(:SESSION_ID).

That’s all. I included a full  test script at the end of this post.

For this package to work you’ll have to have give grant select any transaction to the package owner. If you don’t you can’t see the view sys.flashback_transaction_query.

The logic behind the package is this:

• Before the commit, capture all the XID’s from v$transaction of all the DML that are executed in the current transaction
• Save the XID’s with some identifying number, e.g. the session_id
• By reversal, get the undo_sql from flashback_transaction_query and play back the transaction.
• If original transaction deleted the row he first updated c.q. inserted, the reversal has to catch the new rowid of the insert to play back the latter playback c.q. delete.

Some words of caution:

Don’t, really don’t try to access flashback_transaction_query with anything else than a XID.

One of the features you get with an ordinary rollback is locking. The above solution does not involve a locking mechanism, yet. Ghost updates are therefore a possibility: when transaction A updates a table to X, session B updates to Y and A reverses, the update to Y is lost. In the client-server situation session B was blocked for update until A commits or rollbacks. It’s simple Oracle 1:1

However, when we look back at our options we also would have had the same problem with option 1.

Test, test and test if the given solution is appropriate in your case.

drop table emp_2
/

drop table dept_2
/

delete from apx_transaction
where session_id = -1
/

CREATE TABLE emp_2 
   (empno   NUMBER PRIMARY KEY, 
    empname VARCHAR2(16), 
    salary  NUMBER
    )
/
CREATE TABLE dept_2 
   (deptno   NUMBER, 
    deptname VARCHAR2(32)
   )
/

Prompt Insert in EMP_2 111,mike,555
INSERT INTO emp_2 VALUES (111, 'Mike', 555);
COMMIT;

prompt insert in dept_2 10,accounting
INSERT INTO dept_2 VALUES (10, 'Accounting');
COMMIT;

Prompt
Prompt Start an erroneous transaction
Prompt

UPDATE emp_2 SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept_2 VALUES (20, 'Finance'); 
DELETE FROM emp_2 WHERE empno = 111;

Prompt
Prompt Capture the transactiondata and commit
Prompt
execute asl_transaction.capture_transaction(-1);
COMMIT;

Prompt New data in the tables
Prompt EMP_2
select * from emp_2
/
Prompt DEPT_2
select * from dept_2
/
prompt
Prompt The undo-statements
begin
  for r_transactie in ( select xid from apx_transaction)
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid  = r_transactie.xid
                   and    qry.table_name != 'ASL_GTT_TRANSACTION'
                 )
    loop               
      dbms_output.put_line( r_transactie.xid||'...'||r_dml.undo_sql);
    end loop;
  end loop;
end;  
/
Prompt
Prompt Reverse the transaction
exec asl_transaction.reverse_transaction(-1)
Prompt
Prompt Check 
Prompt Has to be: 111,mike,555
select * from emp_2
/

prompt Has to be:  10,accounting
select * from dept_2
/

Ajax call returned server error with QMS

We are building a new Apex frontend on an existing former Oracle Forms application.  After calling some packaged procedures from en Dynamic Action we were confronted with an “Ajax call returned server error”. It seems that an exception raised by the database but invoked by a DA bypasses the central exception handler function you can set in the application properties.

The problem is, that with QMS, a lot of checks are implemented using QMS$ERRORS, so the top-error returning to the “client” is an ORA-20998.  This is not however the real message. You have to walk the error-stack to find the real error.

I have created a packaged procedure DA_ERROR  (in the package APX_VAL, but that is not that important):

PROCEDURE DA_ERROR
IS
  l_err_rec     hil_message.message_rectype;
  l_has_errors  BOOLEAN := TRUE;
  l_error       varchar2(32676);
begin
  l_error := SQLERRM;
  if ( l_error like '%-20998%' )
  THEN
    WHILE l_has_errors
    LOOP
      cg$errors.pop(l_err_rec);
      IF ( l_err_rec.msg_code IS NOT NULL )
      THEN
        l_error := l_err_rec.msg_code || ': ' || l_err_rec.msg_text;
      ELSE
        l_has_errors := FALSE;
      END IF;
    END LOOP;
    cg$errors.cg$err_tab_i := 1;
  end if;
  --
  if ( l_error is not null )
  then
    htp.p('{"error":"'||l_error||'"}');
  end if;
end;

With this procedure in place we can alter our code in :

begin
  call the old procedure
exception
  when others
  then
    apx_val.da_error;
end;

This still doesn’t show the message in the normal errorposition, but the alert that’s raised is more informative that the ORA-20998

Using a refcursor in Apex

My client had a running portal, build in Delphi. Requests for data were implemented as calls to stored packaged functions returning refcursors to a soapserver. For obvious reasons my client wanted to renew the front end of the portal with Apex. The backend however the refcursors- had to be untouched.

I came up with the idea of using some of the XDB features of the database.

This simple example using EMP should give you some idea of what we did.

First, we create a package with a stored function returning a refcursor

create or replace package demo_ref
is

function give_employees
  return sys_refcursor;

end;
/

create or replace package body demo_ref
is

function give_employees
  return sys_refcursor
is
  l_return sys_refcursor;
begin
  open l_return
  for
    select empno, ename
    from   emp
  ;
  return l_return;
end;

end;/

In an apex-page we create an SQL-region using the following-like SQL:

select x.empno
,      x.ename 
from  xmltable ( '/ROWSET/ROW' 
                 passing xmltype(demo_ref.give_employees) 
                 columns empno  number(10) path 'EMPNO'
                 ,       ename  varchar2(100) path 'ENAME'  
               ) x

Of course you could also just test the SQL in your favorite IDE.