Hansel and Gretel are in the forest and don’t want to get lost, so Gretel leaves a trail of white pebbles to mark their way. In future adaptations of this Bros. Grimm story, the white pebbles have become ‘breadcrumbs.’
So what does this have to do with SQL Developer?
A friend asked me how to get an Oracle view definition out of a
LONG column and into a web application. I thought it was an interesting question because I ran into a similar problem when writing the Oracle Database 12c PL/SQL Programming book.
One of the new Oracle (more...)
I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.
SQL> create table t as select 1 a from dual;
2 j (more...)
I haven't installed 12c yet because
a) I haven't had the time
b) my Linux skills aren't the best
What I have done though is read through some of the documentation in the New Features
The APEX features are just regurgitating what's in 4.2, since that's what's shipped (more...)
If your schemas are like those I deal with, almost every table has a doppelgänger which serves as a journal table; an “after insert, update or delete” trigger copies each and every change into the journal table. It’s a bit of a drag on performance for large updates, isn’t it?
UTL_HTTP package provides easy to use methods to fetch data from web using http protocol. Today, one of my co-workers complained that “utl_http.request” method returns only a small part of the web page instead of full html. She says she already uses it to fetch from other web pages (more...)
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...)
Have you heard of Oracle Designer? Perhaps you even still use it?
Designer incorporates support for business process modeling, systems analysis, software design and system generation.
I don’t want to get you too excited, because this technology is being phased out. You can read the official statement of direction here (more...)
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...)
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...)
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...)
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:
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...)
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...)
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...)
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...)
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...)
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...)