Close

Sending Email with Oracle, Part 3: UTL_SMTP – Introduction

Sending Email with Oracle UTL_SMTP UTL_SMTP has a richer functionality than the UTL_MAIL or APEX_MAIL packages; but that extra versatility comes with more complicated syntax. Before digging into the details of how the package works I’ll first show a short UTL_SMTP example, simply sending “Hello World!” to two recipients. DECLARE crlf CONSTANT VARCHAR2(2) := UTL_TCP.crlf;…

SQL vs Towers of Hanoi

The Towers of Hanoi are a classic children’s puzzle. With 3 pegs and a set of disks of differing sizes, the goal is to move a stack of disks (arranged with largest on the bottom to smallest on top) from one peg to another moving only one disk at a time, never putting a larger…

Recursive WITH clause join bug

I recently ran into a strange bug where I spent a lot of time banging my head trying to figure out why my recursion wasn’t working, causing ORA-32044: cycle detected while executing recursive WITH query errors to show up. After simplifying the query down to a single counter column I eventually discovered the problem was…

SQL meets Calculus

Yesterday, Connor McDonald posted an interesting article about a math puzzle he received asking him to calculate the integral of a function over a range. He posted two variations using the Trapezoidal Rule. In his post he mentions the Riemann sums method and I thought it would be interesting to explore that option as well.…

How to TNSPING without TNSPING

While the Instant Client is a popular client installation for many applications, very often developers and admins become frustrated by the conspicuous absence of the TNSPING utility. Developers using jdbc thin clients may also miss having this handy tool at their disposal. Fortunately there are ways to replicate some of the functionality of TNSPING using…

LDAP – TNS service name package v2.3

I’ve released version 2.3 of my service name maintenance package for LDAP servers implementing the OID schema. I’ve exposed the get_entry function both as a stand alone function the opens, reads, and closes a connection to the ldap server, and as an API accepting an already established connection and using it. Along with the function…

How to put an “IF” inside SQL

Developers coming from a procedural coding background are familiar with traditional IF-THEN-ELSE constructs, possibly extended to SWITCH or CASE blocks that group a series of conditions. Depending on the language, those could either evalute the conditions until the first TRUE is found or until the first result breaks out. They might also be comfortable with…

The 8-Queens Puzzle in PL/SQL

Several months ago I used the classic 8-Queens chess puzzle as an illustration of using a little bit of math to determine intractable problems. In that article I teased a code solution for the puzzle but never followed up… until now. There are 92 distinct solutions, including rotations and reflection duplicates. The package’s demo procedure…

Connection Basics

Before you can dig into your data with SQL and PL/SQL you must first get connected to your database. While there are many architectures, tools, and drivers for connecting such as JDBC, ODBC, ROracle, node-oracledb, Oracle Provider for OLE DB, OCI, cx_Oracle, etc. They essentially all boil down to just two methods: either the Oracle…

Data type quirks in Oracle

In my previous article I stressed the importance of using correct data types. However in some cases you may be forced to choose one type when you really need to use two or more. Also, in some cases, you may think you’re using consistent data types but Oracle will insist on changing them anyway, using…

Using Correct Data Types

One of the fundamental aspects of many programming languages is the notion of data types. SQL and PL/SQL are no exception. This article is not an argument one way or the other on whether strongly-typed languages are superior to weakly-typed, or untyped languages. The argument is moot. If you’re working with an Oracle database, you…

Oracle and the Ends of Time

Contemplating the beginning and ending of time can be a complicated exercise in the mathematics of cosmology. Within the realms of an Oracle database though it’s much easier to get our heads around. By definition within Oracle, the beginning of time is -4712-01-01 00:00:00.The end of time is 9999-12-31 23:59:59.999999999. Thus all of time, past…

CQ Notification deregistration with java

The Oracle Change Notification or Continuous Query Notification functionality is a useful means of triggering events in remote sessions when a transaction commits. Sometimes though a client may set up a registration and then disappear leaving orphaned registrations. Using the DBMS_CHANGE_NOTIFICATION package (documented by it’s synonym DBMS_CQ_NOTIFICATION) these old entries can be removed easily with…

Licensing changes for Spatial and Advanced Analytics/Machine Learning

Today, Oracle announced both the “Oracle Spatial and Graph” as well as the “Oracle Machine Learning” (formerly Advanced Analytics) database options are now included at no additional cost for on-premises installations of Enterprise Edition and Enterprise Edition on Engineered Systems. These options provide a wealth of functionality that were formerly extra cost options. You can…