Random Data Generator

April 9, 2014 at 2:54 pm | Posted in Oracle Developement | 1 Comment
Tags: , , , ,

Today I came across this awesome website which will automatically generate data for you, and spit it out in different formats. These include XML, CSV, Excel, and more. But the coolest format of course is Oracle SQL. The website will generate the table creation script and the insert statements for your data:

CREATE TABLE myTable (
 id number primary key,
 name varchar2(255) default NULL,
 city varchar2(255),
 age varchar2(50) default NULL,
 zip varchar2(10) default NULL,
 PRIMARY KEY (id)
) AUTO_INCREMENT=1;

INSERT INTO myTable (name,city,age,zip) VALUES ('Ian Parks','New Orleans',15,'8587');
INSERT INTO myTable (name,city,age,zip) VALUES ('Silas Blackburn','Dunbar',40,'97129');
INSERT INTO myTable (name,city,age,zip) VALUES ('Kenyon Hammond','Varsenare',60,'6744');

Note: You may have to work with the quotes.

How cool is that?

I’ve previously blogged about another way of generating random data, so take a look at

Generating Data: Increase a number by a random percentage

Configuring Logging in Oracle Application Express Listener 2.0.1

March 26, 2014 at 3:46 pm | Posted in Oracle Developement | Leave a comment

Christoph Ruepprich:

Here is an excellent post about enabling logging in the Apex Listener/ORDS.

Originally posted on cdivilly:

Simple Logging

The easiest way to turn logging on in Listener 2.0.1 is to use Oracle SQL Developer to turn on the debug.debugger setting:

  • Launch SQL Developer
  • Select View|APEX Listener from the main menu
  • Right click on Listener and choose Connect...
  • Choose an existing connection or create a new connection
  • Enter the credentials for the Listener Administrator user
  • In the tree view naviagate to the Listener>Administration>Global Settings>Environment>Error Reporting node. Tick the Show debug messages on the console option.
  • Click the Upload button from the toolbar (5th from left), and click yes to confirm the upload.
  • The changes will immediately take effect on the Listener instance

Alternatively if your prefer you can edit the defaults.xml Listener configuration file directly. Adding the following to the file to enable logging:
1
You must restart the Listener instance after editing defaults.xml manually.

Displaying logging information on the Error Page

Listener can be…

View original 941 more words

Meet “edb360″: a free tool that provides a 360-degree view of an Oracle database

February 20, 2014 at 9:50 am | Posted in Oracle Developement, Oracle Performance, Oracle Tools, PL/SQL | Leave a comment
Tags: , , , ,

Meet "edb360": a free tool that provides a 360-degree view of an Oracle database.

Excel Style Row Numbering in SQL

February 3, 2014 at 12:59 pm | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Tools | Leave a comment
Tags: , , , , , , , , ,

Here is a trick to change your rownumbering (or any other sequential numbering) to the way Excel names their columns. The first 26 rows will be named A – Z, the next 26 AA – AZ, then BA – BZ, etc..

SELECT decode( ceil( ( ROWNUM / 26 ) - 1)
             , 0, ''
             , chr( ceil( (ROWNUM/26)-1 ) + 64)
             )
     || chr(64 + decode( MOD(ROWNUM, 26)
                       , 0, 26
                       , MOD(ROWNUM, 26)
                       )
            ) col2
 FROM dual
CONNECT BY LEVEL <= 702; --limits the result to 702 rows 

Result:
Row	Label
---------------
1	A
2	B
3	C
4	D
5	E
	…
28	AA
29	AB
30	AC
31	AD
32	AE
	…
698	ZV
699	ZW
700	ZX
701	ZY
702	ZZ

Note that this will be good for 702 rows (nbr_of_letters * (nbr_of_letters+1)). After that you’d have to concatenate a third character, or write a function.

Speaking at RMOUG Training Days 2014

February 3, 2014 at 11:21 am | Posted in Oracle Developement | Leave a comment
Tags: , , , , , , , ,

I’m honored to be allowed to present at this year’s RMOUG Training Days. My presentation Making Sense of Application Express Security, will cover some of the basics of Apex security. I’ll cover various authentication types and how they can be used, how to use authorization schemes to control what applications and application elements users have access to, and also touch on session state protection and URL tampering.

Hope to see you there!

2013 in review

December 31, 2013 at 9:21 am | Posted in Oracle Developement | Leave a comment

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

