MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘sql’ Category

Install Cassandra on Fedora

with one comment

It was quite interesting to discover that DataStax no longer provides the DataStax Community version of Apache Cassandra or the DataStax Distribution of Apache Cassandra. Needless to say, I was quite disappointed because it means folks will get less opportunity to learn how to use Cassandra because it makes it more difficult for beginning developers.

I spent a good hour sorting through what was available and then figuring out the real requirements to install Apache Cassandra 3.11. These are the instructions.

Install Java and JRE as Prerequisites

If you don’t have the JRE installed, you should download it from Oracle’s website and install it. After you download the latest version of the JRE package (jre-8u141-linux-x64.rpm). You should use the rpm utility to install the JRE package, like the following example:

rpm -ivh /home/student/Downloads/jre-8*.rpm

It should generate the following installation report:

Preparing...                          ################################# [100%]
	package jre1.8.0_141-1.8.0_141-fcs.x86_64 is already installed
sh-4.2# rpm -qa jre
sh-4.2# rpm -qf jre
error: file /jre: No such file or directory
sh-4.2# rpm -qa | grep jre
jre1.8.0_141-1.8.0_141-fcs.x86_64
sh-4.2# rpm -qa | grep jre | rpm -qi
rpm: no arguments given for query
sh-4.2# rpm -qi `rpm -qa | grep jre`
Name        : jre1.8.0_141
Version     : 1.8.0_141
Release     : fcs
Architecture: x86_64
Install Date: Mon 24 Jul 2017 11:09:58 PM PDT
Group       : Development/Tools
Size        : 139460427
License     : http://java.com/license
Signature   : (none)
Source RPM  : jre1.8.0_141-1.8.0_141-fcs.src.rpm
Build Date  : Wed 12 Jul 2017 04:47:52 AM PDT
Build Host  : jdk7-lin2-amd64
Relocations : /usr/java 
Packager    : Java Software <jre-comments@java.sun.com>
Vendor      : Oracle Corporation
URL         : URL_REF
Summary     : Java Platform Standard Edition Runtime Environment
Description :
The Java Platform Standard Edition Runtime Environment (JRE) contains
everything necessary to run applets and applications designed for the
Java platform. This includes the Java virtual machine, plus the Java
platform classes and supporting files.
 
The JRE is freely redistributable, per the terms of the included license.

Confirm Java and JRE Installation

You can check the current installed version of Java and JRE by using the alternatives utility with the --config option and the keyword of java or jre.

sh-4.2# alternatives --config java

It should generate the following list when you check for the java library:

There are 3 programs which provide 'java'.
 
  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64/jre/bin/java
 + 2           /usr/lib/jvm/jre-1.8.0-openjdk.x86_64/bin/java
   3           /usr/java/jre1.8.0_141/bin/java
 
Enter to keep the current selection[+], or type selection number:

It should generate the following list when you check for the javac library:

There are 2 programs which provide 'javac'.
 
  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64/bin/javac
 + 2           /usr/lib/jvm/java-1.8.0-openjdk.x86_64/bin/javac
 
Enter to keep the current selection[+], or type selection number:

After installing and selecting them as the designated alternative, if you have more than one Java or JRE installed on your OS, you should create a configuration file for the root user. You should include the following to set your $PATH, $JAVA_HOME, and $JRE_HOME environment variables:

# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre
 
# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

Install Apache Cassandra

The yum utility is the best way to install Apache Cassandra. However, you will need to configure the /etc/yum.repos.d/cassandra.repo before you attempt to install Cassandra 3.11 from the Apache organization, like this:

[cassandra]
name=Apache Cassandra
baseurl=https://www.apache.org/dist/cassandra/redhat/311x/
gpgcheck=1
repo_gpgcheck=1
gpgkey=https://www.apache.org/dist/cassandra/KEYS

After you’ve added the necessary yum configuration file and ensured you’re using both Java 1.8 and JRE 1.8, you can install Apache Cassandra with the following yum command as the root user or as a sudoer member with the sudo command:

yum install -y cassandra

If successful, you should see the following output:

