Oracle CURRENT_TIMESTAMP Function with Examples

The CURRENT_TIMESTAMP function is one of the many time and date-related functions in Oracle. Learn how to use it and see some examples in this article. Purpose of the Oracle CURRENT_TIMESTAMP Function The Oracle CURRENT_TIMESTAMP function will display the current date and time. It’s displayed in the session time zone, which is the timezone of […]

Oracle CREATE TABLE Syntax and Examples – The Complete Guide

Creating database tables in Oracle is one of the most common tasks an Oracle developer or Oracle DBA does. Learn how to create tables, what the syntax is, and see some examples in this article. What Is The Create Table Command Used For? The CREATE TABLE command is used to create a database table. It […]

Accessing STATUS columns efficiently

A frequently reoccuring design problem with relational databases is the issue locating unprocessed rows in a large table, so we know which rows of data are still yet to be processed.

The problem with a STATUS column is that it generally has low cardinality; there are probably only a handful of distinct values [(C)omplete, (E)rror, (U)nprocessed or something like that]. Most records will be (C)omplete. This makes STATUS a poor candidate for standard B-Tree indexation. (more...)

Joining to a pipelined table function and “left correlation”

Oracle 11.2.0.4

A pipelined table function may be called from regular SQL using the TABLE collection expression, e.g.

SELECT *
FROM   TABLE(my_pipelined_function('ABC','DEF'));

where ‘ABC’ and ‘DEF’ are the inputs to the function.

What if you want to call the function repeatedly for several sets of inputs, e.g. testing the function for a variety of values? If those inputs are stored in a table somewhere, it ought to be as easy (more...)

Oracle BITAND Function with Examples

In this article, I’ll explain how to use the Oracle BITAND function and show you some examples. Purpose of the Oracle BITAND Function The Oracle BITAND function is used to perform what’s called a standard bitwise AND operation. It’s used to compare two numbers and outputs a third number. I’ll explain what a bitwise AND […]

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

The best idea since 1992: Putting the C into ACID (We need your vote)

Oracle Rdb (only available for the VMS platform) supports SQL-92 assertions (http://community.hpe.com/hpeb/attachments/hpeb/itrc-149/22979/1/15667.doc) so why not Oracle Database? Let’s put the “C” into “ACID.”(read more)

Debug PL/SQL Web Pages

What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.

Here’s a sample web page that fails to run successfully …

1
2
3
4
5
6
7
8
9
10
11
 (more...)

Subtleties – Part 2 (Nested Tables and Varrays)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer
/

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
 (more...)

SQL Developer & PL/SQL

While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.

SQL Developer DBMS_OUTPUT Configuration

SQLDeveloper1

  1. You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.

SQLDeveloper1

  1. You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)

Subtleties – Part 1 (SQL and PL/SQL)

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table  (more...)

Oracle AVG Function with Examples

The Oracle AVG function is a common function in Oracle SQL. I’ll explain how to use it in this article and show you some examples. Purpose of the Oracle AVG Function The AVG function calculates the average of a series of values that you provide to it. Most of the time, this will be a […]

Using a Sparse Index

My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set?

You need to create three object types for this example. They are:

  • a list of numbers
  • a record structure, declared as an object type without methods
  • a list of the record structure

These are the SQL commands to create the required data (more...)

About Oracle: DBA_DEPENDENCY_COLUMNS

A colleague asked if there was a way to do column level dependency tracking recently. He wanted to know for a given view, which tables and the columns on those tables, it was dependent upon, without, of course, reading through the code.

I was vaguely aware that since 11gR1 Oracle has been tracking fine grained (column) dependencies, but couldn’t find a way of seeing the details stored, until I found this interesting article from Rob (more...)

Greatly Improved SQL Index Performance for Oracle Berkeley Database, Version 6.2

A new whitepaper has been published, "Greatly Improved SQL Index Performance for Oracle Berkeley Database, Version 6.2". Learn how to increase the performance of SQL Queries using BDB. Go to: http://www.oracle.com/technetwork/database/database-technologies/berkeleydb/learnmore/index.html   ... [Read More]

SQL Pattern Matching Deep Dive – Part 3, greedy vs. reluctant quantifiers

Picture courtesy of Pixabay Welcome to the third post in this deep-dive series on SQL pattern matching using the MATCH_RECOGNIZE feature that is part of Database 12c.  In the first part of this series we looked at a wide range of topics including ensuring query consistency, how to...... [Read More]

ANSI SQL with Analytic Functions on Snowflake DB

Here is another installment of my Top 10 blog list of cool features of Snowflake Elastic Data Warehouse: At Snowflake, we believe that it should be easy to access, query, and derive insights from your data. To support that, we provide our users with the ability to query all their data using ANSI compliant SQL […]

Parse JSON Array in SQL and PL/SQL – turn to a Nested Table

Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON.

I recently was working on a Node.js application that exposed a REST API to HTTP consumers. The consumers could send POST requests with a (more...)

Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Who Rewrote My SQL?

There are several new features in Oracle 12c that are implemented under the hood by changing the SQL statement that we write to a different statement (e.g., by adding some hidden predicates).
In OUG Ireland 2016 I talked about two such features – In Database Archiving and Temporal Validity – as part of my “Write Less (Code) with More (Oracle12c New Features)” presentation. I usually talk about another such feature in this presentation (more...)