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

Use Oracle’s RegisterSchema with Large Schemas (from Python)

Posted by in Software

An important note when loading XSDs in Oracle is that DBMS_XMLSCHEMA.registerSchema() requires the actual content of the xsd is loaded in a string. A side effect is that strings in Oracle have limits and schemas can exceed those limits. Therefore, such strings must be fragmented. In this case, we need to employ PL/SQL. A processed fragment could look something like this:

Now, all you need to do is populate the fragments (where I’ve placed 'long-string-fragment') with the actual content. A rule of thumb I’ve got is to have a…read more

Drop an Oracle-Loaded XSD in Python

Posted by in Software

A loaded schema can be dropped if: No other schema depends on it No data using the schema is loaded I think you may force unloading schemas even if there is data loaded but I haven’t tried that. The code to drop a schema is:

You can use it if e.g. schema is updated or we don’t need it anymore. The full code in Python is:

Several notes: The order of removal is, of course, the reversed order of insertion. This way, complex XSDs are removed firse leaving…read more

Loading Dependent XSDs in Oracle through Python

Posted by in Software

One essential feature is that schemas can refer to other schemas. Oracle supports it via the schemaURL parameter. In other words, the url of the referred schema must be the same as the schemaURL parameter used to register the referred schema. One consequence of this feature is the necessity to provide a load order of schemas. In my case, The order would look like this:

The loader segment would be something similar to:

This is all trivial, except the INSERT_STR. A simple sample would be:

Note: Please…read more

Memory dump: Work with XMLField in Oracle (part 4)

Posted by in Software

Table of Contents Part 1 – Prerequisites Part 2 – Create a table and perform a SELECT Part 3 – User Python to insert data Part 4 – references References for the series Using XQuery with Oracle XML DB XMLTYPE Oracle XML DB Developer’s Guide ORA-01461: can bind a LONG value only for insert into a LONG column-Occurs when querying The Best Way to shred XML data into SQL Server database columns Mastering Oracle+Python, Part 4: Transactions and Large Objects XML shredding How to use xmltable in oracle? Oracle: If…read more