Suppose your organization wants to enforce a security policy on database password strength. The DBA’s have implemented a password strength verification function in PLSQL such as the oracle supplied ora12c_strong_verify_function in the DEFAULT profile of the database. There seems no way to get around it at first:
Database account u4 is created:
U4 logs in and tries to keep it simple, i.e. the password:
That password verification function got in (more...)
When you have to create stored procedures which need to be called from an Oracle Service Bus, the most covenient way (at least for the one creating the mapping between the incoming message and the stored procedure) is to use Object Types.
The "downside" is that you might need lots of Object Types and Nested Table Types to get the right structure.
If you are unfamiliair with this technique, there are some links at the (more...)
So, after months of filling out IT request forms, multiple unanswered emails to a council of learn-ed database elders and then finally outright begging, you got a brand-spankin’ new user account giving you access to that important database, time to write some queries!
Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a
CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort.
I gave my students this
ORDER BY clause as an example:
CASE WHEN filter = 'Debit' THEN 1 WHEN filter = 'Credit' THEN 2 (more...)
The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. (more...)
The other day I was studying a SQL statement that was performing poorly, when I noticed a peculiar syntax that I have not come across before :
SELECT COUNT(*) FROM customer WHERE annual_spend > ALL ( SELECT spend_threshold FROM promotion WHERE promo_name = 'Summer 2015' );
The only time I’d ever come across the keyword ALL in SQL was in UNION ALL. However, a quick check of the manual led me to the Group Comparison (more...)
I think this is cool as it gives you the ability to use SQL on an HBase table.
To test this, let's say you login to HBase and you create an HBase table like (more...)
Here is a post about another project in the Big Data world, like Apache Hive from my previous post, enables you to do SQL on Big Data. It is called Apache Phoenix.
In this post I will share my experience with an Apache Hadoop component called Hive which enables you to do SQL on an Apache Hadoop Big Data cluster.
Being a great fun of SQL and relational databases, this was my opportunity to set up a mechanism where I could transfer some (a lot) data from a relational database into Hadoop and query it with SQL. Not a very difficult thing to do these days, actually (more...)
DEFINElists the conditions a row may meet; it doesn't always work the way you expect, especially if you use aggregates in the condition.
This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite, as suggested on some web sites. You can determine whether or not Java is installed by running the following command:
Mac-Pro-3:~ username$ java -version No Java runtime present, requesting install.
You must accept the Java license to install Java 8 on the (more...)
Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.
First of all let's start with the version of the database that I tested this on. Yes, I know (more...)