Laur IVAN

Match String on Multiple Columns

Posted by in Software

TL;DR: Use concatenation, Luke! I had to search a name for an user in a table containing the first and last names in separate columns. After a while I got tired on looking up the first_name and last_name columns one at a time and I decided to try and search both. The result is something like:

I have to do the LOWER() call because the names are provided by user input. At first I’ve been worried about performance, but since this is not a recurrent query, it’s acceptable (0.25s…read more

Display a Function’s Errors in Oracle

Posted by in Software, Tutorial

TL;DR: Use the power of DBMS_OUTPUT.PUT_LINE, Luke! I’ve built a function along the lines of:

When using the function, I got some 0 results and I wanted to know why. The solution I got was using DBMS_OUTPUT.PUT_LINE:

Now, all you have to do is enable DBMS_OUTPUT in your environment and you’ll see the exceptions. NOTE: This is useful while building and tuning the function. IMHO one should disable the PUT_LINE statements in production. HTH, A little experiment: If you find this post and ad below useful, please check…read more

Call an Oracle Function from Spring with CallableStatement

Posted by in Software, Tutorial

Following the previous post, I wondered if there’s a more elegant way to call a function. It turns out there is and it’s based on CallableStatement:

The CallableStatementCreator sets up the statement and the CallableStatementCallback executes the statement and returns the value up to the execute() call. Having a function:

We can build a query string like:

The CallableStatementCreator instance is then:

This states the first parameter is a result, the second and 3rd parameter are function parameters. NOTE: Be careful because you can specify indices…read more

Call an Oracle Function from Spring

Posted by in Software, Tutorial

The easiest way to call a function from Spring is using SELECT. For example, having a function HAS_PANEL_FLAG_OK with two parameters and returning an integer, the function call looks like:

This will return all flags for the panels matching the condition. Java Call The query string can be something like:

and the actual query will be:

The problem I’ve encountered is the ability to specify an argument which is not part of the query. Something like this:

I’ll investigate this later… HTH, A little experiment: If…read more

Delete Dependent Entries of a Table in Oracle PL/SQL

Posted by in Software, Tutorial

The other day I had a task to clean up a database which was populated with a list of records. Unfortunately, my main table is linked to a host of other tables via foreign key dependencies and, to make things worse, no CASCADE constraints are specified. The process I want to is as follows: Scan main table First, we need to find the records in the main table we want removed. This is a simple SELECT statement:

In my case, the statement is:

(the 80, and 122 are…read more

List Columns in a ResultSet

Posted by in Software, Tutorial

In order to list all columns in a JDBC ResultSet (e.g. for debug purposes), you need to iterate through its metadata:

Note: Column numbers start from 1, not 0 (hence the md.getColumnName(i + 1) above). I usually wrap this in a if(logger.isTraceEnabled()) so it doesn’t get triggered all the time. HTH, A little experiment: If you find this post and ad below useful, please check the ad out :-)

Select Rows Where Value from One Column Matches Other Column

Posted by in Software, Tutorial

TL;DR: Use a JOIN. I’ve got a table representing a tree, using two columns:

and I want to select all children for parents whose CLASS_ID have a certain value (e.g. abcd). The way I could do it was via a JOIN statement:

This will: Perform a join where child.parent_prop_id = parent.prop_id Filter only parent.class_id = ‘abcd’ Simple. Bonus This will work with other databases as well :) HTH, A little experiment: If you find this post and ad below useful, please check the ad out :-)

Simulate UNION with BLOBs

Posted by in Software

TL;DR: If you have BLOBs, you need to use UNION ALL. If you use UNION ALL, you need to partition off a criterion and select the first row. The other day I had to figure out how UNION works in Oracle, because we’re retrofitting an application with extra features. Assuming I have 2 tables(table_1 and table_2) with identical structures:

I need to perform an UNION to access both tables’fields:

Unfortunately, I’ve found out that you can’t just do UNION when you have BLOB data because a simple UNION…read more

Move SQL Developer Data from the Roaming Profile in Windows

Posted by in IT

TL;DR:: Set up IDE_USER_DIR. I have to keep my roaming profile on a diet and this means moving a bunch of default directories around. The latest victim is SQL Developer According to this post, the environment variable to be changed is IDE_USER_DIR. From the post: According to documentation, the IDE_USER_DIR is picked up automatically if set, so there should be no need for an additional conf file set statement in any recent SQL Developer release. If this doesn’t work, you may try editing config files around as described in the…read more

Loading XSDs into Oracle through Python

Posted by in Software

Following the previous incursions in Oracle and their own documentation, we decided it was useful to attach schemas to the inserted data, just because only XML with schemas attached can be indexed. When you have some million entries, indexing seems like a good idea (once you decide what to index). In Oracle, you have DBMS_XMLSCHEMA.registerSchema() to do just that. It has a bunch of parameters which are thoroughly described in their documentation. Suffices to say that you can choose to generate tables, objects or java beans off the loaded schemas….read more