Bryn Llewellyn (Distinguished Product Manager, Database Division, Oracle) presented at OOW2016 on new features in PL/SQL in Oracle Database 12cR2. One of the features that stood out was a new pragma deprecate that can be added to program units such as functions and procedures inside packages. This pragma is used to mark a program unit at deprecated, which typically means: the program unit is still valid, but has been superseded by a better option and (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story..
For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, whether there is any discrepancy between the schema that was used to build the application (.ear) files and the target schema of the deployment. Of (more...)
Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
Because my hero is Cary Millsap, I'm going to do what he did and publish my
Brendan Tierney and (more...)
Today a box arrived from Oracle Press. In it were a few copies of “Real-World SQL and PL/SQL” which I co-authored with Arup Nanda, Brendan Tierney, Heli Helskyaho and Alex Nuitjen. I know I only blogged about the book a couple of weeks back, how I became involved and the impact it had on my life for several months, but as I can now physically handle and read (more...)
If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.
- SMS (Short Message Service)
- MMS (Multimedia Message Service)
- Text to Voice
I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:
Sending an SMS is as (more...)
Almost a decade ago (in 2007), Oracle released a whitepaper on Integrating Application Express with PayPal Payments Pro which used PayPal's Name Value Pair (NVP) API.
In the years since then, PayPal has made available a new API which is (more...)
I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.
The British part of me wants (more...)
column1 = column2 or (column1 is null and column2 is null)
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?
First of all, the policy applied was a rather complex one. A complex query should be executed to determine if the current (more...)
Somethings it can be a challenge to know what is or isn't a reserved word. Yes we can check the Oracle documentation for the SQL reserved words and the PL/SQL reserved words. There are other references and (more...)
Machine learning and neural networks in particular, are currently hot topics in data processing. Many tools and platform are now easily available to work and experiment (more...)
In short, the packages provided in the Alexandria Utility Library for PL/SQL allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3).
At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM) to create a separate (more...)
That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.
Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these (more...)