Madison Square Garden can seat 20,000 people for a concert. This blog was viewed about 63,000 times in 2013. If it were a concert at Madison Square Garden, it would take about 3 sold-out performances for that many people to see it.

Click here to see the complete report.

Enkitec APEX Resource Center

December 18, 2013 at 3:17 pm | Posted in Oracle Developement | 2 Comments
Tags: , , ,

I’m proud to announce the launch of the Enkitec APEX Resource Center (aka ARC) this week. It consists of a number of APEX and PL/SQL specific articles, tips & tricks, how-to’s and demos.

Read some more about it at Scott Spendolini’s blog.

Find ancestor from hierarchy

October 2, 2013 at 11:06 am | Posted in Oracle Developement, PL/SQL | Leave a comment
Tags: , , , , , , , , , , , ,

I needed to find a quick way to select a particular ancestor for a child in a hierarchical table. For example: “Find the manager of SMITH” (parent), or “Find SMITH’s manager’s manager” (grand parent).

Using the emp table as an example, I came up with the following solution:

SELECT sys_connect_by_path(ename, ':') path
      ,ltrim(regexp_substr(sys_connect_by_path(ename, ':'), ':[A-Z]{1,}', 1, &amp;x), ':') ancestor
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename = 'SMITH'
CONNECT BY PRIOR mgr = empno
 ORDER SIBLINGS BY ename;

This query traverses the hierarchical tree up from the child, and limits it to only that row where the employee I’m looking for is the leaf (connect_by_isleaf=1), i.e. the lowest in the hierarchy.  Then I use the sys_connect_by_path to fetch the entire hierarchical path, and utilize the regexp_substr function to pick out the ancestor. Which ancestor is selected depends on &x. If x=1 then the child itself is selected. If x=2, the parent is selected; x=3 is the grand parent and so on.

SQL > /
Enter value for x: 3
PATH                           ANCESTOR
------------------------------ ----------
:SMITH:FORD:JONES:KING         JONES
SQL >

Lastly, here are a couple of examples that show traversing the path forwards and backwards from/to a given descendant:

col ename for a20
col root for a20
col path for a30
PROMPT Forwards path:
PROMPT ==============
SELECT connect_by_root ename root
     , sys_connect_by_path(ename, ':') path
     , ename
  FROM emp
 WHERE ename          = 'SMITH'
START WITH ename       = 'KING'
CONNECT BY prior empno = mgr
/


PROMPT Backwards path:
PROMPT ===============
SELECT connect_by_root ename ename
     , sys_connect_by_path(ename, ':') path
     , ename root
  FROM emp
 WHERE connect_by_isleaf = 1
 START WITH ename       = 'SMITH'
CONNECT BY empno = prior mgr
/

Forwards path:
==============

ROOT                 PATH                           ENAME
-------------------- ------------------------------ --------------------
KING                 :KING:JONES:FORD:SMITH         SMITH

Backwards path:
===============

ENAME                PATH                           ROOT
-------------------- ------------------------------ --------------------
SMITH                :SMITH:FORD:JONES:KING         KING

Apex with Google Maps: Markers (Map Pins)

September 20, 2013 at 1:07 pm | Posted in Oracle Developement | 1 Comment

This is the second in a series of blog posts on using Google maps in Oracle Application Express. Other posts:

Apex With Google Maps: Getting Started

After displaying a map, the next thing you probably want to do is drop some markers, or map pins on it. For this we will go into a bit more detail. Not only will we drop a marker on the map, but I will drop multiple markers, with info bubbles, and marker clusters.

Single Marker

To display a simple marker you create a new marker object, along with some properties, such as location and icon image.


marker = new google.maps.Marker({
    map:map,
    icon: "#APP_IMAGES#beachflag.png",
    animation: google.maps.Animation.DROP,
    position: new google.maps.LatLng(59.327383, 18.06747)
 });

Note that the position parameter requires a Google maps LatLng object. The icon parameter takes a URL in which you can also use the Apex substitution strings for application or workspace images. If you leave the icon parameter null, the default, red Google map marker will be used.

In order to make the marker clickable, and have an info window appear, we need to add an InfoWindow, and an event listener that opens the InfoWindow object:

var iw = new google.maps.InfoWindow();

google.maps.event.addListener(marker,'click',function(){
    iw.open(map,marker);
    iw.setOptions({content: "Hello World!"});
});

Multiple Markers

Most likely you may want to display multiple markers, each with its own info window. In this case you need to store the coordinate data and the info window text in an array, and loop through it to draw the markers.

