A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.
I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.
In the foreword of the book, Bryn Llewellyn writes:
The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.
I was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.
SQL> drop table test_array purge; Table dropped. SQL> create table test_array as 2 select level num_col from dual 3 connect by level <= 10; Table created. SQL> select * from test_array; NUM_COL ---------- 1 2 3 4 5 6 7 8 9 10 10 (more...)
Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.
That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, (more...)
In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.
There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output (more...)
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; 2 / Warning: Procedure created with compilation errors. email@example.com > show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 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; 2 / Warning: Function created with compilation errors. firstname.lastname@example.org > (more...)
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:
However I had a recent requirement to use a web service that makes use of Digest Authentication. If you have look at the UTL_HTTP SET_AUTHENTICATION subprogram, it only addresses Basic authentication (and, apparently, Amazon S3 which looks intriguing).
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...)
Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH (more...)
The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature (more...)
As we all know, starting version 11g Oracle tracks PL/SQL dependencies in much more granular way (more...)
As one of the examples I decided to illustrate how XMLType can be used as both a collection of (more...)
It leads to a standard problem - how do you make sure (more...)
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...)
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...)