Oracle Magazine

Generally my blog is just snippets of tech content that take my interest as I encounter them (most commonly when looking at AskTOM). If I think they’ll be useful, I’ll just plonk them out right there and then. If you prefer your content in longer (and more structured Smile) form, then also I publish longer form articles on Oracle Magazine every couple of months. Below is a consolidated list of my articles. I’ll try (more...)

External table preprocessor on Windows

There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing:


run_os.bat
==========
@echo off
cd \oracle
dir /b

SQL> create table fs_size (
  2   disk varchar2(64)
  3  )
  4  organization external
  5  (  type oracle_loader
  6     default directory temp
  7     access parameters
  8     ( records delimited by newline
  9       preprocessor  temp:'run_os. (more...)

Connections with a wallet – redux

Wow…it is nearly 4 years ago now that I wrote an article on connecting to the database via a wallet to avoid having to hard code passwords into script. That article is here:

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

So I went to do a similar exercise on my new 18c Windows database today, and to my surprise things went pear shaped at the very first step


c:\oracle\product\18\bin>mkstore -create -wrl c:\temp\wallet
The syntax of the command is incorrect.

Not (more...)

Disabled EZCONNECT

Just a normal start to the day today…I had my coffee

coffee_gif

and then started working on some AskTOM questions. Naturally pretty much the first thing I needed to do is connect to my database, and then this happened:


C:\oracle\product\18\bin>sqlplus scott/tiger@//gtx:1518/pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 6 09:23:09 2019
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve  (more...)

Less slamming V$SQL

It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today Smile

IMG_20190303_114220_012

When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are one or two poorly performing SQL statements, or the server being overly stressed with CPU load with no single SQL being the obvious cause.

In either case, a common plan of (more...)

Worth the wait

Yes, I know it’s been awhile Smile

Yes, I know people have been angry at the delay Smile

But, can we put that behind us, and rejoice in the fact…that YES

It’s here!

Yes, 18c XE for Windows is now available.

https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Most probably, most developers in your organization are running a Windows PC. Now every single one of them can have a fully functioning Oracle database for developing, prototyping, testing, deploying, learning, exploring, (more...)

Statistics on Object tables

Way back in Oracle 8.0 we introduced the “Object-Relational” database, which was “the next big thing” in the database community back then. Every vendor was scrambling to show just how cool their database technology was with the object-oriented programming paradigm.

Don’t get me wrong – using the Oracle database object types and features associated with them has made my programming life a lot easier over the years. But for me, it’s always been pretty (more...)

MERGE and ORA-30926

Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but I had a few requests for the SQL example end-to-end, so here it is.

Imagine the AskTOM team had a simple table defining the two core members, Chris Saxon and myself. (more...)

The death of UTL_FILE – part 2

I wrote a post a while back call “The Death of UTL_FILE”, and probably because of it’s click-bait title I got lots of feedback, so I’m back to flog that horse Smile. Seriously though, I stand behind my assertion in that post, that the majority of usages of UTL_FILE I’ve seen my career are mimicking the spooling behaviour of a SQL*Plus script. And as that post pointed out, you can now achieve that functionality directly with (more...)

Use the features available!

Advance warning: This post is a just another normal Friday morning rant. If you’re not interested in my pontificating, move along…nothing else to see here Smile

Sometimes you can’t make use of a facility that you normally would, and you have to substitute in something else. For example, if I would normally take the train to the basketball game, but today it’s not running due to track maintenance, then I’ll take the bus. I have no (more...)