Loaded plugins: langpacks, refresh-packagekit
cassandra/signature                                         |  819 B  00:00     
cassandra/signature                                         | 2.9 kB  00:00 !!! 
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
updates/20/x86_64/metalink                                  | 2.6 kB  00:00     
Resolving Dependencies
--> Running transaction check
---> Package cassandra.noarch 0:3.11.0-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package            Arch            Version            Repository          Size
================================================================================
Installing:
 cassandra          noarch          3.11.0-1           cassandra           28 M
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 28 M
Installed size: 37 M
Downloading packages:
warning: /var/cache/yum/x86_64/20/cassandra/packages/cassandra-3.11.0-1.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID fe4b2bda: NOKEY
Public key for cassandra-3.11.0-1.noarch.rpm is not installed
cassandra-3.11.0-1.noarch.rpm                               |  28 MB  00:07     
Retrieving key from https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0xF2833C93:
 Userid     : "Eric Evans <eevans@sym-link.com>"
 Fingerprint: cec8 6bb4 a0ba 9d0f 9039 7cae f835 8fa2 f283 3c93
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x8D77295D:
 Userid     : "Eric Evans <eevans@sym-link.com>"
 Fingerprint: c496 5ee9 e301 5d19 2ccc f2b6 f758 ce31 8d77 295d
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x2B5C1B00:
 Userid     : "Sylvain Lebresne (pcmanus) <sylvain@datastax.com>"
 Fingerprint: 5aed 1bf3 78e9 a19d ade1 bcb3 4bd7 36a8 2b5c 1b00
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0x0353B12C:
 Userid     : "T Jake Luciani <jake@apache.org>"
 Fingerprint: 514a 2ad6 31a5 7a16 dd00 47ec 749d 6eec 0353 b12c
 From       : https://www.apache.org/dist/cassandra/KEYS
Importing GPG key 0xFE4B2BDA:
 Userid     : "Michael Shuler <michael@pbandjelly.org>"
 Fingerprint: a26e 528b 271f 19b9 e5d8 e19e a278 b781 fe4b 2bda
 From       : https://www.apache.org/dist/cassandra/KEYS
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
Warning: RPMDB altered outside of yum.
  Installing : cassandra-3.11.0-1.noarch                                    1/1 
  Verifying  : cassandra-3.11.0-1.noarch                                    1/1 
 
Installed:
  cassandra.noarch 0:3.11.0-1                                                   
 
Complete!

Starting Cassandra

You should start Cassandra as the cassandra user. Before starting Cassandra, you need to create a .bashrc file for the cassandra user because one isn’t created by default since you can’t log on to the Linux OS as the cassandra user. The home directory for the cassandra user is /var/lib/cassandra and the owner of that directory is the root user.

As the root user, create the following .bashrc file for the cassandra user:

# Wrap sqlplus with rlwrap to edit prior lines with the
# up, down, left and right keys.
cqlsh()
{
  if [ "$RLWRAP" = "0" ]; then
    cqlsh "$@"
  else
    rlwrap cqlsh "$@"
  fi
}
 
# Set vi as a command line editor.
set -o vi
 
# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre
 
# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

You should start Cassandra in background, like this:

cassandra

Using Cassandra

As the student user in my developer Fedora instance, you should be able to connect using the following:

cqlsh

You will see the following:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh> HELP
 
Documented shell commands:
===========================
CAPTURE  CLS          COPY  DESCRIBE  EXPAND  LOGIN   SERIAL  SOURCE   UNICODE
CLEAR    CONSISTENCY  DESC  EXIT      HELP    PAGING  SHOW    TRACING
 