Lets store our map marker data in an array:

var data = [{name: 'Label 1',lat: '59.32',lng: '18.08'}
    ,{name: 'Label 2',lat: '59.33',lng: '18.06'}
    ,{name: 'Label 3',lat: '59.34',lng: '18.07'}
    ,{name: 'Label 4',lat: '59.35',lng: '18.07'}];

After initializing the map, create a function to handle the markers ,the loop through the array and invoke the function:

    function addMarker(pData){
        marker = new google.maps.Marker({
            map: map,
            position: new google.maps.LatLng(pData.lat,pData.lng)
        });

        google.maps.event.addListener(marker,'click',function(){
            var iw = new google.maps.InfoWindow();
            iw.setOptions({content: "InfoWindow: " + pData.name + "<hr>"});
            iw.open(map,this);
            map.setCenter(this.getPosition());
        });
    }

Note the use of the this keyword in the iw.open function. This is to ensure that each marker gets its own info window. You can also use HTML for the info window content to make it look pretty. On line 11, I’ve added a call to center the map on the coordinates of the marker.

Complete code to render the map, markers and info windows:

var stockholm = new google.maps.LatLng(59.32522, 18.07002);
var marker
var map
var data = [{name: 'Label 1',lat: '59.32',lng: '18.08'}
           ,{name: 'Label 2',lat: '59.33',lng: '18.06'}
           ,{name: 'Label 3',lat: '59.34',lng: '18.07'}
           ,{name: 'Label 4',lat: '59.35',lng: '18.07'}
           ,{name: 'Label 5',lat: '59.35',lng: '18.08'}
           ,{name: 'Label 6',lat: '59.34',lng: '18.08'}
           ,{name: 'Label 7',lat: '59.32',lng: '18.00'}
           ,{name: 'Label 8',lat: '59.32',lng: '18.01'}
           ,{name: 'Label 9',lat: '59.30',lng: '18.00'}
           ,{name: 'Label 10',lat: '59.34',lng: '18.09'}];

function initialize() {
        var mapOptions = {
            zoom: 13,
            mapTypeId: google.maps.MapTypeId.ROADMAP,
            center: stockholm
        };

        map = new google.maps.Map(document.getElementById("map"),
            mapOptions);

        function addMarker(pData) {

            marker = new google.maps.Marker({
                map: map,
                position: new google.maps.LatLng(pData.lat, pData.lng)

            });

            google.maps.event.addListener(marker, 'click', function () {

                var iw = new google.maps.InfoWindow();
                iw.setOptions({
                    content: "InfoWindow: " + pData.name + "<hr>"
                });
                iw.open(map, this);
                map.setCenter(this.getPosition());
            });
        }

        for (var i = 0; i < data.length; i++) {
            addMarker(data[i]);
        }

}

Geocoding with PL/JSON

September 17, 2013 at 3:23 pm | Posted in Oracle Developement | 7 Comments
Tags: , , , , , , ,

The Google Maps API geocoding web service makes it easy to geocode addresses. You can pass an address to the web service and receive either an XML or a JSON response back.

In this post I’ll show how to use the PL/JSON package to parse the JSON response from Google, in order to get to the various address elements and the coordinates associated with a given address.

PL/JSON is a library consisting of a PL/SQL package and some PL/SQL objects and types. It is designed to create and parse JSON. The documentation is a bit lacking, especially when it comes to handling a more complicated JSON object like the Google geocode response. Hopefully, this article will help to clarify its usage.

To get started, first download and install PL/JSON into a schema in your Oracle database. I used version 1.0.3. The installation is simply done by extracting the downloaded zip file, and running the install.sql script. Once installed, make sure all objects are valid. You can run a quick test of the package by running the json_test.sql included in the testsuite directory of the PL/JSON installation.

The Google Geocoding Web Service

Version 3 of the Google Maps API, no longer requires a key. So you can make a simple call to the service like this:

http://maps.googleapis.com/maps/api/geocode/json?address=5605+n+macarthur+blvd+irving+tx&sensor=false

The result of the URL is a JSON object containing the address and geocode data.

To make sense of the JSON object you need to understand that curly brackets {} contain objects, and square brackets [] contain arrays. Strings are enclosed with double quotes, numbers are not. Data is represented in name/value pairs separated by colons.

The Google response consists of two objects: results and status. A successful response will have a status of “OK”.

