Preventing the usage of partitioning in Oracle

In the situation that you have installed the Partitioning option and you don’t want your developers to use this feature, you could use the installer or “chopt” to remove the option.

However, with these utilities you need to shut down the database and that is not always desirable.

You could also use an INSTEAD OF CREATE on DATABASE to prevent your developers to create partitioned tables:

create trigger wi_prevent_partitioning
instead of create on database
when (ora_dict_obj_type = 'TABLE')
declare
  l_sql      varchar2(32767);
  l_sql_list ora_name_list_t;
begin
  for r_i in 1 .. ora_sql_txt(l_sql_list)
  loop
    l_sql := l_sql||l_sql_list(r_i);
  end loop;
  --   
  if ( instr(lower(l_sql),'partition') > 0 )
  then
    raise_application_error
      ( -20999
      , 'Partitioning is not allowed'
      );
  else
    l_sql := replace(l_sql,chr(10),' '); 
    -- 
    l_sql := replace( l_sql
                    , 'CREATE TABLE '
                    , 'CREATE TABLE '
                    ||ora_login_user||'.'
                    );
    execute immediate l_sql;
  end if;
end;

A word of caution: I have tested this with success in a 11GR2 database with APEX 4.2.
In 10.2.0.4 with APEX 4.1 I got an ORA-600 error when I went to the application section of the builder.
So of course test, test, test.

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.