CQL help topics:
================
AGGREGATES               CREATE_KEYSPACE           DROP_TRIGGER      TEXT     
ALTER_KEYSPACE           CREATE_MATERIALIZED_VIEW  DROP_TYPE         TIME     
ALTER_MATERIALIZED_VIEW  CREATE_ROLE               DROP_USER         TIMESTAMP
ALTER_TABLE              CREATE_TABLE              FUNCTIONS         TRUNCATE 
ALTER_TYPE               CREATE_TRIGGER            GRANT             TYPES    
ALTER_USER               CREATE_TYPE               INSERT            UPDATE   
APPLY                    CREATE_USER               INSERT_JSON       USE      
ASCII                    DATE                      INT               UUID     
BATCH                    DELETE                    JSON            
BEGIN                    DROP_AGGREGATE            KEYWORDS        
BLOB                     DROP_COLUMNFAMILY         LIST_PERMISSIONS
BOOLEAN                  DROP_FUNCTION             LIST_ROLES      
COUNTER                  DROP_INDEX                LIST_USERS      
CREATE_AGGREGATE         DROP_KEYSPACE             PERMISSIONS     
CREATE_COLUMNFAMILY      DROP_MATERIALIZED_VIEW    REVOKE          
CREATE_FUNCTION          DROP_ROLE                 SELECT          
CREATE_INDEX             DROP_TABLE                SELECT_JSON

Written by maclochlainn

July 25th, 2017 at 9:23 pm

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

Oracle SQL Strip Quotes

without comments

Somebody wanted to know how to strip double quotes from strings. Obviously, they’re playing with the DBMS_METADATA package. It’s quite simple, the TRIM function does it, like this:

SELECT TRIM(BOTH '"' FROM '"Hello World!"') AS "Message"
FROM   dual;

It will print:

Hello World!

As always, I hope this helps those looking for a solution.

Written by maclochlainn

June 18th, 2017 at 10:30 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

Oracle Diagnostic Queries

without comments

It’s always a challenge when you want to build your own Oracle SQL Tools. I was asked how you could synchronize multiple cursors into a single source. The answer is quite simple, you write an Oracle object type to represent a record structure, an Oracle list of the record structure, and a stored function to return the list of the record structure.

For this example, you create the following table_struct object type and a table_list collection type:

/* Drop the types from most to least dependent. */
DROP TYPE table_list;
DROP TYPE table_struct;
 
/* Create the record type structure. */
CREATE OR REPLACE
  TYPE table_struct IS OBJECT
  ( table_name    VARCHAR2(30)
  , column_cnt    NUMBER
  , row_cnt       NUMBER );
/
 
/* Create the collection of a record type structure. */
CREATE OR REPLACE
  TYPE table_list IS TABLE OF table_struct;
/

The following listing function now reads all table names from the user_tables view. A subordinate cursor reads the user_tab_columns view for the number of columns in a table. A Native Dynamic SQL (NDS) cursor counts the number of rows in each tables found in the .

/* Create the listing function. */
CREATE OR REPLACE
FUNCTION listing RETURN table_list IS
 
  /* Variable list. */
  lv_column_cnt  NUMBER;
  lv_row_cnt     NUMBER;
 
  /* Declare a statement variable. */
  stmt  VARCHAR2(200);
 
  /* Declare a system reference cursor variable. */
  lv_refcursor  SYS_REFCURSOR;
  lv_table_cnt  NUMBER;
 
  /* Declare an output variable.  */
  lv_list  TABLE_LIST := table_list();
 
  /* Declare a table list cursor that excludes APEX tables. */
  CURSOR c IS
    SELECT table_name
    FROM   user_tables
    WHERE  table_name NOT IN
            ('DEPT','EMP','APEX$_ACL','APEX$_WS_WEBPG_SECTIONS','APEX$_WS_ROWS'
            ,'APEX$_WS_HISTORY','APEX$_WS_NOTES','APEX$_WS_LINKS'
            ,'APEX$_WS_TAGS','APEX$_WS_FILES','APEX$_WS_WEBPG_SECTION_HISTORY'
            ,'DEMO_USERS','DEMO_CUSTOMERS','DEMO_ORDERS','DEMO_PRODUCT_INFO'
            ,'DEMO_ORDER_ITEMS','DEMO_STATES');
 
  /* Declare a column count. */
  CURSOR cnt
  ( cv_table_name  VARCHAR2 ) IS
    SELECT   table_name
    ,        COUNT(column_id) AS cnt_columns
    FROM     user_tab_columns
    WHERE    table_name = cv_table_name
    GROUP BY table_name;
 
