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

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

TIL you can mix ASC, DESC in ORDER BY (SQLite)

Posted by in Software

TL;DR: You can use ORDER BY column1 DESC, column2 ASC ... in your code. The other day I was trying to get a custom ordering in a list for an object: order decreasing by UNREAD MESSAGES count and then order ascending by DATE Initially, I tried a GROUP BY, but I could not get it to do the ordering properly. Then I thought of composite keys (multiple rows, like ones used in statistics), but then I realised it’d be overkill for a small app. Then, I found out that you can do per-column sorting in ORDER BY:

read more

Add Primary Key Column to PostgreSQL Table

Posted by in Software

The other day I got a question: How to add a numeric PK column to an already existent table? A quick look on the net showed that it is indeed possible to add an auto-incrementing PK to an already existing table (if you don’t have already a PK that is!). The code is quite simple too! The statement:

will add an autoincrementing column named id to my table_without_pk. HTH, PS: Thanks to a couple of colleagues of mine for asking the question and working together to finding the answer…read more

Postgres: Show Schema Sizes in a Database

Posted by in Software

While loading a large database from files to a Postgres schema, I found very useful to know how much space on disk it actually occupies (just in case I need to re-size the disk). I found this answer online:

Which results in a list like: HTH, PS: All credit goes to this StackOverflow answer! A little experiment: If you find this post and ad below useful, please check the ad out :-)

PostgreSQL: Call a Stored Procedure for Every Row

Posted by in Software

I guess that newbies in PostgreSQL like me have first question when they create a store procedure: How do I call it? The answer is simple; you just execute

. Then, usually immediately the second question pops up: I wrote a procedure which I need to call for each row in a table. How do I do that? The answer is, just as simple; you execute

. A Short Explanation Let’s assume a table:

and a procedure:

The procedure is designed to be called for each row…read more

UPSERT (update or insert) in Oracle with Python

Posted by in Software

One of the things I had to do is to build a script to upload data (e.g. the content of a list of files) into a database with a single table. My table has 3 columns: ID – a 20-character string identifier with PK properties (unique) ARTICLE– an XMLType element with the article details CITEDBY – an XMLType element with citations data The problem is that some of the data is already in the database and it may only need updating. Following this question, the selected strategy is to perform…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

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

Posted by in Software

TL;DR: Here I present a SQL example from creating the table to performing a SELECT. 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 Table and XMLTYPE Once we’ve seen that we have XMLTYPE available, we can proceed to create a simple table. The table structure My problem required for a table with the following structure: An identifier (key) – 20 character long string An article field (nullable) – XML…read more

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

Posted by in Software

TL;DR: Here I present an introduction of the problem and some prerequisites to check if you have XMLFIELD available. 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 Introduction Recently, I’ve got a request to import a bunch of XML files in a database (Oracle). After reading a lot on mapping XML on a relational DB (and considering NoSQL databases too), I was considering using a script to generate the…read more