DEV Community: isabolic99

Development pattern (generator) vs dynamical programming

isabolic99 — Sat, 17 Nov 2018 18:45:30 +0000

What do you prefer and why?

I lean to development pattern. First I establish development structure (objects and directories) and then create a code generator, which will generate code for the established structure.

The benefit, once you get to understand the pattern you can easily detect problems (bugs) also others devs will take less time to debug.

The disadvantage is your code is duplicated by the generator.

The other way is dynamical programming. Your code is very dynamical, challenging, every feature is differently implemented.

The benefit, you learn a lot by "experimenting", you build your knowledge.

The disadvantage is your code legacy...

How to set host-only adapter on VM (Virtual box)

isabolic99 — Fri, 03 Aug 2018 08:21:09 +0000

You can find many tutorials about how to setup host-only adapter, but most of them are incomplete, something is missing. So I decided to provide a step-by-step guide from my personal notes.

1) Configuration of network adapter

In newer versions of VirtualBox select from File menu > Host network manager.

Create new adapter, and set the following options:
Configure the adapter manually.

ipv4 address 192.168.99.99
ipv4 network mask 255.255.255.0
DHCP server checked
Server address 192.168.99.100
Server mask 255.255.255.0
Lower address bound 192.168.99.101
Upper address bound 192.168.99.254

You can set IP address whatever you like, but make sure that other options are also set to appropriate address (DHCP server address, Lower/upper address bound, conf. of the network interface on VM)

2) Set up the adapter in network settings of VM

Then, in the network settings for the virtual machine (machine tools icon - hammer), set up two adapters:

Adapter 1
host only, vboxnet0

Adapter2
NAT

also don't forget to click on refresh mac address of both adapters

3) Configure the VM

Boot the virtual machine and log in through the console VirtualBox provides.

open the terminal and run the following command:

ls /sys/class/net

This will provide you the name of adapters on the VM. In my case, the adapters were named eth1 and eth2 (and lo, the loopback interface).

Then, edit the network configuration.

sudo vi /etc/network/interfaces

Now apply the following configuration:

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface host-only adapter
auto eth0
allow-hotplug eth0
iface eth0 inet static
address 192.168.99.100
netmask 255.255.255.0

# The NAT
auto eth1
iface eth1 inet dhcp

After that, I recommend restart of VM.

And that is it. Now I am too lazy to explain every option in network interface configuration (hotplug etc..). You can google itself. :P

Also, you can set the hostname in your host's file (win => C:\Windows\System32\drivers\etc\hosts, linux => /etc/hosts) so you don't have to every time access to VM by ip address.

My hole developer environment (and playground) is on VM, there I have installed tomcat, apache, db, node.js etc. because I don't want to pollute my host machine and also by my opinion this is the best solution for simulation of production/test environment.

PL/SQL find me right table and column based on a value :)

isabolic99 — Fri, 23 Mar 2018 09:38:42 +0000

Motive

How many times did I write someone an email, and ask them where is a specific value stored in a database (table, column) and the answer came in best scenario tomorrow. So I came with the idea to write my PL/SQL block to find me where is that particular value stored.

Solution

I wrote simple PL/SQL anonymous block to find me the necessary table and column for my task.

SET SERVEROUTPUT ON;
DECLARE
  match_count INTEGER;

--the owner/schema of the tables you are looking at
  v_owner VARCHAR2( 255) :='CUSTOMER' ;

-- data type you look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
  v_data_type VARCHAR2( 255) :='VARCHAR2' ;

--The value you are looking for with like "%" operator
  v_search_string VARCHAR2(4000) := '%TGP%' ;

BEGIN
  FOR t IN (SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- esclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name) ) LOOP

    EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;

    IF match_count > 0 THEN
      dbms_output.put_line( t. table_name ||' ' ||t.column_name ||' '||match_count );
    END IF;

  END LOOP;
END;
/

parameters

The code has 3 variables:

v_owner - the schema where query will look for
v_data_type - data type
v_search_string - The value you are looking for with like "%" operator

query

Code executes FOR LOOP on a query to find table_name, column_name and table owner(schema).
The query uses upper parameters v_owner and v_data_type, also exclude virtual columns and views.

SELECT atc.table_name
                  ,atc.column_name
                  ,atc.owner 
            FROM all_tab_cols atc
           WHERE atc.owner = v_owner
             AND data_type =  v_data_type
             -- exclude vir. columns
             AND atc.column_id is not null
             -- exclude views
             AND not exists (select 1 
                               from all_views 
                               where view_name = atc.table_name)

EXECUTE IMMEDIATE

 EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' 
         || t.owner || '.'  ||t. table_name|| 
        ' WHERE UPPER("'||t.column_name ||'") LIKE  UPPER(:1)'
    INTO match_count
    USING v_search_string ;

