In this series of blogs about Continuous Delivery and the Oracle database, I describe how to automate deployments (installations). In the previous two Blogs I have described the tools and techniques used to create and install migration scripts.
In this Blog I will describe the ‘(un)happy flow’ for a database deployment: what to do if the deployment (installation) is correct (the ‘happy flow’) and what to do if it goes wrong (the ‘unhappy flow’)?
One of the simplest tuning techniques to encapsulate PL/SQL used in APEX within packages, minimising the size of anonymous blocks. This applies to any PL/SQL within the page, including computations, processes, plugins, dynamic actions, validations, shortcuts and dynamic PL/SQL regions.
This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of interpreted at runtime.
Plug-ins can be wonderful black boxes and consumers may not care (more...)
"Come on, come on, let's stick together
" - Bryan Ferry
There's more to PL/SQL programs than packages, but most of our code will live in packages. The PL/SQL Reference offers
the following benefits of organising our code into packages:Modularity
- we encapsulate logically related components into an easy to understand structure.Easier Application Design
- we can start with the interface in the package specification and code the implementation later.Hidden Implementation (more...)
Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON.
I recently was working on a Node.js application that exposed a REST API to HTTP consumers. The consumers could send POST requests with a (more...)
Sending emails from the Oracle database can be both simply deceptively braindead easy, and confoundingly perplexingly awful at the same time. Easy, because all you have to do is call one of the supplied mail packages to send an email:
(sender => 'firstname.lastname@example.org'
,recipients => 'email@example.com'
,subject => 'Test Subject'
,message => 'Test Message');
(p_from => 'firstname.lastname@example.org'
,p_to => 'email@example.com'
,p_subj => 'Test Subject'
,p_body => 'Test Message'
In the previous Blog I have described how to implement CD for an Oracle database by using migration scripts.
In this Blog I will describe how to create migration scripts (automagically).
This is the more simple case because you need them less often. There are various data compare tools which create scripts to migrate the differences between a source and target schema:
• dbForge Data Compare for Oracle, v3.7 Express (free)
Guys, AIOUG is back again with OTN Yathra’ 2016. It is a series of technology evangelist events organized by All India Oracle Users Group in six cities touring across the length and breadth of the country. It was my extreme pleasure to be the part of it in 2015 and I’m pleased to announce that … Continue reading
Obviously Interface Segregation is crucial for implementing restricted access. For any given set of data there are three broad categories of access:
- administration and governance
So we need to define at least one interface - packages - for each category in order that we can grant the appropriate access to different groups of users: read-only users, regular users, power users.
But there's more to Interface Segregation. This example is based on a procedure (more...)
Here are some more principles which can help us design better programs. These principles aren't part of an organized theory, and they're aren't particularly related to any programming paradigm. But each is part of the canon, and each is about the relationship between a program's interface and its implementation.
The Principle Of Least Astonishment
Also known as the Principle of Least Surprise
, the rule is simple: programs should do what we expect them to (more...)
When we talk about program design we're mainly talking about interface design. The interface is the part of our program that the users interact with. Normally discussion of UI focuses on GUI or UX, that is, the interface with the end user of our application.
But developers are users too.
Another developer writing a program which calls a routine in my program is a user of my code (and, I must remember, six months after (more...)
This example, tested on Oracle 11, shows how you can define bind variables in SQL*Plus, assign values to them in PL/SQL then display those values afterwards back in SQL*Plus:
SQL> variable bv1 varchar2(3)
SQL> variable bv2 number
2 select 'ABC' into :bv1 from dual;
3 select 123 into :bv2 from dual;
PL/SQL procedure successfully completed.
SQL> print bv1
SQL> print bv2
Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library. The DBMS_CREDENTIAL package is … Continue reading
Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.
The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)
Designing PL/SQL Programs is a succession of articles published the articles in a nonlinear fashion. Eventually it will evolve into a coherent series. In the meantime this page serves as a map and navigation aid. I will add articles to it as and when I publish them.
IntroductionDesigning PL/SQL Programs
Principles and PatternsIntroducing the SOLID principlesIntroducing the RCCASS principlesThe Dependency Inversion Principle: a practical example
Tools and Techniques
In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created (more...)
Ever since I started exploring the idea of using a TAPI approach with Apex, something I was never quite satisfied with was Tabular Forms.
They can be a bit finicky to work with, and if you’re not careful you can break them to the point where it’s easier to recreate them from scratch rather than try to fix them (although if you understand the underlying mechanics you can fix them [there was an article about (more...)
For a procedure (or function) in a package, you can assign default values only in the package specifications without assigning them in the package body:
SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
Package TEST_DEFAULT_PKG compiled
SQL> SHOW ERRORS;
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER) AS
Package body TEST_DEFAULT_PKG compiled
SQL> SHOW ERRORS;
Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:
-- A function that uses PL/SQL
create or replace function add_one( value number ) return number is
l_value number(10):= value;
-- A SQL statement that uses the PL/SQL function
select sum(add_one(id)) (more...)