Fear No SQL
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)…
Oracle gave me an unexpected present for Christmas this year by naming me an Oracle ACE! I’m truly honored to be included in the ranks of so many I’ve read and learned from. Of course I’m also extremely happy to know my efforts to return the favor and share my knowledge and experience has been…
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
What’s it take to be a good developer? Brains? Of course you have to be able to think. Logic? Sure, you need to have logical constructions. Detailed? Maybe, you do need to pick out the intricacies of requirements. Thorough? Yeah, I’ll give you that. The cause of all bugs is failing to cover some scenario.…
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…
Like everyone else, I make mistakes. While the results can sometimes be unfortunate, it’s also a truth that shouldn’t be ignored. A recurring problem though is that as a designated “expert” sometimes people don’t bother to test what I’ve given them. They just roll with it and then are surprised when their production installation goes…
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…
I’m flying out tomorrow for Collaborate 16. Looking forward to another great conference. I’m presenting again this year. I’ll be speaking on Tuesday, at 2:15 “Why Developers Need to Think like DBAs, Why DBAs Need to Think like Developers” Session 1355 in Jasmine C
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…
I’ve used this tool for a wide variety of other parsing projects. One of the interesting tuning techniques I used was to pull the clob apart into 32K varchar2 chunks. It is possible to split the clob directly using the DBMS_LOB package or through the overloaded SQL functions; but clobs are expensive objects. Varchar2 variables…
A little while ago Oracle announced the winners of the Oracle Database Developer Choice Awards and I was a winner in both of categories I was nominated, SQL and PL/SQL I was surprised and overjoyed when I was notified that I had not only been nominated; but named a finalist. I’m truly humbled by the…
If you haven’t voted yet, please do so soon! Voting ends 3pm US/Pacific time. I’ve been nominated in the SQL and PL/SQL categories https://community.oracle.com/community/database/awards/sql-voting https://community.oracle.com/community/database/awards/plsql-voting
Yesterday Kim Berg Hansen posted a couple entries to his blog about scraping and parsing the Oracle Developer Choice Awards voting pages and building summaries of the results. I’ve been doing something similar since the voting began but took a slightly different approach. I do like the idea of storing them in a table to…
Yesterday I was happily surprised by an email from Steven Feuerstein letting me know I hadn’t responded to my finalist announcement. Well, the reason for that was I never saw it! I’m glad he let me know though, what an honor. I am a finalist in both the SQL and PL/SQL categories. If you’ve enjoyed…
I live in Ohio which is fairly stable as far as earthquakes go; but I have coworkers and friends that live in other, more quake-prone areas of the country. So, sometimes I like to look up what’s going on in those areas. Naturally, I decided to see how I could use Oracle features to assist…
I let this project sit on the back burners for too many months but tonight finally got around to cleaning it up and getting it out for people to use. Do note, the authentication is a little different and hence, not compatible with prior versions. While I don’t like breaking compatibility, it was a necessary…
If A < B and B < C then A < C Right? We all know the transitive rule… all of us except the Oracle optimizer. Here’s a seasonal example to illustrate how the Optimizer changes it’s methods based on seemingly trivial options. Let’s say I bake 10 types of cookies and then I want…
I finished “Oracle PL/SQL Performance Tuning Tips & Techniques” by Michael Rosenblum and Paul Dorsey today. I recommend it as a lesson, a reference, and an inspiration for things to try. Steven Feuerstein still gets the nod as the “go to” for first learning how to use pl/sql; but this book has great examples for…
I’m in the process of updating my OID package from last year to version 2.0. The new version will include a significant change in API but will also include more functionality, in particular, support for TNS aliases. I’m also changing the way exceptions are raised as well as improving the efficiency of searches. Along the…
11gR2 introduced the DBMS_PARALLEL_EXECUTE package. The basic idea behind it is you divide some large task into smaller chunks and then execute all the chunks in parallel. The API and the examples in the Oracle Documentation might lead one to believe the package can only be used to split large SQL statements into parallel chunks.…
I have an Oracle Express Edition (XE) database running on almost every pc in my home. It’s not so numerous that I can’t manage them by manipulation of TNSNAMES.ORA; but I always thought it would be nice to manage them centrally via LDAP service name lookup. Unfortunately Oracle Internet Directory (OID) licensing is included with…
A not-uncommon request I hear is to explain when the contents of a view are updated. Sometimes this will be to explain the difference between a “view” and a “materialized view.” I like analogies as a tool to help describe what I’m trying to say. I wear glasses. Querying through a view is like looking…
One of the new features of 12c is the ability to create varchar2 columns up to 32k in size. This feature isn’t enabled by default. You have to put the db into upgrade mode, set max_string_size to EXTENDED and restart the db. However, once you do that, then the big strings are just like normal…
I’m disappointed with the container licensing. Seems odd to publish with a “consume more resources than needed” option turned on. Of course, people will want to buy the containers to take full advantage of their hardware. So I guess I can understand the sales model but from a branding perspective publishing with the brakes locked…
Looking forward to another great conference. I’ll presenting again this year, this time doing two sessions: What an Oracle Developer Ought to Know Why Developers Need to Think Like DBAs, Why DBAs Need to Think Like Developers
Oracle supports several methods for constructing Interval types including the NUMTODSINTERVAL and NUMTOYMINTERVAL functions. As their names imply, these simply convert a number of some units (day,hour,minute, second or month,year respectively) into an interval type. There is however no built-in method for reversing the process. The two functions below fill in this hole. I hope…
Last week was the 5 year anniversary of answering my first question on Experts Exchange and on Monday of this week I answered my 6000th question. With coincidental milestones like that it seems like a good time to reflect on what I’ve accomplished since I started participating. In that time I’ve also published 13 articles,…
First day was unintentionally APEX heavy for me. Not that that’s a bad thing, just sort of surprising to me in hindsight. Saw some really neat apps and ideas with HTML5 and dynamic actions. Also saw some new tools I’ll be checking out to make my own development more productive like aptana studio and apexlib.…
Just found this data yesterday, not sure when or how it got into the real table. Ironically the column that is corrupt is the mod_date which could have told me when. Note I can only display 2 digits of the year. If I try using yyyy format I’ll get an ORA-01877. I think it’s interesting…
Using the algorithm discovered by David Bailey, Peter Borwein, and Simon Plouffe, you can easily generate values of pi with a simple recursive query… SQL> SELECT d, to_char(bbp, rpad(‘0.’,55, ‘9’)) bbp, 2 TO_CHAR( 3 SUM(bbp) OVER (ORDER BY d), 4 ‘9.’ || RPAD(‘9’, d – 1, ‘9’) 5 ) pi …
According to recent press release http://www.oracle.com/us/corporate/press/1563775 Yes- you can install 11gR2 on Oracle’s latest release of Linux. However, I was not successful in my attempts. Upon further research it seems the certification of db and os is only for 11.2.0.3 which is only available through download from Oracle support. That’s fine for work where we…
I don’t normally write a lot of java stored procedures. They simply aren’t necessary most of the time; but today somebody asked me a question that just seemed a perfect fit. They wanted to know how to generate a SHA-256 hash for a given string. For most hashing tasks I’d recommend Oracle’s built-in package DBMS_CRYPTO;…
I’ve known about Heterogeneous Services and Gateways for a while but only recently got around to playing with them. Like others, I chose the ODBC Gateway because the database license includes the ODBC usage, unlike the rest of the Gateways that are each licensed separately. First, create an ODBC DSN for whatever database and platform…
Welcome to my blog about Oracle database development. I’m also interested in math and frequently write pl/sql and sql snippets to solve various math problems. For example, here’s an article I wrote about solving a combinatorics problem found in dart games like 301,501,701, etc. Fun with Oracle SQL – Solving Checkouts in a Game of…