Inside LOOP the code runs EXECUTE IMMEDIATE statement with concatenating table owner, table name, and column and parameter v_search_string. So for every column inside every table, it runs to check if there is close enough value to that I look for.

Output

If the is a match (> 1) output table_name, column_name and match count to dbms_output.

    IF match_count > 0 THEN
      dbms_output.put_line( t. table_name ||' ' ||t.column_name ||' '||match_count );
    END IF;

Also since dbms_output has to be turned on and has a limit, I set at the beginning of PL/SQL block

SET SERVEROUTPUT ON;

which means the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line prints the argument on the main console (Script output).

Oracle export DDLS into zip

isabolic99 — Fri, 16 Mar 2018 11:25:35 +0000

The goal

The goal was to create a single PL-SQL function that will generate a zip file with all database objects source(DDL).

Find the necessary objects with the SQL query

SELECT
    lower(object_name)
    || decode(object_type, 
              'VIEW', '.vw', 
              'TRIGGER', '.trg', 
              'PACKAGE', '.pks', 
              'PACKAGE BODY', '.pkb',
              '.sql')  file_name,
    dbms_metadata.get_ddl(object_type,object_name, owner)  file_content
FROM
    all_objects
WHERE owner = 'DEMO';

The query will return two columns file_name with extension and file_content, for generating file_content I have used Oracle build in function "dbms_metadata.get_ddl" a function that returns DDLs (source of objects) CLOB.
Now that query is ready I need to create a function that will return zip with all DDLs in files.

Functions clob_to_blob and get_source

Since a didn't find any build-in function for clob to blob a had to create my own, once again StackOverflow was very useful:) The second function get_source consists of:

running FOR LOOP on a query from above
call clob_to_blob (convert ddl content to blob)
adding blob file output to zip file, this done with apex_zip package from oracle
once FOR LOOP is done finish zip and RETURN zip BLOB

CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB)
   RETURN BLOB
AS
   v_blob      BLOB;
   v_varchar   RAW (32767);
   v_start     BINARY_INTEGER := 1;
   v_buffer    BINARY_INTEGER := 32767;
BEGIN
   dbms_lob.createtemporary (v_blob, false);

   FOR i in 1 .. ceil (dbms_lob.getlength (clob_in) / v_buffer)
   LOOP
      v_varchar := utl_raw.cast_to_raw (dbms_lob.substr (clob_in, v_buffer, v_start));
      dbms_lob.append (v_blob, v_varchar);
      v_start := v_start + v_buffer;
   END LOOP;

   RETURN v_blob;
END clob_to_blob;
/

CREATE OR REPLACE FUNCTION get_source RETURN BLOB AS
    l_zip_file       BLOB;
    v_file           BLOB;
BEGIN

    FOR l_file IN (
      SELECT lower(object_name)
             || decode(object_type, 
                       'VIEW', '.vw', 
                       'TRIGGER', '.trg', 
                       'PACKAGE', '.pks', 
                       'PACKAGE BODY', '.pkb',
                       '.sql')  file_name,
             dbms_metadata.get_ddl(object_type,object_name, owner)  file_content
        FROM all_objects
       WHERE owner = 'DEMO'
    ) LOOP
        --call clob to blob fn
        v_file := clob_to_blob(l_file.file_content);

        --add file into zip
        apex_zip.add_file(
                p_zipped_blob => l_zip_file
               ,p_file_name   => l_file.file_name
               ,p_content     => v_file
        );        

    END LOOP;

    -- finish zip
    apex_zip.finish(p_zipped_blob => l_zip_file);

    RETURN l_zip_file;
END;
/

Execute function

Once everything is compiled on DB I can call the function get_source from SQL or PLSQL.

select get_source from dual

Now I can save DDL source from SQL developer to my PC (git/svn repo). :)

Oracle maps and MarqueeZoomTool on the SHIFT key

isabolic99 — Wed, 21 Feb 2018 07:52:48 +0000

Oracle maps intro

Oracle maps (short OM) is not most commonly used a javascript library for maps, but it is very decent for DISPLAYING georeferenced data. Most developers when it comes to maps turn to javascript libraries such as leaflet and OpenLayers.
Oracle maps are best to use with Oracle map viewer, there is some support for OGC standards, so you can add layers from geoserver but don't expect advanced usage of them.

MarqueeZoomTool

Zoom Tool is a standard tool in OpenLayers.Your draw rectangle with holding left key on mouse and the SHIFT key and map zoom itself to that specific area drawn. In the example below, you can try this with OL.

In OM that is not a standard tool, SHIFT key is not implemented and there are 3 types of behavior.

