MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Uncategorized’ Category

SQL Logic Overkill, again …

with 2 comments

It’s interesting to watch people try to solve problems. For example, the student is required to use a scalar subquery in a SQL lab exercise that I wrote. It should be a simple fix. The problem is structured with an incorrect foreign key value in an external CSV file and the restriction that you can not replace the value in the external CSV file. I hoped that students would see the easiest option was to write a scalar subquery in the SELECT clause to replace the value found in the external file. There’s even a hint about how to use a scalar subquery.

Students who are new to SQL can take very interesting approaches to solve problems. The flexibility of SQL can lead them to solve problems in interesting ways. While the following solution worked to solve the problem, it’s wrong on two levels:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          END AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN common_lookup cl
 WHERE    cl.common_lookup_table = 'TRANSACTION'
 AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
 AND      cl.common_lookup_type = 'CREDIT');

The CASE statement on lines 4 through 12 substitutes a value only when the source value is not a match. That means if the source file is ever correct a null value would become the transaction_type column value, which would make the statement fail because the transaction_type column is NOT NULL constrained in the target transaction table. Therefore, the logic of the student’s approach requires adding an ELSE clause to the CASE statement for the event that the source file is ever corrected. The modified CASE statement would be =the following:

4
5
6
7
8
9
10
11
12
13
14
 ,        CASE
            WHEN NOT tr.transaction_type =
             (SELECT common_lookup_id
              FROM   common_lookup
              WHERE  common_lookup_table = 'TRANSACTION'
              AND    common_lookup_column = 'TRANSACTION_TYPE'
              AND    common_lookup_type = 'CREDIT') THEN
              cl.common_lookup_id
          ELSE
            tr.transaction_type
          END AS transaction_type

The second element of student thought at issue is the CROSS JOIN to the in-line view. It does one thing right and another wrong. It uses the unique key to identify a single row, which effectively adds all the columns for that one row to all rows returned from the external transaction_reversal table. The CROSS JOIN is a correct approach to adding values for computations to a query when you need those columns for computations. The problem with this CROSS JOIN logic may not be immediately obvious when you write it in ANSI SQL 1992 syntax, but it should become obvious when you replace the inline view with a Common Table Expression (CTE) in ANSI SQL 1999 syntax, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
INSERT INTO TRANSACTION
(WITH cte AS
 (SELECT *
  FROM   common_lookup
  WHERE  common_lookup_table = 'TRANSACTION'
  AND    common_lookup_column = 'TRANSACTION_TYPE'
  AND    common_lookup_type = 'CREDIT')
 SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,        cte.common_lookup_id AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr CROSS JOIN cte);

Unfortunately, you would discover that Oracle Database 11g does not support the use of an ANSI SQL 1999 WITH clause inside as the source for an INSERT statement. Oracle Database 12c does support the use of the ANSI SQL 1999 WITH clause inside a subquery of an INSERT statement. That’s an “Oops!” for Oracle 11g because that means the Oracle database fails to meet the ANSI SQL 1999 compliance test. 😉 Great that they fixed it in Oracle 12c. While the nested query would work in Oracle as an ordinary query (outside of an INSERT statement). It raises the following error when you embed it in an INSERT statement:

ERROR AT line 20:
ORA-32034: unsupported USE OF WITH clause

The WITH clause does highlight a key problem with the idea of a CROSS JOIN in this situation. You don’t need all the columns from the common_lookup table. You only need the common_lookup_id column. That make the CROSS JOIN approach suboptimal if it worked.

The complex logic in the original approach is wasted. That’s true because the common_lookup_id value can be supplied to each row as the value from a scalar subquery. The scalar query runs once and the result is placed in the return set for each row. You implement the scalar subquery in the SELECT clause, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO TRANSACTION
(SELECT   transaction_s1.NEXTVAL
 ,        tr.transaction_account
 ,       (SELECT common_lookup_id
          FROM   common_lookup
          WHERE  common_lookup_table = 'TRANSACTION'
          AND    common_lookup_column = 'TRANSACTION_TYPE'
          AND    common_lookup_type = 'CREDIT') AS transaction_type
 ,        tr.transaction_date
 ,       (tr.transaction_amount / 1.06) AS transaction_amount
 ,        tr.rental_id
 ,        tr.payment_method_type
 ,        tr.payment_account_number
 ,        tr.created_by
 ,        tr.creation_date
 ,        tr.last_updated_by
 ,        tr.last_update_date
 FROM     transaction_reversal tr);

There really was no intent or logical outcome where the value from the original CASE statement would be different than the subquery’s common_lookup_id value. That fact makes adding an ELSE clause useless, and the solution viable though inefficient. Also, there was no need for the additional columns from the common_lookup table because they are unused. The subquery on lines 4 through 8 provides the optimal solution and improved efficiency.