The results object is an array of four other objects:

  1. address_components – an array of elements such as street number, city, state, etc.
  2. formatted_address – a single string containing the full address
  3. geometry – an array of coordinate data elements
  4. types – an array of strings, which in this case is always “street_address”.

Calling the Web Service

Calling the Google web service from within PL/SQL, I use the ult_http package.  Since the JSON response can be quite large, I use the utl_http.request_pieces function to fetch the result. This works around the 2000 byte limit of the utl_http.request function .  I simply loop through all the pieces returned, and concatenate them to a CLOB variable inside the procedure.

Note that I make the http request inside a WHILE loop (line 50). This is helpful when you attempt to geocode a number of addresses in rapid succession (e.g. by looping through a table with addresses that need to be geocoded). Google will return an error (OVER_QUERY_LIMIT) when it receives too many requests per second. So the loop waits two seconds before the next attempt. I try a total of three attempts. If I still don’t get an OK status back, I give up. Note that Google limits the free geocoding to 2,500 requests per IP per day. If that limit is reached, you also receive an OVER_QUERY_LIMIT error.

In order to get to the status of the response, I use the json() function to convert the JSON string to a JSON object (lines 64 and 67).

Parsing the JSON response

Now that we have our geocoded address stored in a JSON object, I can now use PL/JSON to extract the various elements we need.

Since the results object consist of an array, I pass this array to a JSON_LIST object (l_results) (line 96). This array, however, consists only of a single element, so I pass this to a variable of type JSON, named l_tempobj (line 99).

The variable l_tempobj now contains the JSON of the four elements listed above (address_components, formatted_address, geometry, and status).

Since the address_components is yet another array, I pass it to another JSON_LIST object named l_addr_comps (line 102). Now I can loop through the address components and test the types array element to find out what type of address component I’m dealing with. I use the CASE statement (line 117) to test the types and to assign the address elements to their individual PL/SQL variables.

Once the loop is done, I have all my address components I looked for, but I still want to get to the coordinates. Those are stored in the third array element (geometry) of l_tempobj.  I pass the location element to the l_loc variable (line 140) of type JSON., then extract the lat/lng strings from it, and pass them to PL/SQL varchar2 variables (lines 146 and 147).

At this point the procedure is done, and I print the results.

Notes

The JSON and JSON_LIST data types are created on installation of PL/JSON. When parsing through a JSON object, you need to be keenly aware of what you are processing: Whether it is a nested JSON object or a string or number. Notice that I had to use the get_number function to extract lat/lng, while using get_string to get the status.

Use json_list() to pass the value of an element that consists of an array:

"address_components" :  [{ "name 1" : value} ,{"name 2" : "value"},{"name 3" : "value"}]

Use json_ext.get_string (or get_number) to get the value of a non-array element:

"status" : "OK"

Other errors from the Google API:

ZERO_RESULTS – no address data was fund

REQUEST_DENIED – most likely the sensor parameter was not specified correctly. Check the error_message text included in the response.

I’ve also included a few commented out <obj>.print statements for you to see the contents of various objects.

Let me know how you fare with this. Comments are always appreciated.

CREATE OR REPLACE PROCEDURE geocode_address(p_address IN VARCHAR2) IS
  l_obj           json;
  l_results       json_list;
  l_tempobj       json;
  l_addr_comps    json_list;
  l_addr          json;
  l_typesarr      json_list;
  l_geom_obj      json;
  l_loc           json;

  l_status        VARCHAR2(255);
  l_types         VARCHAR2(30);
  l_short_name    VARCHAR2(200);
  l_street_number VARCHAR2(200);
  l_street        VARCHAR2(200);
  l_city          VARCHAR2(200);
  l_state         VARCHAR2(30);
  l_zip           VARCHAR2(10);
  l_county        VARCHAR2(30);
  l_country       VARCHAR2(10);
  l_tries         PLS_INTEGER := 0;
  l_address       VARCHAR2(4000);
  l_request       VARCHAR2(4000);
  l_pieces        utl_http.html_pieces;
  l_json          CLOB;
  l_lat           VARCHAR2(40);
  l_lng           VARCHAR2(40);

  e_query_limit  EXCEPTION;
  e_zero_results EXCEPTION;

  l_prc CONSTANT VARCHAR2(16) := 'GEOCODE ADDRESS';

