Search

Top 60 Oracle Blogs

Recent comments

plsql

Expanded TYPE definitions for PL/SQL 21c

There has always been that odd conflict between the language you use for SQL versus the language you use for PL/SQL when it comes to expressing what appears to the be the same thing.

PL/SQL: Do want a datatype that contains two elements? Use a RECORD


SQL> declare
  2    type MY_EMPS is record (
  3     empno number,
  4     ename varchar2(10)
  5     );
  6  begin
  7    null;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL: Do want a datatype that contains two elements? Use an OBJECT TYPE

PL/SQL in 21c gets amazing new loop iterators

“It’s a dead language” people said.

“There’s never anything new” people said.

Updating my APEX 20.2 installation

One of (or should I say “another of”!) the very cool features of Application Express (APEX) is that by being a database-centric software installation, patching of the product can be done very efficiently and easily by simply loading fresh versions of the underlying PL/SQL source.

The reason loading PL/SQL source is such a good thing is that when you load PL/SQL source that is unchanged the database can simply treat that as a “no-op” which

  • makes loading the unchanged PL/SQL faster,
  • avoids invalidation and recompilation impacts

We can see that with this simple demo.

Video : Simple Oracle Document Access (SODA) for PL/SQL

In today’s video we give a demonstration of Simple Oracle Document Access (SODA) for PL/SQL. SODA is a feature of Oracle REST Data Services (ORDS),
but this PL/SQL interface for SODA was introduced in Oracle Database 18c.

The video is based on this article.

You might find these useful.

How to do a GRANT on an entire schema

TL;DR: There is no schema grant command in Oracle, but this routine lets you get close.

Why no schema level grant?

A very common request on the various forums that exist for the Oracle Database is a “schema level grant”. The idea is that you could do something like


grant select on HR to SCOTT;

The concept sounds relatively straightforward but there would ne some nuances to cover off, for example:

How to use DBMS_PIPE to halt and continue a PLSQL database session

I posted a message on twitter saying that DBMS_PIPE is an excellent mechanism to make a session run and halt in PLSQL. One response I gotten was asking for an example of that. That is what this post is about.

DBMS_PIPE is an implementation of a pipe inside the Oracle database. A pipe is a mechanism that is not limited to the Oracle database, in fact I assume the implementation is inspired by an operating system level pipe, which can be created using the ‘mknod /path/pipename p’ on unix and unix-like systems. A quick search shows windows has got them too, but not really, or does it? Another implementation is the pipe to redirect output from one command to the next using ‘|’. The essence of a pipe that input to and output from the pipe are separated from each other, and that information is flowing from the inputting process of the pipe to the outputting process.

Level up your audit trigger game

A weekend audit

Often if you see the words “weekend audit” in a non-technical sense, it means that awkward moment where you look at the state of your house/bedroom/bathroom/bookshelf/shed etc and say to yourself “Yup, it is time we had a weekend audit of all this crap” Smile

A guided path to database expertise

At college/university, the learning path for a computing-related course often includes several coding languages, and occasionally some treatment of database technology as well. But often, it is only once you enter the business world as you embark upon your IT career that you truly start to face the demands of enterprise-level data requirements. The small amount of education on databases, or the small databases themselves sometimes do not prepare you for the realisation that data drives everything in the business world.

And where does one start? The amount of content out there about databases is overwhelming, and often assumes a base level of knowledge or is targeted at solving specific business problems.

If you have faced this obstacle, then I’m pleased to offer a new resource for you. One simple, consolidated, FREE training guide to kick start your database knowledge on developing applications on the Oracle Database.

Enjoy!

Code re-use … Not a substitute for brain use

I was doing some code review today of a PL/SQL package, and came across a routine to generate a list of employees for a department.  Truth be told, it wasn’t employees and departments but some anonymity is called for here. Smile

Arbitrary length addition and subtraction

This one just for fun today. An AskTOM question came in about arbitrary length arithmetic because “NUMBER(38) was not enough”. After some back-and-forth discussions it turned out that the business need under the requirement was managing bit strings. The implementation was currently converting the bits to decimals, hence the need for potentially very large number handling.

The problem was ultimately tackled with using RAW datatypes and holding the bits as raw strings, but I thought it would be interesting to throw together an addition and subtraction facility where the boundaries could exceed NUMBER(38).

So using nested tables, I had some fun with the code below.