Developers should ask themselves two questions when they write SQL:

  • If my logic is so elegant why do I need it to be so elegant?
  • Is there a simpler solution to provide the desired result set?

If there aren’t good answers to both questions, they should re-write it. I hope the examples answer questions and help folks solve problems.

Written by maclochlainn

July 9th, 2017 at 11:08 am

Reset Oracle Password

without comments

This blog entry shows you how to reset the system password for an Oracle Database. It uses a Linux image running Oracle Database 11g Express Edition. It assumes the student user is the sudoer user.

After you sign on to the student user account, you open a Terminal session and you should see the following:

[student@localhost python]$

The oracle user account should be configured to prevent a login. So, you should use the su command or sudo command to open a terminal shell as the root user.

[student@localhost python]$ sudo sh
[sudo] password for student:

As the root user, you can login as the oracle user with the following command:

su - oracle

and, you should see the following prompt. You can see the present working directory (pwd) with the pwd command:

-bash-4.2$ pwd
/u01/app/oracle

You need to source the oracle_env.sh shell file created by the installation of the Oracle Database during the installation. You have two approaches to source the environment file, the first approach is with a dot (.), like

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

or, this

source /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

The oracle_env.sh file contains the following:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$ORACLE_HOME/bin:$PATH

Now, you can connect to the Oracle Database as the internal user with the following command:

sqlplus / as sysdba

Once connected as the internal user, you can reset the system user’s password to “cangetin” with this command:

ALTER USER system IDENTIFIED BY cangetin;

At this point, you can also stop and start the database. You stop the database with this command:

shutdown immediate

You can then start the database with this command:

startup

After setting the system user password, sign out of SQL*Plus. Then, you can type two exits to return to the student user account, like this:

-bash-4.2$ exit
logout
sh-4.2# exit
exit
[student@localhost python]$

As always, I hope this helps those who need to reset the system password when they don’t know what it was to begin with.

Written by maclochlainn

February 21st, 2017 at 3:45 pm

TurboTax Bug

with 4 comments

It was quite annoying to find that TurboTax couldn’t send me a text message to confirm my order of a second state. However, I made the mistake of clicking the “Confirm my account a different way (takes longer)” radio button to get to their web page.

After I got two-step verification enabled on their web site, now it’s impossible to order the second state software. It appears that once you click that button, the software writes it to a file and never prompts you for text, email, or other verification. That seems like a bug to me, what do you think?

TurboTaxBug

Hope this helps others …

Written by maclochlainn

December 23rd, 2015 at 1:31 pm

Posted in Uncategorized

Result Cache Functions

without comments

I finally got around to cleaning up old contact me messages. One of the messages raises a question about RESULT_CACHE functions. The writer wanted an example implementing both a standalone schema and package RESULT_CACHE function.

The question references a note from the Oracle Database 11g PL/SQL Programming book (on page 322). More or less, that note points out that at the time of writing a RESULT_CACHE function worked as a standalone function but failed inside a package. When you tried it, you raised the following error message:

PLS-00999: Implementation Restriction (may be temporary)

It’s no longer true in Oracle 11gR2, but it was true in Oracle 11gR1. I actually mentioned in a blog entry 4 years ago.

You can implement a schema RESULT_CACHE function like this:

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION full_name
( pv_first_name   VARCHAR2
, pv_last_name    VARCHAR2 )
RETURN VARCHAR2 RESULT_CACHE IS
BEGIN  
  RETURN pv_first_name || ' ' || pv_last_name;
END full_name;
/

You would call it like this from a query:

SELECT   full_name(c.first_name, c.last_name)
FROM     contact c;

You can declare a published package RESULT_CACHE function like this:

1
2
3
4
5
6
7
CREATE OR REPLACE PACKAGE cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE;
END cached_function;
/

You would implement the function in a package body like this:

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE PACKAGE BODY cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE IS
  BEGIN  
    RETURN pv_first_name || ' ' || pv_last_name;
  END full_name; 
END cached_function;
/

You would call the package function like this from a query:

SELECT   cached_function.full_name(c.first_name, c.last_name)
FROM     contact c;

I hope this answers the question.

Written by maclochlainn

May 29th, 2012 at 12:31 am

Collaborate 2012 – Day 3

without comments

Virtualization is important and Dave Welch from the House of Brick gave a great presentation of experiences with VMWare and Tier 1 databases. It was a comprehensive presentation, but the white paper was easier to follow. The slides were complete but the volume of information was a lot for an hour presentation. Well worth the time though.

Utah Oracle User Group (UTOUG) announced a call for Fall Symposium papers today. The Fall Symposium will be in Salt Lake City on 9/6/2012. If you’re interested in presenting on Oracle or MySQL, the call for presentations will be open until 6/15/2012.

