Useful SQL macro for dates

05/09/24 13:23

Here some utilities to simplify the handling of dates…

More...

Mastermind solution

07/08/23 09:59
Everybody knows the Mastermind game, here is a solution in SQL supporting 4 or 5 pawns with 4, 5, 6, 7, 8, … colors.
You supply the problem definition as a JSON with the guesses you have attempted and their results:

size is the number of pawns (4,5), ncolors the number of colors (4 to 8 usually), the guesses is an array of your attempts,
the ids of the pawns and "p" (for placed correctly) and "n" for present but not placed correctly pawns.

More...

simple example to connect to ORACLE Cloud DB using the Wallet

28/04/23 13:50
package YOUR_PACKAGE;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Properties;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class TestDBConnect {
    final static String DB_URL = "jdbc:oracle:thin:/@DB_NAME_high?TNS_ADMIN=/etc/oracle/wallet";
    final static String DB_USER = "USERNAME";
    final static String DB_PASSWORD = "PASSWORD";

    public static void main(String args[]) throws SQLException {
        Properties info = new Properties();

       info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
       info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
       // set other properties at your taste
       info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");
       info.put(OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT, Boolean.FALSE);
       info.put(OracleConnection.CONNECTION_PROPERTY_FAN_ENABLED, Boolean.FALSE);

       OracleDataSource ods = new OracleDataSource();

       ods.setURL(DB_URL);
       ods.setConnectionProperties(info);

       try (OracleConnection connection = (OracleConnection) ods.getConnection()) {

              try (Statement statement = connection.createStatement()) {
                  try (ResultSet resultSet = statement.executeQuery("YOUR QUERY")) {

                      while (resultSet.next()) {
                         ...
                      }
                  }
               }
           }
       }
}

The Maven pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0
  <groupId>YOUR_GROUP
  <artifactId>YOUR_ARTIFACT
  <version>0.0.1-SNAPSHOT
  <name>Test JDBC Wallet

  <properties>
    <project.build.sourceEncoding>UTF-8
    <project.reporting.outputEncoding>UTF-8
    <jdk.version>11
    <java.version>11

    <ojdbc8.version>21.9.0.0

    <maven.compiler.source>11
    <maven.compiler.target>11
  </properties>

  <dependencies>
    <dependency>
      <groupId>com.oracle.database.jdbc
      <artifactId>ojdbc8
      <version>${ojdbc8.version}
    </dependency>
    <dependency>
      <groupId>com.oracle.database.jdbc
      <artifactId>ucp
      <version>${ojdbc8.version}
    </dependency>
    <dependency>
      <groupId>com.oracle.database.security
      <artifactId>oraclepki
      <version>${ojdbc8.version}
    </dependency>
    <dependency>
      <groupId>com.oracle.database.xml
      <artifactId>xdb
      <version>${ojdbc8.version}
    </dependency>
    <dependency>
      <groupId>com.oracle.database.xml
      <artifactId>xmlparserv2
      <version>${ojdbc8.version}
    </dependency>

    <dependency>
      <groupId>com.oracle.database.security
      <artifactId>osdt_core
      <version>${ojdbc8.version}
    </dependency>
    <dependency>
      <groupId>com.oracle.database.security
      <artifactId>osdt_cert
      <version>${ojdbc8.version}
    </dependency>
  </dependencies>
</project>

@org.hibernate.annotations.RowId annotation and native queries

08/12/22 09:49
Using @RowId and native queries to fetch entities, we get ORA-17006. Any solution?More...

Split a string using REGEXP and CONNECT BY...

29/11/22 11:42

This one having the advantage of not using the regexp_substr in the where clause,and - when using the techniques on actual tables - to avoid the usage of "prior sys_guid() is not null"

SELECT column_value AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, column_value) AS data
FROM DUAL
CROSS JOIN
  TABLE(CAST(MULTiSET(
    SELECT LEVEL FROM DUAL
      CONNECT BY LEVEL <= regexp_count('A,B,C,D', ',') + 1
   ) AS sys.odcinumberlist)
  )
;

Split a string using XMLTABLE

27/09/22 09:56
Yet another way - specific to ORACLE - to split a comma-separated string into a set of rows. More...

Count the number of NULL columns in each row

15/06/22 17:30
SQL is good at processing set of rows, but what about columns…
Here an easy way to count number of nulls in a set of columns per row.More...

Preordered tree traversal: conversion from adjency list

08/01/21 07:23
Debate over usage of agency list or preordered tree traversal are numerous,
we don't enter in pros and cons of each one here, we just provide a recursive procedure
to move from agency list to PTT representation and a query using the MODEL clause providing the same functionality.More...

Generate a DOT graph of Foreign Keys dependencies

27/12/20 13:31
Recursive query generating a DOT graph of the foreign keys dependencies of the USER schema.
More...

Recursive queries using WITH, CONNECT BY and graph data: some examples

10/11/20 07:48
We will compare result on a small graph using different options.More...