Oracle PL/SQL Programming 6th edition by Steven Feuerstein

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

PL/SQL, UTL_HTTP and Digest Authentication

For the first time in what seems like ages, I've actually put together a piece of code worth sharing. It's not that I haven't been working, but just that it has all been very 'in-house' specific.

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

In (more...)

Typical

After a presentation by Lucas Jellema I decided to try something with types in Oracletable_EMP. 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:

type_EMP_T

KScope14

ODTUG_Kscope14

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:

Readable Code for Modify_Snapshot_Settings

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

Oracle Database 12c PRAGMA UDF and WITH clause enhancements

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

New York Oracle User Group Fall Conference Materials

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

Oracle 12c OFFSET…FETCH.. bug?

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

JSON parser for PL/SQL in Apex 5 ?

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

Dynamic SQL and RESULT_CACHE

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

Performance tuning. Spending time is NOT OK (if you do not know exactly why)

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

Oracle Internals. Digging out PL/SQL fine-grain dependencies

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

Sample code: Using Datapump API for metadata and data filtering

I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.

Structure for reload_dev_tables:

 Name                                       (more...)

Oracle Dynamic SQL: generic search – crazy case

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

Oracle Dynamic SQL: generic search – REF CURSOR

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

Oracle Dynamic SQL: generic search – simple case

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

Fun with CHAR

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

dbms_output.put_line

I've been scratching my eyes out lately trying to reverse engineer some 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, (more...)

The power of using records in APEX III

Uncategorized
| Mar 14, 2013

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a (more...)

TO_DATE or not TO_DATE

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