How to identify SQL performing poorly on an APEX application?

September 5, 2014 at 9:23 am | Posted in Oracle Developement | Leave a comment

Originally posted on Carlos Sierra's Tools and Tips:

Oracle Application Express (APEX) is a great tool to rapidly develop applications on top of an Oracle database. While developing an internal application we noticed that some pages were slow, meaning taking a few seconds to refresh. Suspecting there was some poorly performing SQL behind those pages, we tried to generate a SQL Trace so we could review the generated SQL. Well, there is no out-of-the-box instrumentation to turn SQL Trace ON from an APEX page… Thus our challenge became: How can we identify suspected SQL performing poorly, when such SQL is generated by an APEX page?

Using ASH

Active Session History (ASH) requires an Oracle Diagnostics Pack License. If your site has such a License, and you need to identify poorly performing SQL generated by APEX, you may want to use find_apex.sql script below. It asks for an application user and for the APEX session (a list is provided…

View original 508 more words

Use jQuery to check if a report returned rows in Apex

August 19, 2014 at 2:03 pm | Posted in Oracle Developement | Leave a comment

In order to conditionally hide/show a button, I needed to know whether a particular report on a page returned any rows.

This can easily be done with a line of jQuery, which differs between classic and interactive reports:

  1. Make sure to give your report a static ID. In this example it is emp.
  2. Classic Report:  If data is returned there will be a div with an id that prepends ‘report_’ to the report static id. Check the length of that div to find out if it exists. If it doesn’t, the length will be zero, meaning no rows were returned.
    1. $('#report_emp').length
  3. Interactive Report:
    1. $('#emp').find('div[id="apexir_DATA_PANEL"] table').length
    2. Here, if the length comes back as one, the report returned no rows.

Thanks to @rimblas for a correction.

Free script to generate a Line Chart on HTML

July 29, 2014 at 10:54 am | Posted in Oracle Developement | Leave a comment

Free script to generate a Line Chart on HTML.

Pins Polygons and Perspectives

July 11, 2014 at 2:53 pm | Posted in Oracle Developement | Leave a comment

This is the presentation I gave at KScope 14 in Seattle this year.

Ever wonder how much easier it would be to make sense of your geographic data if you could visualize it? Learn how to free your geographic data from the confines of your tables, and render it in colorful pins and polygons, to provide you with a better perspective.

Pins Polygons and Perspectives

Oracle ACE Associate

June 11, 2014 at 10:23 am | Posted in Oracle Developement | 1 Comment

Oracle ACE Associate

I have received the honor of becoming an Oracle Ace Associate. I’m proud and humbled to be part of this group of technical Oracle experts, and hope to live up to their standards.

A new future for Enkitec

May 15, 2014 at 2:25 pm | Posted in Oracle Developement | Leave a comment

Enkitec has been acquired by Accenture. We’re very excited about this move, and the new opportunities it brings.

More details at Enktec.com.

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.

Next Page »

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

Follow

Get every new post delivered to your Inbox.