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.
© Eddie Awad's Blog, (more...)
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;
SQL> create table test_array as
2 select level num_col from dual
3 connect by level <= 10;
SQL> select * from test_array;
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...)
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...)
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...)
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...)
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...)
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 a chapter focused on Dynamic SQL.
As one of the examples I decided to illustrate how XMLType can be used as both a collection of (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...)
I've been scratching my eyes out lately trying to reverse engineer
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
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...)
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.
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...)
Tom Kyte on many new Database 12c features: http://t.co/z98lLxHH Via: (https://twitter.com/lucasjellema/status/257019546974683136) Filed under: DB Architecture, Oracle 12c, plsql Tagged: LinkedIn, New Features, OOW, Oracle 12c, Tom Kyte
We were recently looking for a way to easily anonymize some customer data, specifically bank account numbers.
Part of our testing mechanism is to verify that a bank account and sort-code combination is valid and we retrieve the bank details for that combination (using a commercial account lookup service). So we couldn't just replace the sort-code and/or account number with random numbers.
Conversely, replacing all account numbers with a handful of known, "safe" working values would lead to skewed data spreads, so that wouldn't work.
A better solution is to shuffle the existing data, moving the sort-code and account (more...)