Fear No SQL
If you’re not into PL/SQL, substitute Jonathan Lewis, Tanel Poder, Maria Colgan, or any other technology great you might respect. Doesn’t even need to be Oracle related. The answer still, quite simply, is yes. First – it’s not a competition. If it were, then yes, I concede to the guy that literally wrote book on…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes Timeouts Modifying Data (this page) Modifying data in the LDAP hierarchy The DBMS_LDAP package allows you to write as well as read data from the directory. However, it does not provide an API for manipulating the schemas within the tree. That is, you…
Today I was asked about a query from an Audit Vault job that was failing. Unfortunately, the job didn’t report any useful error messages to explain the failure. After some digging the problem was narrowed down to a query pulling user account information. In particular, a calculated column was proving both erroneous as well as…
how-oracle-stores-passwords Just minor updates. 18c supports the same hashes as 12cR2 and doesn’t add any new ones. 18c also uses the same sqlnet.ora parameters and values as 12cR2. Updated links to 18c documentation and added note that 18c is the same as 12.1.0.2 and 12cR2.
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes Timeouts (this page) Modifying Data Timeouts LDAP servers are designed to be fast; but it is still possible for an action to take a long time. A deep sub-tree search with an open search criteria could require from seconds to even minutes to…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes (this page) Timeouts Modifying Data Searching Hierarchical Data with Attributes Returning attributes with search results The examples in the prior chapter use the “1.1” code to return no attributes. In this example we’ll return two of the attributes associated with an entry. Instead…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data (this page) Browsing Attributes Timeouts Modifying Data Searching Hierarchical Data Once you’ve established a connection, the next thing you’ll probably want to do is start searching for records. Either to pull the data back to a client, or to modify one or more records or…
Last week the 18c Linux on-premises software was released for download. So this weekend I installed it on my test server and gave it a whirl. The software installs themselves went smoothly and then it was time to do an upgrade. Below I’ve capture the steps and output of an upgrade by unplug/plug from older…
woohoo finally! http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html
I was working on a report from our RMAN catalog and found I was having trouble reconciling the data I found in RC_BACKUP_SET with what I was expecting to see. Furthermore I couldn’t find anything in the Reference Guide to adequately explain what I was seeing. In particular I’ll reference the descriptions of the BACKUP_TYPE…
The trimming functions in Oracle frequently are used by many developers; but not all know the full set of options available and how they compare to each other. Sometimes I need to remind myself so this post is as much for me as for others. I hope you find it helpful. The LTRIM and RTRIM…
In Toad version 13.0, after upgrading to an 18c client, the dropdown list on the LDAP tab of the connection window will be empty. The reason is Toad is looking for the 12c ldap client dll in the 18c $ORACLE_HOME/bin directory. Connections will still work; but if you want the list and search functionality you…
Table of Contents Introduction Establishing a Connection (this page) Searching Hierarchical Data Browsing Attributes Timeouts Modifying Data Establishing a Connection The DBMS_LDAP.INIT function is used to connect to an LDAP server. On success, the function returns a session handle of DBMS_LDAP.SESSION type. FUNCTION init (hostname IN VARCHAR2, portnum IN PLS_INTEGER ) RETURN SESSION; DBMS_LDAP.PORT is…
Introduction In 9iR1 (9.0.1) Oracle released the first version of DBMS_LDAP. Between then and 18c the package has received a few enhancements including support for BLOBs, Basic Encoding Rules (BER,) and NLS character set conversions. The documentation for the package has alwasy been somewhat terse, especially if you’re unfamiliar with LDAP structures and usage. I’ve…
Finally getting around to posting these, sorry about the delay. My powerpoint slides can be downloaded here. Some of the slides included simplified queries so the text would fit on a single screen and remain legible. To find who is using temp, a more functionally robust query can be found here: /2018/03/08/who-is-using-temp-and-what-are-they-doing/ Similarly, the who…
This past week I was looking through the Oracle documentation on a task of pedantry; looking up the exact description of Oracle’s outer join syntax. While digging through the syntax diagrams and explanations of the SQL Reference I came across the phrase “partitioned outer join”. It’s an old feature now, having been around since 2003…
Several years ago I made a little math joke about a process that performed millions of look-ups and took forever to complete. That joke, just a short one-liner: “Fast Times Many Equals Slow” has since become a bit of a mantra. The problem can be particularly pernicious in that a problematic set of code might…
I got back from speaking at Collaborate 18 a week ago. My topic was performance diagnostics. The recurring theme of my talk is for developers to leave themselves some bread crumbs to mark their trail. The Oracle database has wonderful diagnostic tools like ASH/AWR, SQL Monitor, tracing, and the hierarchical profiler; but as good as…
I tried the following query on 18c via Oracle’s LiveSQL site and the results don’t correspond to the documented functionality; or, at least, not my reading of the functionality. select to_utc_timestamp_tz(‘2018-04-27’) t from dual union all select to_utc_timestamp_tz(‘2018-04-27T17:40:25+00:00’) from dual union all select to_utc_timestamp_tz(‘2018-04-27T17:40:25Z’) from dual union all select to_utc_timestamp_tz(‘20180427T174025Z’) from dual union all select…
I presented this topic at the Collaborate 2018 conference. My slides can be found here. A couple of the slides included simplified queries so the text would fit on a single screen and remain legible. To find who is using temp, a more functionally robust query can be found here: /2018/03/08/who-is-using-temp-and-what-are-they-doing/ Similarly, the who is…
This is another article inspired by a recent request. That is, given a PDF file with tabular data, could I extract the data into a more human-usable format? Yes, it is possible but it requires multiple steps and does come with some caveats. The first step is converting the PDF into a format that is…
Recently I was asked to look at a problem where a simple query refused to use an index. The table had several million rows in it with a single index on one column populated with 3 values (in my sample the values are A, B, and C.) CREATE TABLE null_test ( col1 NUMBER, col2 NUMBER,…
Part 1Part 2 I’ve continued to enjoy this little puzzle and trying to find different ways of solving it. I’ve used the CONNECT BY clause to unpivot words into lists of letters but in this article I’ll show a more direct recursion on the word and then wrap up the series with the smallest and…
Part 1Part 3 In my last post I solved the task of finding words that can be typed with alternating hands by using collections, match recognize, and analytics. I closed with the comment that I was confident MODEL could be used to solve it too; but at the time I wasn’t sure how to do…
Part 2Part 3 Yesterday, a coworker asked me a question just out of curiosity: “How would you determine which words can be typed with alternating hands?” He then added an additional criteria that he’d like to find the words that ended on the left hand (presumably, so you could press space or enter with the…
Previously, I’ve shown how to use a user-defined aggregate to process numeric values; but they can be used for text as well. Recently I was tasked with importing some denormalized text data and aggregating into a consistent form. The source data consisted of many rows holding identifiers, multiple comma-separated values (CSV,) and several other fields.…
A common occurrence in sql is to aggregate text values into a larger, delimited value. It can also occur that you have a delimited value and you want treat it as a collection to pull data out of it process each of the sub-values. Oracle has a built-in function LISTAGG to aggregate text values into…
I’ve been asked this question in various forms and forums over the years and the askers are often surprised when I tell them there is more than one way to answer that question. Furthermore, each method yields a different but not necessarily wrong answer! First, let’s take a simple, maybe even obvious method: SELECT avg_row_len…
TEMP can be a difficult resource to manage in mixed workload environments. You might have sufficient space to run a few big sorts or aggregates; or maybe you have enough space to run lots of concurrent sessions each of which consumes a little bit. But then you get the occasional overlap and there just isn’t…
A fairly common question I get as a DBA is tracking down which sessions are blocking others. Fortunately the v$session view has the blocking session (as of 10.1) and instance of that session (as of 10.2) included. Since a blocker could itself be blocked by yet another session, I use a hierarchical query to determine…
https://docs.oracle.com/en/database/oracle/oracle-database/18/index.html Definitely interested in looking into the json functionality https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/changes.html#GUID-8C9B00DD-45D8-499B-919C-8632E034B664 and digging in more with analytic views https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Changes-in-This-Release-for-Oracle-Database-SQL-Language-Reference.html#GUID-9D1C204A-91C3-4B8D-A882-374F78118862
The servers of your Oracle databases will be of either Big or Little endianness. That is, the ordering of bytes within values. For the most part, the Oracle APIs insulate your code from ever needing to know which method your system is using. However, there are cases, such as transportable tablespaces or working with binary…
In my previous post I showed how to use the MAP member functions to compare two objects of the same class. The MAP functions are useful when an object can be meaningfully represented and compared by a single value, especially if that value might provide functionality or meaning itself (such as the triangle area mapping…
If you have 2 instances of an object, A and B, you might want to compare them: is A > B or B > A? By default, two instances can’t be compared for greater or lesser value, only for equality. Where equality is determined by checking each attribute of each instance in order and comparing…
When an object type is instantiated (i.e. assigned a non-null value) it will execute initialization code. This initialization code is called a CONSTRUCTOR function. Every object type includes an implicit constructor which simply assigns values to the object attributes according to the values provided in the object assignment. For example: CREATE OR REPLACE TYPE simpleobject…
A lot of developers see Object Types as confusing, esoteric, or simply unrelated to their SQL work. To be fair, some objects can be quite complex and some of the data cartridge (ODCI) features will never find a home in some developer’s applications. But, that still shouldn’t preclude an examination of some of the fundamentals…
The MERGE command in Oracle is a great tool for modifying data in a table; efficiently solving the old problem of insert collisions and updates of non-existent rows. Most of the time we use it becaue it “just works.” But, what really goes on when you issue a merge? In particular, which triggers fire? How…
One of the interesting features of Oracle is if an entry in an index would only have nulls in it, then that entry isn’t created. A simple test can verify this assertion. SQL> CREATE TABLE null_test 2 ( 3 id INTEGER NOT NULL, 4 dummy_string VARCHAR2(10) 5 ); Table created. SQL> INSERT INTO null_test(id, dummy_string)…
I ran into an interesting bug in some code today due to a math error which caused CHR() to be called with non-integer input values. According to the Oracle documentation, this is perfectly acceptable; but it doesn’t make much sense. Unfortunately, the documentation doesn’t specify what should happen for such inputs. This ambiguity is the…
When I wrote my article on Oracle passwords I included a small, limited implementation of the PBKDF2 algorithm in order to illustrate the 12c hashing methodology. Here I’m publishing a fuller implementation with parameters for determining the derived key length, number of hashing iterations, and a choice of hashing methods (SH1, SH256, SH384, or SH512.) …
Added section on 11g and 12c SQLNET.ORA parameters. WordPress article here. A pdf of this article can be downloaded from my dropbox here.
Recently I was asked how to bind an R variable to a SQL statement executed with ROracle. In this case it wasn’t a simple “select * from table where x = :b1 and y = :b2.” You can do those quite easily with a data.frame with only one row in it using dbGetQuery(connection, sqlstatement, data.frame)…
In my previous two posts I showed how to setup a schema for the TPC-H tables and test data. A related test system called the Star Schema Benchmark (SSB) from Pat O’Neil, Betty O’Neil, and Xuedong Chen at the University of Massachusetts at Boston alters the TPC-H structures to create a warehousing data model. The…
In my previous post I showed how to set up data loads for the TPC-H test data. All of the steps provided should work regardless of the data volume. However, for large data sets you may want to parallelize the data generation and data import in order to speed up the process. The user, directory,…
While Oracle has their own SCOTT, SH, OE, HR, etc. sample schemas, it’s often useful to have the ability to scale data volumes for different experiments. The TPC-H schema and sample data sets provide a convenient means of doing so. They are especially helpful in scenarios such as this, blogging, where readers may have a…
12.2.0.1 has been released for on premises installation. It’s only available for Linux and Solaris so far, but that’s good enough to get some test installations started. http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Recently I encountered an interesting situation where a job has been running successfully for months and then one day it suddenly started failing every time with an ORA-06502: PL/SQL: numeric or value error. The same error can also manifest itself in bulk operations with ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind. The…
The ASH charts in OEM are great utilities for getting a quick summary of your system’s activity. However, these results can be misleading because of how the data is represented on screen. First, ASH is data is collected by sampling so it’s not a complete picture of everything that runs. Another thing to consider is…
Oracle offers several methods for finding the name of a database. More significantly, 12c introduces new functionality which may change the expected value from some of the old methods due to the multi-tenant feature. Here are 11 methods for finding the name of a database. SELECT ‘ora_database_name’ method, ora_database_name VALUE FROM DUAL UNION ALL SELECT…
Several years ago I wrote a small summary of the Oracle password hashing and storage for versions up to 11g. Today I’ve completed my update of that article up to 12.1.0.2, including code to mimic generation of passwords given the appropriate salts. The initial publication is in PDF format, I may convert and reformat it…