The conference party was tonight, and it provided some nice orderves and pizza. The theme was a return to 1980s music, and some folks really dressed their parts. You can listen to a short snapshot of the band by clicking the image to launch a small video segment.

I’m looking forward to the APEX Behind the Scenes presentation at 8:30 a.m. tomorrow. When the conference is over, I won’t miss the smoke filled air that we walk through from the Luxor to the Mandalay. It’s really amazing that the complex is more than a mile in length. It runs from the Luxor to the Mandalay South Conference Center.

Written by maclochlainn

April 26th, 2012 at 12:31 am

Java Generics in Oracle

without comments

Somebody posed the question about using a Comparator in the sorting examples provided in this earlier post on Updating Table View Columns (columns using a Varray or Nested Table of a single scalar data type). It seems the individual thought that you can’t use Java Generics inside an Oracle Database 11g’s Java libraries. It’s seems odd since they’ve been around since Java 5.

You can use Generics like those shown in the following example. It builds on explanation from the prior post. If you want to get the whole set of facts click the link above but you should have all the code you need in this post.

An example like this requires you first define a collection of strings in the database. This one uses the following definition:

1
2
CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

This creates the Java library source, and line 21 shows the use of Generics in the instantiation of a anonymous Comparator class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SortList" AS
 
  // Import required classes.
  import java.io.*;
  import java.security.AccessControlException;
  import java.sql.*;
  import java.util.Arrays;
  import java.util.Comparator;
  import oracle.sql.driver.*;
  import oracle.sql.ArrayDescriptor;
  import oracle.sql.ARRAY;
 
  // Define class.
  public class Sorting {
    public static ARRAY sortTitleCaseList(oracle.sql.ARRAY list) throws SQLException, AccessControlException {
 
    // Convert Oracle data type to Java data type.
    String[] unsorted = (String[])list.getArray();
 
    // Sort elements.
    Arrays.sort(unsorted, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
      // Declare a sorting key integer for the return value.
      int sortKey;
 
      // Check if lowercase words match and sort on first letter only.
      if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
         sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
      else
        sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
      // Return the sorting index.
      return sortKey; }});
 
      // Define a connection (this is for Oracle 11g).
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
      // Declare a mapping to the schema-level SQL collection type.
      ArrayDescriptor arrayDescriptor = new ArrayDescriptor("STRINGLIST",conn);
 
      // Translate the Java String{} to the Oracle SQL collection type.
      ARRAY sorted = new ARRAY(arrayDescriptor,conn,((Object[])unsorted));
 
      // Return the sorted list.
      return sorted; }
  }
/

The PL/SQL wrapper for this class would be:

1
2
3
4
CREATE OR REPLACE FUNCTION sortTitleCaseList(list STRINGLIST) RETURN STRINGLIST IS
LANGUAGE JAVA
NAME 'Sorting.sortNaturalCaseList(oracle.sql.ARRAY) return oracle.sql.ARRAY';
/

You can test the code with the following query:

1
2
SELECT column_value
FROM TABLE(sortTitleCaseList(stringlist('Oranges','apples','Apples','Bananas','Apricots','apricots')));

It sorts the strings based on a title case sort, like:

COLUMN_VALUE
------------------------
Apples
apples
Apricots
apricots
Bananas
Oranges
 
6 rows selected.

If you want a quick example of a Generic Collection sort operation outside of the database, here a sample file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
// Import required classes.
import java.util.Arrays;
import java.util.Comparator;
 
/**
 * An example of using a Comparator sort.
 */
public class SortStaticString {
 
  /*
   * Sort the instance array.
   */
  public static String[] sortTitleCaseList(String[] list) {
 
    // Sort elements by title case.
    Arrays.sort(list, new Comparator<String>() {
      public int compare(String s1, String s2) {
 
        // Declare a sorting key integer for the return value.
        int sortKey;
 
        // Check if lowercase words match and sort on first letter only.
        if (s1.toLowerCase().compareTo(s2.toLowerCase()) == 0)
          sortKey = s1.substring(0,1).compareTo(s2.substring(0,1));
        else
          sortKey = s1.toLowerCase().compareTo(s2.toLowerCase());
 
        // Return the sorting index.
        return sortKey; }});
 
    // Return the sorted Index.     
    return list;
  }
 
  /*
   * Test case.
   */
  public static void main(String[] args) {
 
    // Construct and instance and apply sort method.     
    args = SortStaticString.sortTitleCaseList(args);
 
    // Print the title case sorted list.
    for (int i = 0; i < args.length; i++) {
      System.out.println(args[i]); }
  }
}

You would call the SortStaticString class as follows:

java SortStaticString apples Oranges Pears Apples orange Grapefruit

I hope this helps the interested party and any others looking for a sample file. 😉

Written by maclochlainn

January 11th, 2012 at 12:49 am