oracle - Laur's blog

Build a Django Docker Image with Cx-Oracle

TL;DR: Don't use alpine images. There's a glibc issue. We added a cx-oracle dependency to one of our django apps. As the previous image was just an alpine basic (python) image we needed to put in place a process to build the new image. Initially,

developer

Build Update Statements with Oracle SQL Developer and Python

By default, SQL Developer can only export INSERT statements. This is quite unfortunate when one needs to build UPDATE statements because their syntax is different. I've tried to use regexp to convert the statements but I didn't find it not straight forward at all. So, I

columns

Match String on Multiple Columns

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

dbms

Display a Function's Errors in Oracle

TL;DR: Use the power of DBMS_OUTPUT.PUT_LINE, Luke! I've built a function along the lines of: CREATE OR REPLACE FUNCTION HAS_PANEL_FLAG_OK( P_USER_ID in USERS.USER_ID%TYPE, P_PANEL_ID in PANELS.PANEL_ID%TYPE, ) RETURN integer IS lv_count

call

Call an Oracle Function from Spring with CallableStatement

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: int value = jdbcOperations.execute( new CallableStatementCreator() { // ... }, new CallableStatementCallback<Integer>() { // ... } ); The CallableStatementCreator sets up the statement and the CallableStatementCallback

function

Call an Oracle Function from Spring

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: SELECT panel_id, HAS_PANEL_FLAG_OK('17584', panel_id) AS flag FROM panels WHERE

batch

Delete Dependent Entries of a Table in Oracle PL/SQL

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

column

List Columns in a ResultSet

In order to list all columns in a JDBC ResultSet (e.g. for debug purposes), you need to iterate through its metadata: // Get the metadata ResultSetMetaData md = rs.getMetaData(); // For each column, list its name for (int i=0;i < md.getColumnCount(); i++) logger.debug("Column :" + i

db

Select Rows Where Value from One Column Matches Other Column

TL;DR: Use a JOIN. I've got a table representing a tree, using two columns: CREATE TABLE "EXAMPLE" ( "PROP_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, "PARENT_PROP_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE, /* * Data ... */ "CLASS_ID" VARCHAR2(20

all

Simulate UNION with BLOBs

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

developer

Move SQL Developer Data from the Roaming Profile in Windows

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:

clob

Loading XSDs into Oracle through Python

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)

fragment

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

An important note when loading XSDs in Oracle is that DBMSXMLSCHEMA.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

cx_oracle

Drop an Oracle-Loaded XSD in Python

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: dbms_xmlschema.deleteSchema(

cx_oracle

Loading Dependent XSDs in Oracle through Python

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

oracle

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

Table of Contents 1. Part 1 - Prerequisites 2. Part 2 - Create a table and perform a SELECT 3. Part 3 - User Python to insert data 4. Part 4 - references References for the series * Using XQuery with Oracle XML DB * XMLTYPE * Oracle XML DB Developer's

cx_oracle

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

TL;DR: Here I present how to insert data with python in the table created at the previous step. Table of Contents 1. Part 1 - Prerequisites 2. Part 2 - Create a table and perform a SELECT 3. Part 3 - User Python to insert data 4. Part 4

oracle

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

TL;DR: Here I present a SQL example from creating the table to performing a SELECT. Table of Contents 1. Part 1 - Prerequisites 2. Part 2 - Create a table and perform a SELECT 3. Part 3 - User Python to insert data 4. Part 4 - references Table

oracle

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

TL;DR: Here I present an introduction of the problem and some prerequisites to check if you have XMLFIELD available. Table of Contents 1. Part 1 - Prerequisites 2. Part 2 - Create a table and perform a SELECT 3. Part 3 - User Python to insert data 4. Part