BEGIN
  dbms_application_info.set_module(module_name => l_prc, action_name => 'START');

  -- replace empty spaces with +
  l_address := REPLACE(TRIM(p_address), ' ', '+');

  BEGIN

    l_request := 'http://maps.googleapis.com/maps/api/geocode/json?address=' ||
                 l_address || chr(38) || 'sensor=false';

    l_tries  := 0;
    l_status := NULL;
    l_json   := NULL;

    -- Try three gets in case of OVER_QUERY_LIMIT errors
    WHILE nvl(l_status, '*') != 'OK' AND l_tries < 3
    LOOP
      dbms_application_info.set_client_info(client_info => 'HTTP REQUEST try ' ||
                                                           l_tries);

      -- JSON object may be long, so split it into pieces
      l_pieces := utl_http.request_pieces(l_request);

      FOR i IN 1 .. l_pieces.count
      LOOP
        l_json := l_json || l_pieces(i);
      END LOOP;

      -- Create json object from google response
      l_obj    := json(l_json);

      -- Check the response status. If it is OK then proceede with parsing.
      l_status := json_ext.get_string(l_obj, 'status');

      IF l_status != 'OK'
      THEN
        dbms_output.put_line(l_prc || '  status [' || l_status || ']');
        IF l_status = 'OVER_QUERY_LIMIT' AND l_tries >= 3
        THEN
          dbms_output.put_line(l_prc || ': query limit reached. Exiting');
          RAISE e_query_limit;
        ELSIF l_status = 'ZERO_RESULTS'
        THEN
          RAISE e_zero_results;
        ELSE
          l_tries := l_tries + 1;
          dbms_lock.sleep(2);
        END IF;

      END IF;

      --dbms_output.put_line(l_json);
      l_json := NULL;

    END LOOP;

    BEGIN

      dbms_application_info.set_client_info(client_info => 'PARSE JSON');

      -- The overall JSON is an array named results
      l_results := json_list(l_obj.get('results'));

      -- There is only a single element in the results array, so get the first (and last) one
      l_tempobj := json(l_results.get(1));

      -- The next level contains an array named address_components
      l_addr_comps := json_list(l_tempobj.get(1));

      -- loop through the address components and test the types array for address elements
      FOR i IN 1 .. l_addr_comps.count
      LOOP
        l_addr := json(l_addr_comps.get(i));
        --l_addr.print;

        l_typesarr := json_list(l_addr.get('types'));

        -- Types is not a json array, but a string array so we have to get
        -- the first element using the types[1] syntax
        l_types      := json_ext.get_string(l_addr, 'types[1]');
        l_short_name := json_ext.get_string(l_addr, 'short_name');

        CASE l_types
          WHEN 'street_number' THEN
            l_street_number := l_short_name;
          WHEN 'route' THEN
            l_street := l_short_name;
          WHEN 'locality' THEN
            l_city := l_short_name;
          WHEN 'administrative_area_level_1' THEN
            l_state := l_short_name;
          WHEN 'administrative_area_level_2' THEN
            l_county := l_short_name;
          WHEN 'postal_code' THEN
            l_zip := l_short_name;
          WHEN 'country' THEN
            l_country := l_short_name;
          ELSE
            NULL;
        END CASE;

      END LOOP;

      -- now get lat/lng
      l_geom_obj := json(l_tempobj.get(3));
      --l_geom_obj.print;

      l_loc := json_ext.get_json(l_geom_obj, 'location');
      --l_loc.print;

      l_lat := to_char(json_ext.get_number(l_loc, 'lat'));
      l_lng := to_char(json_ext.get_number(l_loc, 'lng'));

      dbms_output.put_line('Address: ' || l_street_number || ' ' ||
                           l_street || ' ' || l_city || ' ' || l_state || ' ' ||
                           l_zip || ' ' || l_county || ' ' || l_country);
      dbms_output.put_line('lat/lng: ' || l_lat || ' ' || l_lng);

    EXCEPTION
      WHEN e_query_limit THEN
        raise_application_error(-20001, 'GOOGLE QUERY LIMIT REACHED');
      WHEN OTHERS THEN
        dbms_output.put_line(l_prc || ': ' || SQLERRM);
    END;
  EXCEPTION
    WHEN e_zero_results THEN
      dbms_output.put_line(l_prc || ': Zero Results. Getting next address');
  END;

  COMMIT;

  dbms_application_info.set_client_info(client_info => 'DONE');
EXCEPTION
  WHEN OTHERS THEN
    RAISE;

END geocode_address;
Next Page »

Blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.

Follow

Get every new post delivered to your Inbox.