Listener log data mining with SQL

Uncategorized
| Dec 15, 2019

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.


16-DEC-2019 09:54:20 * service_update * db18 * 0
2019-12-16T09:54:23. (more...)

Friday Funny – Festival of serious yahoos

Uncategorized
| Dec 12, 2019
I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons
If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty (more...)

PostgreSQL Creating Schema

Uncategorized
| Dec 10, 2019

The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:

GRANT CREATE ON DATABASE videodb TO student;

As the student user, you create the app schema with the following syntax:

CREATE SCHEMA app;

Then, you can query the result as follows:

SELECT   * 
FROM     pg_catalog.pg_namespace
ORDER BY nspname;

You should see the following:

      nspname       | nspowner |               nspacl                
 (more...)

Where would we be if we just believe?

Uncategorized
| Dec 10, 2019
As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. (more...)

PostgreSQL WITH to INSERT

Uncategorized
| Dec 3, 2019

This demonstrates how you insert results from a common table expression (CTE) in a leading WITH clause. I thought it would be a nice add since the existing tutorials didn’t have an example.

Create the message table, like this:

CREATE TABLE message
( message_id    SERIAL
, message_text  VARCHAR );

Now, here’s a CTE with a two fabricated rows:

WITH cte AS
( SELECT 'x-ray' AS msg
  UNION ALL
  SELECT 'MRI' AS msg )
INSERT INTO  (more...)

My Presentations at #UKOUG #techfest19

Uncategorized
| Dec 3, 2019
I just finished my second presentation at TechFest19 in sunny Brighton, England. The conference this year is great from every point of view: venue, people, content, conversations… Thanks to those who came to my talks for your benevolent attention and excellent questions/remarks! Both my presentations are now on SlideShare. I beg you to download them […]

Human readable JSON, stored in BLOB

Uncategorized
| Nov 26, 2019

Currently Oracle (we're using Oracle 18c at the moment) is still recommending to store your JSON document in a BLOB column. More efficient, no character conversions and probably some more advantages.

When there is a need to look at the JSON document, having it as a BLOB is not very useful.

An easy way to make the JSON Blob readable is to use the following query:


select json_query (i.json_payload, '$' returning clob pretty)
from (more...)

Athena – SQL to get date of next Monday

Uncategorized
| Nov 18, 2019

I was recently asked how to get date of next Monday irrespective of which day of the week sql is executed. So thought to share it, in-case someone else has such requirement.

select date_add('day', 8 - extract(day_of_week from current_date), current_date)

 
Or,

select date_trunc('week', current_date) + interval '7' day;

Happy learning 🙂

 

Oracle Apex Social Sign in

Uncategorized
| Nov 13, 2019
In this post I want to show you how I used the Oracle Apex Social Sign in feature for my Oracle Apex app. Try it by visiting my web app beachmap.info.




Oracle Apex Social Sign in gives you the ability to use oAuth2 to authenticate and sign in users to your Oracle Apex apps using social media like Google, Facebook and others.

Google and Facebook are the prominent authentication methods currently available, others will (more...)

PostgreSQL and LPAD

Uncategorized
| Nov 12, 2019

While porting my Oracle code to PostgreSQL I encountered a little quirk. It’s probably not a quirk except for the fact that I’ve worked in Oracle so long. Oracle implicitly type casts so well that we seldom notice.

PostreSQL doesn’t work like Oracle. It does type cast sometimes but not very often. I tried porting the following segment from my Oracle stored procedure to PostgreSQL:

/* Add account number with zeros. */
FOR j IN  (more...)

Postgres Foreign Keys

Uncategorized
| Nov 6, 2019

Just sorting out how to query the information_schema to discover the magic for a query of a table’s foreign key constraints. This query works to return the foreign key constraints:

SELECT   conrelid::regclass::text AS table_from
,        conname AS foreign_key
,        pg_get_constraintdef(oid)
FROM     pg_constraint
WHERE    contype = 'f'
AND      connamespace = 'public'::regnamespace 
AND      conrelid::regclass::text = 'rental_item'
ORDER BY conrelid::regclass::text
,        conname;

It returns the following for the rental_item table:

 table_from  |   foreign_key    |                           pg_get_constraintdef                            
-------------+------------------+---------------------------------------------------------------------------
 rental_item |  (more...)

Postgres Overloaded Routines

Uncategorized
| Nov 5, 2019

Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.

Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. (more...)

DBeaver for PostgreSQL

Uncategorized
| Nov 3, 2019

I’m migrating my database classes from the Oracle database to the PostgreSQL database. Using the Oracle Express Edition has always required a virtualized image because students use Windows and Mac OS. Also, the university doesn’t like my use of a virtualized image. Virtualization imposes incremental cost on students to have high end laptops.

The available Docker images don’t typically support the Oracle Express Edition. That means there are licensing implications tied to Oracle.

As a (more...)

Java and Postgres

Uncategorized
| Nov 2, 2019

I wanted to get Java working with PostgreSQL to test some GUI interfaces on Linux. Figuring out the necessary JAR file for the JDBC was my first hurdle. I found it was postgreSQL-42-2.5.jar file.

You can download it with the following command line:

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

I downloaded it to a Java directory off the home/student directory. Then, I added the following CLASSPATH to local java.env environment file.

 (more...)

MATCH_RECOGNIZE Restrictions

Uncategorized
| Oct 31, 2019
Sometimes we think that certain restrictions are not documented when in fact they are. Where do we forget to look? Database Error Messages

The holistic SQL tuning series

Uncategorized
| Oct 24, 2019

I did a set of articles for Oracle Magazine on a more holistic view of SQL tuning. What do I mean by “holistic”? It was a reflection of a common problem that I see when questions come into AskTOM, or when people in the community approach me at conferences, namely, there is an inclination to dive straight into the deepest levels of the tuning exercise:

  • “What index should I create?”
  • “Should I increase the (more...)

Subquery

Uncategorized
| Oct 24, 2019


with x as (select sysdate from dual)
select * from utter_bollocks.x;

Machine Learning with SQL

Uncategorized
| Oct 19, 2019
Python (and soon JavaScript with TensorFlow.js) is a dominant language for Machine Learning. What about SQL? There is a way to build/run Machine Learning models in SQL. There could be a benefit to run model training close to the database, where data stays. With SQL we can leverage strong data analysis out of the box and run algorithms without fetching data to the outside world (which could be an expensive operation in terms of (more...)

OGB Appreciation Day: APEX_DATA_PARSER: Flexible, Powerful, Awesome (#ThanksOGB #OrclAPEX)

Uncategorized
| Oct 10, 2019

For this years OGB Appreciation Day I wanted to highlight the very awesome APEX_DATA_PARSER.

At my current project one of the requirements is that CSV-files are to be uploaded by the user and the application has to figure out what type of file it is and process accordingly.
So the challenge with this is:

  • Which type of file is it?
  • What delimiter was used?

Uploading the file is straight forward enough with Application Express (APEX) (more...)

LISTAGG() as a Cumulative Function

Uncategorized
| Sep 30, 2019
LISTAGG() can be used as an analytic function, but cannot be cumulative. What does that mean, and is there a workaround?