I have been awfully quiet on my blog lately. I think that is because I have been busy with other things, like my garden and stuff like that. There are some ‘techie’ thing I have done in the meantime, though,
I have submitted a couple of abstracts for Tech14. Hope at least one of them gets selected. I really like presenting and if it is in a different country, that is just a plus. That (more...)
firstname.lastname@example.org > create procedure p is begin null; end this_does_not_compile;
Warning: Procedure created with compilation errors.
email@example.com > show errors
Errors for PROCEDURE P:
1/32 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P'
at line 1, column 11
firstname.lastname@example.org > REM expexted
email@example.com > create function f return number is begin return null; end this_does_not_compile;
Warning: Function created with compilation errors.
firstname.lastname@example.org > (more...)
Being a huge fan of Logger, the PL/SQL logging utility, I really wanted this be to included in the project that I'm currently working on. So I downloaded it (link at the bottom of this blog) and included it in our deployment scripts. Done.... at least I thought so, but of course this wasn't the case.
The regular install script for Logger looks something like the following (parts removed and table names are changed):
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.
The function defined is something like this:
In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:
SQL> create or replace function f_deterministic(p varchar2)
2 return varchar2
7 return p;
SQL> set (more...)
PL/SQL is procedural language, that's very useful when you work with Oracle Database. As DBA, you might have to write PL/SQL to do some tasks. Developer on Oracle Database must know about PL/SQL.
This post I mention a book title - Oracle PL/SQL Programming
by Steven Feuerstein (@stevefeuerstein
), Bill Pribyl
. This book is a new edition that covers PL/SQL on Oracle Database 12c. Readers can use examples in a book for practice and get (more...)
I have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.
The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the (more...)
After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this presentation was that the type cannot include self-reference. Neither direct nor indirect.
A table like the emp table cannot be expressed as an object type. The table has a column mgr which is a reference to another employee.
So I tried something like this:
This year, from June 22 - June 26, the best convention in the world, ODTUG’s KScope14 will be held in Seattle, Washington. I am already looking forward to meeting some ‘old’ friends again, creating new friends and seeing some of the best content by presenters from all over the world.
Some of the presentations I am looking forward to:
In the Developer’s Toolkit track:
It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road (more...)
Triggered by a comment on an older article I wrote a little article
yesterday on determining the instance of an object. As far as known by me at least and seemingly by Google there is no java instanceOf counterpart in Oracle Pl/Sql. In SQL Where clauses you can use the (more...)
Already four and a half year ago I wrote an article on Oracle Type inheritance and type casting. See here
. But although Object types are supported by Oracle since Oracle 8, it seems to me that there is still little familiarity on the subject.
I'm not really a Pl/Sql (more...)
Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he (more...)
Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve (more...)
Here are two interesting enhancements in Oracle database 12c PL/SQL. PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can (more...)
Thank you all who attended my sessions at NYOUG Fall Conference this morning. I appreciate spending you most precious commodity - your time
- with me. I sincerely hope you found both the presentations enlightening as well as entertaining.
Please see the details of the sessions below along with the (more...)
Working on the chapter that covers best ways of manipulating with object collection. Of course, if you talk about pagination of the rowset, it is very tempting to test Oracle 12c new feature - OFFSET…FETCH.
Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH (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...)
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...)