BEGIN
  /* Read through the data set of non-environment variables. */
  FOR i IN c LOOP
 
    /* Count the columns of a table. */
    FOR j IN cnt(i.table_name) LOOP
      lv_column_cnt := j.cnt_columns;
    END LOOP;
 
    /* Declare a statement. */
    stmt := 'SELECT COUNT(*) AS column_cnt FROM '||i.table_name;
 
    /* Open the cursor and write set to collection. */
    OPEN lv_refcursor FOR stmt;
    LOOP
      FETCH lv_refcursor INTO lv_table_cnt;
      EXIT WHEN lv_refcursor%NOTFOUND; 
      lv_list.EXTEND;
      lv_list(lv_list.COUNT) := table_struct(
                                    table_name => i.table_name
                                  , column_cnt => lv_column_cnt
                                  , row_cnt    => lv_table_cnt );
    END LOOP;
  END LOOP;
 
  RETURN lv_list;
END;
/

The following query pulls the processed data set as the function’s result:

COL table_name   FORMAT A20     HEADING "Table Name"
COL column_cnt   FORMAT 9,999  HEADING "Column #"
COL row_cnt      FORMAT 9,999  HEADING "Row #"
SELECT table_name
,      column_cnt
,      row_cnt
FROM   TABLE(listing);

It returns the following result set:

Table Name	     Column #  Row #
-------------------- -------- ------
SYSTEM_USER		   11	   5
COMMON_LOOKUP		   10	  49
MEMBER			    9	  10
CONTACT 		   10	  18
ADDRESS 		   10	  18
STREET_ADDRESS		    8	  28
TELEPHONE		   11	  18
RENTAL			    8  4,694
ITEM			   14	  93
RENTAL_ITEM		    9  4,703
PRICE			   11	 558
TRANSACTION		   12  4,694
CALENDAR		    9	 300
AIRPORT 		    9	   6
ACCOUNT_LIST		    8	 200
 
15 rows selected.

As always, I hope this helps those trying to work with the Oracle database.

Written by maclochlainn

January 5th, 2017 at 7:28 pm

DB_LINK w/o tnsnames.ora

without comments

A question popped up, which I thought was interesting. How can you create a DB_LINK in Oracle without the DBA changing the tnsnames.ora file? It’s actually quite easy, especially if the DBA sets the TNS address name the same as the instance’s service name or in older databases SID value.

  1. Do the following with the tnsping utility:
    tnsping mohawk

    It should return this when the server’s hostname is mohawk and domain name is techtinker.com:

    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 26-JUL-2016 16:55:58
     
    Copyright (c) 1997, 2011, Oracle.  All rights reserved.
     
    Used parameter files:
     
     
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mohawk.techtinker.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
    OK (10 msec)
  1. You can now create a DB_LINK in another Oracle instance without a tnsnames.ora entry by referencing the type of server connection and service name with the following syntax (please note that you should remove extraneous white space):
    CREATE DATABASE LINK test
      CONNECT TO student IDENTIFIED BY student
      USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mohawk.techtinker.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))'

    In an older database version, you may need to refer to the SID, like this:

    CREATE DATABASE LINK test
      CONNECT TO student IDENTIFIED BY student
      USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mohawk.techtinker.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)))'

    Then, you can query a contact table in the remote instance like this:

    SELECT COUNT(*)
    FROM   contact@test;

As always, I hope this helps somebody trying to solve a problem.

Written by maclochlainn

July 26th, 2016 at 6:15 pm

Debug PL/SQL Web Pages

without comments

What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.

Here’s a sample web page that fails to run successfully …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE OR REPLACE
  PROCEDURE html_table_values
  ( name_array   OWA_UTIL.VC_ARR
  , value_array  OWA_UTIL.VC_ARR ) IS
  BEGIN
    /* Print debug to SQL*Plus session. */
    FOR i IN 1..name_array.COUNT LOOP
      DBMS_OUTPUT.put_line('Value ['||name_array(i)||'='||value_array(i)||']');
    END LOOP;
 
    /* Open HTML page with the PL/SQL toolkit. */
    htp.print('<!DOCTYPE html>');
    htp.htmlopen;
    htp.headopen;
    htp.htitle('Test');
    htp.headclose;
    htp.bodyopen;
    htp.line;
    htp.print('Test');
    htp.line;
    htp.bodyclose;
    htp.htmlclose;
