JSON parser for PL/SQL in Apex 5 ?

I was looking at the Apex 4.2.2 installation script (coreins.sql), and noticed the mention of a new package, wwv_flow_json, which is apparently being worked on but was removed at the last minute "as no longer required for 4.2.2".

Even though the package is not installed (more...)

Dynamic SQL and RESULT_CACHE

Over the weekend one of my friends asked me for a clarification on RESULT_CACHE - a really interesting functionality that allows Oracle to cache in SGA results of PL/SQL function calls.

The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature (more...)

Performance tuning. Spending time is NOT OK (if you do not know exactly why)

Yet another performance tuning story, similar to one that happened about a month ago. Just to keep it short:

  • once upon a time there was a very time-consuming module.
  • eventually we were forced to take much closer look - WHY is it so time-consuming
  • we found in our own (more...)

Oracle Internals. Digging out PL/SQL fine-grain dependencies

After a couple of requests at IOUG Collab'13 I decided to resurrect a pretty old topic that I've participated in 2008 (since in 5 years there was no changes on that front :-) ).

As we all know, starting version 11g Oracle tracks PL/SQL dependencies in much more granular way (more...)

Sample code: Using Datapump API for metadata and data filtering

I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.

Structure for reload_dev_tables:

 Name                                       (more...)

Oracle Dynamic SQL: generic search – crazy case

For the Friday evening I decided to dig out my example from the most recent of my published books (Expert PL/SQL Practices, APress, 2011) - it was a very fun project to work with: first, to come with with the idea of such multi-author book, second, to write (more...)

Oracle Dynamic SQL: generic search – REF CURSOR

This post is a continuation of a topic, I've raised previously - dynamic implementation of generic searches. As  I mentioned in that post, there are circumstances, when it is much more convenient to return a pointer to a row-set instead of that row-set. Or in terms of Oracle - REF CURSOR (more...)

Oracle Dynamic SQL: generic search – simple case

It is a very common problem - how do you implement generic search over the table? By generic search I mean that users are provided with tons of different options that could be used in all possible permutations.

It leads to a standard problem - how do you make sure (more...)

Fun with CHAR

I'm busy deriving file layouts from PL/SQL. Probably close to 100 file definitions...each of them slightly different, each of them defined in the code. Fun!

There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to (more...)

invalid LOB locator specified: ORA-22275

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators: don't forget to include the FOR UPDATE clause in the SELECT statement fetching (more...)

DUMMY issues

I've just noticed in one of LinkedIn PL/SQL forums the following question:
- Which one is faster and why SELECT 1 FROM DUAL or SELECT ROWID FROM DUAL?

Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID (more...)

dbms_output.put_line

I've been scratching my eyes out lately trying to reverse engineer some lots of PL/SQL.

One thing I've seen a lot of is calls to dbms_output.put_line. Fortunately, I've seen some dbms_application_info.set_module and other system calls too. But back to that first one.

1. When I used dbms_output, (more...)

The power of using records in APEX III

Uncategorized
| Mar 14, 2013

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a (more...)

TO_DATE or not TO_DATE

Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what (more...)

Version 1.7 of Alexandria Utility Library for PL/SQL

Uncategorized
| Feb 17, 2013
The latest version of Alexandria, the utility library for PL/SQL, is now available for download. The previous version has been downloaded more than 2,000 times in the last 8 months.



The docs/readme.txt file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:

Improved installation script


You can now choose between installing the full library (close to 50 packages as of this version), by running the main /setup/install.sql script, or you can install just the core set of packages via setup/install_core.sql and then choose additional sets of (more...)

Interview with Yalim Gerger (CEO Formspider – the PL/SQL Web Framework)

Uncategorized
| Feb 12, 2013
In every other profession having a 20 year experience is a good thing, in ours you are treated as a has-been who is kept around only to maintain the legacy system. This is insane.

PL/SQL and Microsoft technology

Uncategorized
| Jan 16, 2013
Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).



Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.

Over the last couple of years, I've written a number of blog posts on this topic. This post is just (more...)

COLLECT and DISTINCT

Uncategorized
| Dec 10, 2012


In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')


SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
(more...)

Error Wrangling

Last week the OTN SQL and PL/SQL Forum hosted of those threads which generate heat and insight without coming to a firm conclusion: this one was titled WHEN OTHERS is a bug. Eventually Rahul, the OP, complained that he was as confused as ever. The problem is, his question asked for a proof of Tom Kyte's opinion that, well, that WHEN OTHERS is a bug. We can't proof an opinion, even an opinion from a well-respected source like Tom. All we can do is weigh in with our own opinions on the topic.

One of the most interesting things in (more...)

GOTOs, considered

Extreme programming is old hat now, safe even. The world is ready for something new, something tougher, something that'll... break through. You know? . And here is what the world's been waiting for: Transgressive Programming.

The Transgressive Manifesto is quite short:

It's okay to use GOTO.
The single underlying principle is that we value willful controversy over mindless conformity.

I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that GOTOs are "considered harmful". But as Marshall and Webber point out, (more...)