PROCEDURE solve (my_problem IN varchar2) IS
my_idea := have_great_idea (my_problem) ;
my_code := start_coding (my_idea) ;
IF i_hit_complications (my_idea)
new_problem := the_complications (my_idea);
NULL; --we will never get here
This abuse of recursion was inspired by @ThePracticalDev !
Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions
that can raise exceptions
(think of currency conversion and a non-existent currency code gets passed into the function), DML error logging
and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery
to benefit from the built-in scalar subquery caching feature (more...)
If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.
|Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.|
Consider this scenario of dynamic actions on change of P42_ITEM:
|Synchronous vs Asynchronous server calls|
One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".
For the examples I will use (more...)
In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)
In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.
create type integer_ntt as table of integer
select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.
To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.
create table (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...)
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
These design principles may seem rather academic, so let's look at a real life demonstration of how applying Dependency Inversion Principle lead to an improved software design.
Here is a simplified version of an ETL framework which uses SQL Types in a similar fashion to the approach described in my blog post here
. The loading process is defined using an abstract non-instantiable Type like this:
create or replace type load_t force as object
( txn_date (more...)
Rob C Martin actually defined eleven principles for OOP. The first five, the SOLID principles, relate to individual classes. The other six, the RCCASS principles, deal with the design of packages (in the C++ or Java sense, i.e. libraries). They are far less known than the first five. There are two reasons for this:
- Unlike "SOLID", "RCCASS" is awkward to say and doesn't form a neat mnemonic.
- Programmers are far less interested in (more...)
PL/SQL programming standards tend to focus on layout (case of keywords, indentation, etc), naming conventions, and implementation details (such as use of cursors). These are all important things, but they don't address questions of design. How easy is it to use the written code? How easy is it to test? How easy will it be to maintain? Is it robust? Is it secure?
Simply put, there are no agreed design principles for PL/SQL. So it's (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;