END;
/

You can test the program with the following anonymous block as the SYSTEM user, which is equivalent to the following URL:

http://localhost:8080/db/html_table_values?begin=1004&end=1012

The following test program lets you work:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
  x  OWA_UTIL.VC_ARR;
  y  OWA_UTIL.VC_ARR;
BEGIN
  /* Insert first row element. */
  x(1) := 'begin';
  y(1) := '1004';
 
  /* Insert second row element. */
  x(2) := 'end';
  y(2) := '1012';
 
  /* Call the anonymous schema's web page. */
  anonymous.html_table_values(x,y);
END;
/

It should print:

Value [begin=1004]
Value [end=1012]

I hope this helps those looking for a solution.

Written by maclochlainn

May 16th, 2016 at 5:18 pm

SQL Developer & PL/SQL

without comments

While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the Dbms Output view for SQL Developer.

SQL Developer DBMS_OUTPUT Configuration

SQLDeveloper1

  1. You need to open SQL Developer, which may look like this when the DBMS_OUTPUT view isn’t visible.

SQLDeveloper1

  1. You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu element.

SQLDeveloper1

  1. You should see a grayed-out Dbms Output view.

SQLDeveloper1

  1. You should type a simply “Hello World!” anonymous block program in PL/SQL, like the one shown in the drawing.

SQLDeveloper1

  1. After writing the “Hello World!” anonymous block program in PL/SQL, click the green arrow to start the statement and you will see two things. There is now a Script Output view between your console and Dbms Output views, and it should say “anonymous block completed.” Unfortunately, none of your output is displayed in the Dbms Output view because you need to enable it.

SQLDeveloper1

  1. If you hover over the Dbms Output view’s green arrow, you see the help message that describes the behavior of the green arrow. The Dbms Output green arrow lets you enable the Dbms Output view for output.

SQLDeveloper1

  1. After you click the Dbms Output view’s green arrow, you receive a Select Connection prompt for the view. Make sure you have the right user, and click the OK button to continue.

SQLDeveloper1

  1. After you create the connection for the Dbms Output stream, the view area becomes white rather than gray.

SQLDeveloper1

  1. Click the green arrow to start the statement and you will see the “Hello World!” string in the Dbms Output view.”

As always, I hope this helps those looking for a solution.

Written by maclochlainn

May 13th, 2016 at 10:55 am

Using a Sparse Index

with 2 comments

My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set? You can solve the problem by passing an ADT (Attribute Data Type) or UDT (User Defined Type) variable into a subquery of a cursor. The subquery leverages the TABLE function to translate the ADT or UDT into SQL result set, which is equivalent to a comma-delimited list of values.

You can also solve this problem with Native Dynamic SQL (NDS). However, the person who posed the question didn’t want to use NDS to build out a variable length list of comma-delimited numbers.

You need to create three object types for this example. They are:

  • a list of numbers
  • a record structure, declared as an object type without methods
  • a list of the record structure

These are the SQL commands to create the required data types:

CREATE OR REPLACE
  TYPE list_ids IS TABLE OF NUMBER;
/
CREATE OR REPLACE
  TYPE item_struct IS OBJECT
  ( item_id       NUMBER
  , item_title    VARCHAR2(80)
  , release_date  DATE );
/
CREATE OR REPLACE
  TYPE item_struct_list IS TABLE OF item_struct;
/

Next, you create a nonsynchronous function. It takes a sparsely populated list of values that map to the surrogate key of the column, which is typically the table’s primary key column. It returns a collection of the item_struct object type. This type of function is an object-table function.

