sql - Laur's blog

Sign in Subscribe

sql

A collection of 12 posts

Export SQL Developer Connections

Export SQL Developer Connections

When I joined in, I found myself suddenly needing access to a bunch of Oracle databases, so I thought initially to get all accounts loaded one by one in SQL Developer. That's an option... A colleague showed me that you can actually back up and restore your DB

Build Update Statements with Oracle SQL Developer and Python

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

Delete Dependent Entries of a Table in Oracle PL/SQL

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

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:

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

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

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

Add Primary Key Column to PostgreSQL Table

Add Primary Key Column to PostgreSQL Table

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

Postgres: Show Schema Sizes in a Database

Postgres: Show Schema Sizes in a Database

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: SELECT schema_name, pg_size_pretty(sum(table_size)::bigint) as

PostgreSQL: Call a Stored Procedure for Every Row

PostgreSQL: Call a Stored Procedure for Every Row

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 SELECT your_procedure_name(parameters); Then, usually immediately the second question pops up: I wrote a procedure which I need to

UPSERT (update or insert) in Oracle with Python

UPSERT (update or insert) in Oracle with Python

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: 1. ID - a 20-character string identifier with PK properties (unique) 2. ARTICLE-

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

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

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

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

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

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