OM.tool.MarqueeZoomTool.ONE_TIME - one time only, once the tool is active it will deactivate itself when the zoom is done.

OM.tool.MarqueeZoomTool.CONTINUES - the tool is always active then there is no pan/drag control on a map.

OM.tool.MarqueeZoomTool.PROMPT - the tool is always active and you draw a rectangle and click on him for zoom, if you click somewhere else you cancel zoom.

MarqueeZoomTool on SHIFT key

So in OM, I had to do a lite hack to get this working with the SHIFT key. I
instanced MarqueeZoomTool with ONE_TIME options.

After that, I added event handler "mousedown" on the $oracleMapDiv property of map object. $oracleMapDiv property actually jquery selector so "on" method is available for event registration.

The event handler function checks if the SHIFT key is pressed if disable map drag action (map.enableMapAction.drag = false) and activate marqueeZoomTool by calling "start()" and "begin(e)" drawing a rectangle for zoom.


var map = new OM.Map(
                document.getElementById('map'),
                {mapviewerURL: baseURL}
           );


var marqueeZoomTool = new OM.tool.MarqueeZoomTool(
                       map,  OM.tool.MarqueeZoomTool.ONE_TIME
                  );

marqueeZoomTool.allowZeroSize = true;

map.$oracleMapDiv.on("mousedown", function (e) {
    if(e.shiftKey){
      map.enableMapAction.drag = false;
      marqueeZoomTool.start();
      marqueeZoomTool.begin(e);
    }
});

example

Normaly I would provide example in codepen/jsfiddle/jsbin. But I didn't find any public mapviewer or link for OM.js that works, so i provided screenshot. :/

some useful OM links

orclapex 5.x page validations (upgraded concept)

isabolic99 — Wed, 14 Jun 2017 20:23:19 +0000

First thing first, let me be clear this is observation post and some sort of solution approach. Last week I analyzed apex application which was created on apex 4.x and imported to apex 5. The application was imported successfully and I knew that I can't use universal theme features ( for example template options). Now for the interesting part and that is validation process/execution. This has changed in apex 5, but also for imported application it did not stayed in apex 4 "mode".
Here is the explanation:
In apex 4.x validation are rendered as html region after page is submitted but in apex 5 validation are actually applied by javascript. Before submitting page there is AJAX call that return JSON array with errors. Look at the example down below..

Example how it looks in apex 5:

Example how it looks in apex 4:

Now in our application which was build on apex 4 we hided validation region and displayed validation using jquery jgrowl plugin after page submit.

when we migrate to apex 5, message where not displayed, and reason is AJAX call..

<script type="text/javascript"> 
$(document).ready(function() { 
  $.jGrowl($('#static-id-of-val-region').html()); 
}); 
</script>

And the solution was that I changed code on page initialization (code snippet above) to this:

<script type="text/javascript"> 
apex.message.setThemeHooks({
    beforeShow: function(){ 
        $.jGrowl($('#static-id-of-val-region').html()); 
    }
}); 
</script>

I applied callback on apex.messages before validation shows.
More about this can be actually found in apex JS DOCS

Also I found that on page login they used "old" approach, they render validation region, so setThemeHooks won't work here...

I did some thinking and came up with this scenario regarding apex js API:
You have apex 5 on development env. (because of faster development) and some old apex 4 production env. that for some reason it won't get upgrade in near feature. You use some specific apex JS API that is only available in apex 5 and you deployed that code to apex 4 production env. and then the fun starts :D

Big thanks to our poor organization, that we only support our clients directly on production... ;)

Oracle apex chat demo

isabolic99 — Wed, 12 Apr 2017 19:32:52 +0000

A week before or two I published on apex.oracle.com simple apex chat demo. It is demo about how to integrate oracle apex and socket.io (node.js).

Basics, part I: Oracle apex

Oracle apex is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure.

Basics, part II: Socket.io

Socket.io is a JavaScript library (uses websockets, more info on wiki) for realtime web applications. It enables realtime, bi-directional communication between web clients and servers. It has two parts: a client-side library that runs in the browser, and a server-side library for Node.js. Both components have a nearly identical API. Like Node.js, it is event-driven.

Proof of concept

Concept was to developed apex region plugin that uses socket.io javascript library for realtime interaction with node.js. Socket.io is composed of two parts, one is included on client side and the other is installed on server side. They communicate through websocket which uses 101 switching protocols to send and receive data. So when message is send to server side, server side propagate the same message to all active socket.io sessions. Also you can see from the concept, that messages are not stored in database.

Client side

On client side apex chat is designed as apex region plugin. Witch uses these js libraries:

handlebars.js -> compiles html forms with data
socket.io client side -> client side lib. for realtime communication between web clients and servers.
jquery witch is included in apex

Beside controling the html form, it also listen for propagated events from websocket and triggers the same in order to send data.

Server side, node.js

Server side is developed in node.js with multiple packages. Underneath there is a list of used packages and short description with links. Comunication with client is through websocket.

Express.js & http - used to create http server
socket.io - server side library for websockets
winston - logger
mkdirp - 'mkdir -p', creates log folder
ip - IP address utilities

...
    const express = require('express');
    const http = require('http');
    const socketio = require('socket.io');
    const winston = require('winston');
    const mkdirp = require('mkdirp');
    const ip = require("ip");
...

Socket events

Socket.io is event-driven library, that means communication goes by triggering events and there registration. On server side triggering is done in emit function. If on socket object is not set room property chat then broadcast event to all open socket sessions.

...
var emit = function(emitCmd, data){
...
    if (socket.room !== undefined){
        socket.in(socket.room).broadcast.emit(emitCmd, data);
    } else {
        socket.broadcast.emit(emitCmd, data);
    }
 }
...
emit("new.message", {
    username: socket.username,
    message : data
});
...

and this is how event is registered on server side:

...
socket.on("add.user", function (data) {
...
    socket.username = data.username;
    emit("user.joined", {
        username: socket.username
     });
});
...

So when on client side is triggered event "add.user" (in demo when user set there own username), on server side socket.io catch that event and triggers to all others socket.io session "user.joined" event.

on client side this is much simpler, triggering example:

...
this.socket.emit("new.message", msg);
...

and there registration:

...
this.socket.on("user.joined", function(data) {
      ...
}.bind(this));
...

List of events that are triggered on client side, note that this events are registered on server side:

public - send flag that this chat is public is available to everyone
set.room - send flag that this chat is private
typing - send flag that user has started to type in message area
stop.typing - send stop typing flag
new.message - send new message to server side for propagation to other sessions

List of events that are registered on client side, there are triggered on server side:

user.joined - received data from server that user has joined in your chatroom, create notification in chat region
typing - received data from server that user has started typing, create typing info in chat region
stop.typing - received data from server that user has stopped typing, remove typing info in chat region
new.message - received new message from server, display message in chat region
user.left - received data from server that user has left your chatroom (socket.io session is terminated), create notification in chat region

Monitoring network between client and server

Now for this, I used google developer tools for screenshot. In your developer tools under network tab, and WS you can see all traffic (under Frame tab you can see text message that are sended/received) that is currently comming and going.

Deploying on apex.oracle.com / OpenShift Online

Since websocket can do cross-domain (but you will need to have a handshake in order to get this going), I installed plugin on apex.oracle.com and node.js module (server side socket.io) is installed on OpenShift Online. OpenShift Online is Red Hat's public cloud application development and hosting platform that automates the provisioning, management and scaling of applications so that you can focus on writing the code for your business, startup, or big idea. OpenShift Online supports a variety of technologies through use of cartridges, including Java, PHP, Node.js, Python, Perl, MySQL, PostgreSQL, MongoDB, Jenkins, Cron, and more.

The future of apex chat demo

Right now all code/project is available at github.
Note that it is still work in progress. The passed few weeks I received a huge, huge feedback which I am very thankful for.

I also encourage you to submit ideas to github issue page and tag them with label New feature.

Oracle Apex 5.0.* right side column width

isabolic99 — Sat, 18 Mar 2017 07:36:58 +0000

Apex 5.* comes with universal theme that includes page template called
Right Side Column.
This template provides a collapsible right-side display position that is useful for displaying action-oriented controls such as buttons or lists.
Now this region position is limiting my region to 200px width. That was too thin for me.
So I tried to expand this with css and I found that toggling "open" and "close" are not based on any javascript function. Instead a css property transform is used: translate3d(200px, 0, 0);.

Specification for this css rule can be found here: https://goo.gl/5qnxqJ

This css rule is applied on button click and css selector is doing the rest.
That means when user clicks on button to expand the region, class .js-rightExpanded is set on body. When user clicks on close then that class is replaced with .js-rightCollapsed.

The solution for my problem was to set css on page:

body.t-PageBody.js-rightCollapsed .t-Body-actions {
    -webkit-transform: translate3d(300px, 0, 0);
    -ms-transform: translate(300px);
    transform: translate3d(300px, 0, 0);
}

.t-Body .t-Body-actions {
    width: 300px;
}

Apex example can be found here: https://goo.gl/7sboEj

NOTE
If you use APEX 5.1 and up you can use option called "Actions Column" in the new version of apex (>5.1) inside Theme Roller.

https://twitter.com/isabolic99/status/974537323084828672