Quick & Dirty: How to parse a delimited string and get the nth element

March 2, 2015 at 9:55 pm | Posted in Oracle Developement | Leave a comment

I have a comma delimited string:

Now I want to extract the 3rd element (is) from it inside a select statement:

select regexp_substr('Hello,this,is,my,string', '[^,]+', 1, 3) from dual;

To count the elements in my string I can use:

select regexp_count('Hello,this,is,my,string', ',', 1, 'i')+1 regexp_count from dual;

SQLd360 v3 now available, new features and (hopefully) less bugs!

February 27, 2015 at 8:51 am | Posted in Oracle Application Express (Apex), Oracle Developement, Oracle Performance, Oracle Tools, PL/SQL | Leave a comment

SQLd360 v3 now available, new features and (hopefully) less bugs!.

10 places where anyone can learn to code

February 24, 2015 at 2:39 pm | Posted in Oracle Developement | Leave a comment

Originally posted on TED Blog:

blog_learn_to_code_art_revTeens, tweens and kids are often referred to as “digital natives.” Having grown up with the Internet, smartphones and tablets, they’re often extraordinarily adept at interacting with digital technology. But Mitch Resnick, who spoke at TEDxBeaconStreet, is skeptical of this descriptor. Sure, young people can text and chat and play games, he says, “but that doesn’t really make you fluent.”

[ted_talkteaser id=1657]Fluency, Resnick proposes in this TED Talk, comes not through interacting with new technologies, but through creating them. The former is like reading, while the latter is like writing. He means this figuratively — that creating new technologies, like writing a book, requires creative expression — but also literally: to make new computer programs, you actually must write the code.

The point isn’t to create a generation of programmers, Resnick argues. Rather, it’s that coding is a gateway to broader learning.“When you learn to read, you…

View original 567 more words

Set Session State with JavaScript and dynamic action

January 16, 2015 at 9:38 am | Posted in Oracle Developement | Leave a comment
Tags: , , , , ,

I have two page items, P1_FIRST_NAME and P1_LAST_NAME whose values I wish to remain in session state. Here are two ways you can achieve this.

To have item values persist in session state with JavaScript in APEX, you can use the following code:

apex.server.process(‘DUMMY’,{pageItems: ‘#P1_FIRST_NAME,#P2_LAST_NAME’},{dataType: “text”});

An alternative method would be using a dynamic action that fires a dummy PL/SQL process. The action allows you to specify page items to submit in session state.;

PL/SQL Process to Set Session State

PL/SQL Process to Set Session State


September 26, 2014 at 2:19 pm | Posted in Oracle Developement | Leave a comment

Originally posted on Carlos Sierra's Tools and Tips:

eSPEnkitec’s Sizing and Provisioning (eSP) is a new internal tool designed and developed with Oracle Engineered Systems in mind. Thanks to the experience and insights from Randy Johnson, Karl Arao and Frits Hoogland, what began as a pet project for some of us, over time became an actual robust APEX/PLSQL application, developed by Christoph Ruepprich and myself, and ready to debut at Oracle Open World 2014.

This posting is about eSP, what it does, and how it helps on the sizing and provisioning of Oracle Engineered System, or I would rather say, any System where Oracle runs.

We used to size Engineered Systems using a complex and very useful spread sheet developed by Randy Johnson and Karl Arao. Now, it is the turn for eSP to take the next step, and move this effort forward into a more scalable application that sits on top of one of our Exadata machines.

Sizing an…

View original 720 more words

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 134 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.

Next Page »

Create a free website or blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.


Get every new post delivered to your Inbox.