One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.
With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)
Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.
First of all let's start with the version of the database that I tested this on. Yes, I know (more...)
When some of my users were using my system to send emails, they’d often copy-and-paste their messages from their favourite word processor, but when my system sent the emails they’d have question marks dotted around, e.g.
“Why doesn’t this work?”
would get changed to
?Why doesn?t? this work??
Simple fix was to detect and replace those fancy-pants quote characters with the equivalent html entities, e.g.:
function enc_chars (m in varchar2) return (more...)
Since I have written about my pet project about a month ago, I have made some major changes to the functionality of it.
If you haven't read that blog about my pet project, here's the synopsis:
I love Logger to instrument my code, I just don't like to type in all the bits and pieces to register all the input arguments when I write a new procedure or function. To solve this problem I have (more...)
The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just (more...)
Execution Plan Instability – What is the problem?
The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.
In the beginning, the Optimizer was rule-based.
The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.
This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.
This means that (more...)
When you are working with and developing Decision Trees by far the easiest way to visualise these is by using the Oracle Data Miner (ODMr) tool that is part of SQL Developer.
Developing your Decision Tree models using the ODMr allows you to explore the decision tree produced, to drill in on each of the nodes of the tree and to see all the statistics etc that relate to each node and branch of the (more...)
The PL/SQL reference guide has a short section and example for the INSTEAD OF CREATE ON SCHEMA trigger. Since the description was so little and could not find much detail on the internet, tried some examples as below. This feature is good to prevent any CREATE operation for an user, giving him/her privilege to alter or drop […]
To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).
The procedure that I initially wrote was the following:
Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):
The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question (more...)
Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
Thanks to twitter I found this post
by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.ORA-01489: result of string concatenation is too long
I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search (more...)
Recently someone asked me: "How did you do that? When I include an APEX page in a Page Group, the correct tab is automatically highlighted"
When I setup an application, I usually use Dimitri Gielis' method,
so instead of using "real tabs", I use a List and display that list as Tabs.
For each of the "Tabs", I also create Page Groups, just to keep things organized.
Each of the List Entries will have a (more...)
The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub
. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.
The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library
which highlights some of (more...)
Steven Feuerstein runs a great site at PL/SQL Challenge
that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.
PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.
Unfortunately do to a timing
It seems impossible to raise an exception when handling no_data_needed.
create or replace function demo return sys.odciNumberList pipelined
exception when others then
dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
firstname.lastname@example.org > select * from table(demo());
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero
no rows selected
in exception handler, sqlcode: -1476
If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:
I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to (more...)