The code follows:

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
CREATE OR REPLACE
  FUNCTION nonsynchronous 
  ( pv_list_ids  LIST_IDS ) RETURN item_struct_list IS
    /* Declare a record data structure list. */
    lv_struct_list  ITEM_STRUCT_LIST := item_struct_list();
 
    /* Declare a sparsely indexed list of film items. */
    CURSOR get_items
    ( cv_list_ids  LIST_IDS ) IS
      SELECT   item_id AS item_id
      ,        item_title
      ||       CASE
                 WHEN item_subtitle IS NOT NULL THEN
                   ': '|| item_subtitle
               END AS item_title
      ,        release_date AS release_date
      FROM     item
      WHERE    item_id IN (SELECT *
                           FROM   TABLE(cv_list_ids))
      ORDER BY item_id;
BEGIN
  /* Lood through the sparsely populated list of numbers. */
  FOR i IN get_items(pv_list_ids) LOOP
    lv_struct_list.EXTEND;
    lv_struct_list(lv_struct_list.COUNT) := item_struct( item_id      => i.item_id
                                                       , item_title   => i.item_title
                                                       , release_date => i.release_date );
  END LOOP;
 
  /* Return the record structure list. */
  RETURN lv_struct_list;
END;
/

The foregoing nonsynchronous function uses a nested query that transforms to a result set on lines 18 and 19. In the execution block of the program, it uses a call to the item_struct structure to capture and assign row values to an element of the lv_struct_list variable.

You can now test the nonsynchronous function with the following query:

COL item_id      FORMAT 9999  HEADING "Item|ID #"
COL item_title   FORMAT A40   HEADING "Item Title"
COL release_date FORMAT A11   HEADING "Release|Date"
SELECT   *
FROM     TABLE(nonsynchronous(list_ids(1002, 1013, 1007)));

The query returns the record set as an ordered list in the result set, like:

Item					       Release
 ID # Item Title			       Date
----- ---------------------------------------- -----------
 1002 Star Wars I: Phantom Menace	       04-MAY-99
 1007 RoboCop				       24-JUL-03
 1013 The DaVinci Code			       19-MAY-06

I hope this answers the question about how to get results sets with sparsely populated ID values.

Written by maclochlainn

May 11th, 2016 at 1:37 am

REGEXP_LIKE Behavior

with one comment

Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  lv_input  VARCHAR2(100);
BEGIN
  /* Assign input value. */
  lv_input := '&input';
 
  /* Check for numeric string. */
  IF REGEXP_LIKE(lv_input,'[[:digit:]]') THEN
    dbms_output.put_line('It''s a number.');
  ELSE
    dbms_output.put_line('It''s a string.');
  END IF;
END;
/

When they test numbers it appears to works, it even appears to work when the test string start with number, but it fails with any string that starts with a character. That’s because the REGEXP_LIKE function on line 8 only checks the first character, but the following checks all the characters in the string.

8
  IF REGEXP_LIKE(lv_inputs(i),'[[:digit:]]{'||LENGTH(lv_inputs(i))||'}') THEN

You can also fix it with the following non-Posix solution:

8
  IF REGEXP_LIKE(lv_input,'[[0-9]]') THEN

You can add a collection to the program and use it to test single-digit, double-digit, and string with a leading integer. Save the program as test.sql and you can test three conditions with one call.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
  /* Declare the local collection type. */
  TYPE inputs IS TABLE OF VARCHAR2(100);
 
  /* Declare a local variable of the collection type. */
  lv_inputs  INPUTS;
BEGIN
  /* Assign the inputs to the collection variable. */
  lv_inputs := inputs('&1','&2','&3');
 
  /* Read through the collection and print whether it's an number or string. */
  FOR i IN 1..lv_inputs.COUNT LOOP
    IF REGEXP_LIKE(lv_inputs(i),'[[:digit:]]{'||LENGTH(lv_inputs(i))||',}') THEN
      dbms_output.put_line('It''s a number.');
    ELSE
      dbms_output.put_line('It''s a string.');
    END IF;
  END LOOP;
END;
/

You can run the test.sql program like this:

SQL> @test.sql 1 12 1a

It prints:

It's a number.
It's a number.
It's a string.

As always, I hope this helps those looking for a solution.

Written by maclochlainn

September 30th, 2015 at 7:23 pm