Refresh Multiple Materialized Views in One Go: No Data Found

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:

create (more...)

PL/SQL Teaser: A Valid Package With No Callable Subprograms

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...)

Speed Up Development with Logger: Generate a Template

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.
(more...)

Oracle APEX_WEB_SERVICE REST API call

In this post I will try to show you how I used the Oracle Apex and the APEX_WEB_SERVICE  PL/SQL package to quickly send a request to a public Internet API and how I handled the response. The code below was written during a 'Hackday' and hasn't been extensively tested.

My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.

The idea was to build an (more...)

APEX: Active Tabs Based on Page Groups

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...)

Alexandria PL/SQL Utility Library moved to GitHub

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...)

Why is it not possible to raise an exception when handling no_data_needed ?

It seems impossible to raise an exception when handling no_data_needed.

See

create or replace function demo return sys.odciNumberList pipelined
as
begin
  pipe row(1);
  pipe row(1/0);
exception when others then
  dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
  raise program_error;
end;
/

sokrates@11.2 > select * from table(demo());
ERROR:
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

As (more...)

NYOUG Training Event: "High Performance PL/SQL" on Nov 7 2014

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:

http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258

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...)

DBMS_REDACT and complete ROW update

My session on "Oracle 12c for Developers" is done.Afterwards someone asks the question:

What happens when you use DBMS_REDACT with a complete row update?
My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:


create table emp
as
select ename
,to_char (abs (dbms_random.random)) credit_card
from scott.emp
;


begin
dbms_redact.add_policy
(object_schema => 'A'
,object_name => 'EMP'
,policy_name => 'Hide Creditcard'
,expression (more...)

My new book

It seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you a one and only performance tuning book dedicated to PL/SQL:


This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - (more...)

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. (more...)

Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that (more...)

Time to resurrect blogging!

Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of Steven Feuerstein, who asked to tell how PL/SQL impacted our life. Here is my story:

-----------------
I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to (more...)

★ Database as a Storage (DBaaS) vs. Thick Database

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 (more...)

Avoid UTL_FILE_DIR Security Weakness – Use Oracle Directories Instead

Integrigy:

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...)

New in Oracle 12c: Querying an Associative Array in PL/SQL Programs

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...)

Are You Using BULK COLLECT and FORALL for Bulk Processing Yet?

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...)

The APEX_JSON package: Generating JSON from PL/SQL

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

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...)

The APEX_JSON package: Parsing JSON in PL/SQL


As I mentioned a year ago, it looks like Apex 5.0 will include a new package called APEX_JSON for parsing and generating JSON. In this blog post, I will take a quick look at the parsing, and the next blog post will be about generating JSON output.

Since Apex 5.0 is still in Early Adopter (beta) mode and not yet released for download, we can run a query against the data dictionary (more...)

Creating PDF reports from PL/SQL

Do you need to create PDF reports from PL/SQL?

There are a number of options:

  • Anton Scheffer wrote a PL/SQL package called AS_PDF3 that covers all the basics: Different fonts (including TrueType), images, and tables.
  • There's the commercial PL/PDF package that has more features, such as the ability to use existing PDF documents as templates for new documents, compression, encryption, and more. There's also an extra Toolkit component which allows you to extract pages from (more...)