MacLochlainns Weblog

It’s been a couple releases trying to fix the following error thrown by SQL Developer on Fedora 30 and shown as the following dialog:

When you click the Detail button it shows the following error stack:

java.lang.NoClassDefFoundError: javafx/embed/swing/JFXPanel
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanelImpl(JFXPanelFactory.java:58)
	at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanel(JFXPanelFactory.java:34)
	at oracle.dbtools.raptor.startpage.StartPageViewer.createGUIComponent(StartPageViewer.java:179)
	at oracle.dbtools.raptor.startpage.StartPageViewer.getEditorContent(StartPageViewer.java:136)
	at oracle.ide.editor.AsynchronousEditor$2.run(AsynchronousEditor.java:345)
	at oracle.ide.editor.AsynchronousEditor$5.run(AsynchronousEditor.java:555)
	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443)
	at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68)
	at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303)
	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058)
Caused by: java.lang.ClassNotFoundException: javafx.embed.swing.JFXPanel cannot be found by oracle.sqldeveloper_19.2.0
	at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421)
	at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412)
	at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107)
	at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
	... 10 more

I thought applying the Open Java FX package might fix the problem. I installed the package like the following:

yum install -y openjfx

The installation log:

Last metadata expiration check: 4:03:29 ago on Tue 21 Apr 2020 06:42:26 PM MDT.
Dependencies resolved.
=============================================================================================
 Package            Architecture      Version                       Repository          Size
=============================================================================================
Installing:
 openjfx            x86_64            8.0.202-8.b07.fc30            updates            8.8 M
 
Transaction Summary
=============================================================================================
Install  1 Package
 
Total download size: 8.8 M
Installed size: 11 M
 
Downloading Packages:
openjfx-8.0.202-8.b07.fc30.x86_64.rpm                        2.5 MB/s | 8.8 MB     00:03    
---------------------------------------------------------------------------------------------
Total                                                        2.1 MB/s | 8.8 MB     00:04     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                     1/1 
  Installing       : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Running scriptlet: openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
  Verifying        : openjfx-8.0.202-8.b07.fc30.x86_64                                   1/1 
 
Installed:
  openjfx-8.0.202-8.b07.fc30.x86_64                                                          
 
Complete!

After installing the software, I determined the new JAR files. Then, I added them to my $CLASSPATH environment variable, like:

export CLASSPATH=/usr/share/java/mysql-connector-java.jar:/usr/lib/jvm/openjfx/rt/lib/ext/fxrt.jar:/usr/lib/jvm/openjfx/rt/lib/jfxswt.jar:.

While it appears to load faster with these JAR files, it still raises the same Dialog error. I simply have to continue to look for a complete fix.

I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

In Oracle, a CASE statement ignores the null of a missing ELSE clause between lines 4 and 5. Oracle assumes a null value is an empty string when concatenated to a string with the double-piped concatenation operator. Oracle’s implementation differs from the ANSI standard and is non-compliant.

It would display the following thanks to the SQL reporting features that don’t exist in other Command-Line Interface (CLI) implementations, like mysql, psql, sqlcmd, or cql:

Account    Name
Number     (Last, First MI) City         State      Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71445 Winn, Randi      San Jose     CA         001-(408) 111-1111
B293-71445 Winn, Brian      San Jose     CA         001-(408) 111-1111

However, it fails in Postgres without a notice, warning, or error. Postgres simply returns a null string for the missing ELSE clause and follows the rule that any string concatenated against a null is a null. That means it retunes a null value for the full_name column above. The Postgres behavior is the ANSI standard behavior. After years of working with Oracle it was interesting to have this pointed out while porting a query.

You can fix the statement in Postgres by adding an explicit ELSE clause on a new line 5 that appends an empty string, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
           ELSE ''
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

It would display:

 account_number |  full_name  |   city   | state_province |     telephone      
----------------+-------------+----------+----------------+--------------------
 B293-71445     | Winn, Randi | San Jose | CA             | 001-(408) 111-1111
 B293-71445     | Winn, Brian | San Jose | CA             | 001-(408) 111-1111
(2 rows)

As always, I hope this helps those looking to solve a problem.

Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:

BEGIN
*
ERROR AT line 1:
ORA-22288: FILE OR LOB operation  failed
ORA-06512: AT "STUDENT.LOAD_CLOB_FROM_FILE", line 71
ORA-06512: AT line 11

Oddly enough, it was simple to identify generally. It failed on a call to the DBMS_LOB.LOADCLOBFROMFILE procedure. However, the better question is why did it fail because the virtual directory resolved and the permissions worked.

The first test was to try another file, which worked perfectly with the same code. That meant it had to be something with the physical file. I took a look and sure enough I found a character set problem, like the following:

… he reveals that the Nazgûl, or Ringwraiths, have left Mordor to capture the Ring and kill whoever carries it.

and,

The group flees to the elvish realm of Lothlórien …

The “û” and “ó” characters were incompatible with the default NLS_LANG setting of the database and a CLOB limits the use of non-standard character sets. It’s ashamed that Oracle didn’t through a character set error, which would have expedited resolution of the problem.

As always, I hope this helps those looking for solutions.

My students always struggle initially with basic Linux skills. I wrote little function for their .bashrc file to help them avoid the frustration. It finds and displays all errors by file name, line number and error message for a collection of log files in a single directory (or folder).

errors()
{
  # Determine if any log files exist and check for errors.
  label="File Name:Line Number:Error Code"
  list=`ls ./*.$1 | wc -l`
  if [[ $list} -eq 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    filename=`ls *.txt`
    echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`
  elif [[ ${list} -gt 1 ]]; then
    echo ${label}
    echo "--------------------------------------------------"
    find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-
  fi
}

Let’s say you name your log files with a file extension of .txt, then you would call the function like this:

errors txt

It would return output like the following:

common_lookup_lab.txt:229:ORA-02275: such a referential constraint already exists in the table
common_lookup_lab.txt:239:ORA-02275: such a referential constraint already exists in the table

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

After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.

1. Create a student Oracle user account with the following command:

   CREATE USER student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;

   CREATE USER student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;

2. Grant necessary privileges to the newly created student user:

   GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO student;

   GRANT CREATE cluster, CREATE indextype, CREATE operator , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO student;

As always, I hope this helps those looking for how to do something that’s less than clear because everybody uses tools.

Having run into an obsolete library issue installing Oracle Database 18c XE on Fedora, Version 30, I opted to revert my student image to Oracle Database 11g XE. The installation went without issue but when I tried to log into SQL*Plus as the oracle user, I got the following error message:

sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory

The libnsl.so.1 library is no longer installed as part of the distribution for Fedora 28 forward but you can install it with the yum tool, like:

yum install -y libnsl

Last metadata expiration check: 1:35:44 ago on Sun 11 Aug 2019 07:28:07 PM MDT.
Dependencies resolved.
=============================================================================
 Package        Architecture   Version                 Repository       Size
=============================================================================
Installing:
 libnsl         x86_64         2.29-15.fc30            updates          97 k
 
Transaction Summary
=============================================================================
Install  1 Package
 
Total download size: 97 k
Installed size: 287 k
Downloading Packages:
libnsl-2.29-15.fc30.x86_64.rpm               134 kB/s |  97 kB     00:00    
-----------------------------------------------------------------------------
Total                                         69 kB/s |  97 kB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                     1/1 
  Installing       : libnsl-2.29-15.fc30.x86_64                          1/1 
  Running scriptlet: libnsl-2.29-15.fc30.x86_64                          1/1 
  Verifying        : libnsl-2.29-15.fc30.x86_64                          1/1 
 
Installed:
  libnsl-2.29-15.fc30.x86_64                                                 
 
Complete!

If you attempted to run the oracle-xe utility to configure the database prior to adding this library, it fails to provision the instance without a message. You won’t get the message until you manually try to connect as the sysdba privileged user. At that point, you’ll determine the instance wasn’t provisioned.

You can see that the installation failed when the oracle-xe utility fails to print the following lines to the console after the options are entered:

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

After installing the missing library, the oracle-xe utility works correctly. Alas, it looks like I’ll never bother to sort the Oracle Database 18c XE issues because after this version of the image we are moving the courses to a PostgreSQL database. PostgreSQL offers the smaller footprint that supports the core learning objectives of the courses.

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

Somebody asked why adding a user to the wheel group in didn’t enable them as a sudoer, as qualified in my earlier Fedora post. The reason is that you also need to modify the primary group in the /etc/passwd file to specify the Group ID value for the wheel group as the primary group of the designated student user.

You can identify the Group ID with the following command:

cat /etc/group | grep wheel

It should return the following for the wheel group:

wheel:x:10:student

You need to check the target student user in the /etc/passwd file, which you can do with the following command:

cat /etc/passwd | grep student

It should return the following for the student user, which has a default group value equal to the user of the same name:

student:x:1000:1000:Student:/home/student:/bin/bash

As the root user, edit the /etc/passwd file to correct the student user’s primary group ID, as follows:

student:x:1000:10:Student:/home/student:/bin/bash

You should see the following two lines. If you want authorized sudoers to provide a password (recommended), then modify the first line by removing the # comment. If you don’t want authorized sudoers to provide a password, modify the second line by removing the # comment. Open the /etc/sudoers file with vi or gedit if you’d like a GUI editor.

# %wheel     ALL=(ALL)      ALL
# %wheel     ALL=(ALL)      NOPASSWD: ALL

Hope this helps. It’s a quick update for Fedora 30, you su to root and add your user to the sudoers list with the following syntax:

usermod someusername -a -G wheel

By the way, don’t forget to log off and then back on to the account.

My students wanted a quick solution on how to find the log files that contain errors. That’s a simple line of code in Linux if you want any Oracle errors that start with ORA-:

find $HOME/lab2 -type f | xargs grep -i ora\-

It takes only a moment more to look for errors starting with ORA- or PLS-, like:

find $HOME/lab2 -type f | xargs grep -i -e ora\- -e pls\-

The latter might return something like this:

contact_lab.txt:ORA-00904: "MEMBER_LAB_ID": invalid identifier 
contact_lab.txt:ORA-00942: table or view does not exist 
contact_lab.txt:ORA-00942: table or view does not exist 
member_lab.txt:ORA-02264: name already used by an existing constraint 
member_lab.txt:ORA-00955: name is already used by an existing object

You can improve the error identification by identifying line numbers by adding -n option, like:

find $HOME/lab2 -type f | xargs grep -in -e ora\- -e pls\-

The latter might return something like this when there are two or more files:

contact_lab.txt:76:ORA-00904: "MEMBER_LAB_ID": invalid identifier 
contact_lab.txt:150:ORA-00942: table or view does not exist 
contact_lab.txt:157:ORA-00942: table or view does not exist 
member_lab.txt:75:ORA-02264: name already used by an existing constraint 
member_lab.txt:149:ORA-00955: name is already used by an existing object

Unfortunately, the command raises an error when there aren’t any files found of with a qualified extension. It also fails to prepend the file name when there’s only one qualified file name. As a result of these deficiencies, I’ve written the following Bash shell script. I’ve opted to call it the .findErrors.bashrc file name and deploy it in the user’s $HOME directory.

#!/bin/bash
 
  # Assign any file filter to the ext variable.
  ext=${1}
 
  # Assign the extension or simply use a wildcard for all files.
  if [ ! -z ${ext} ]; then
    ext="*.${ext}"
  else
    ext="*"
  fi
 
  # Assign the number of qualifying files to a variable.
  fileNum=$(ls -l ${ext} 2>/dev/null | grep -v ^l | wc -l)
 
  # Evaluate the number of qualifying files and process.
  if [ ${fileNum} -eq "0" ]; then
    echo "[0] files exist."
  elif [ ${fileNum} -eq "1" ]; then
    fileName=$(ls ${ext})
    find `pwd` -type f | grep -in ${ext} -e ora\- -e pls\- |
    while IFS='\n' read list; do
      echo "${fileName}:${list}"
    done
  else
    find `pwd` -type f | grep -in ${ext} -e ora\- -e pls\- |
    while IFS='\n' read list; do
      echo "${list}"
    done                                                                                                                                                   
  fi

You can modify the errors() function with or without a file extension to identify errors beginning with ORA- or PLS- in their log files. As always, I hope this helps those looking for a solution.

While Python is an interpreted language, Python is a very popular programming language. You may ask yourself why it is so popular? The consensus answers to why it’s so popular points to several factors. For example, Python is a robust high-level programming language that lets you:

• Get complex things done quickly
• Automate system and data integration tasks
• Solve complex analytical problems

You find Python developers throughout the enterprise. Development, release engineering, IT operations, and support teams all use Python to solve problems. Business intelligence and data scientists use Python because it’s easy to use.

Developers don’t generally write end-user applications in Python. They mostly use Python as scripting language. Python provides a simple syntax that lets developers get complex things done quickly. Python also provides you with a vast set of libraries that let you can leverage to solve problems. Those libraries often simplify how you analyze complex data or automate repetitive tasks.

This article explains how to use the Python programming language with the Oracle database. It shows you how to install and use the cx_Oracle library to query data. Part 2 will cover how you insert, update, and delete data in the Oracle database, and how you call and use PL/SQL stored functions and procedures.

The article has two parts:

• How you install and use cx_Oracle with the Oracle database
• How you query data statically or dynamically

This audience for this article should know the basics of writing a Python program. If you’re completely new to Python, you may want to get a copy of Eric Matthes’ Python Crash Course: A Hands-On, Project-Based Introduction to Programming. More experienced developers with shell scripting backgrounds may prefer Al Sweigart’s Automate the Boring Stuff with Python.

This article uses Python 2.7, which appears to be the primary commercial version of Python in most organizations. At least, it’s what most vendors ship with Linux distros. It also happens to be the Python distro on Fedora Linux.

How you install and use cx_Oracle with the Oracle database

The first step requires that you test the current version of Python on your Operating System (OS). For the purpose of this paper, you use the student user account. The student user is in the sudoer list, which gives the account super user privileges.

You can find the Python version by opening a Terminal session and running the following command:

[student@localhost ~]$ python -V

It displays:

Python 2.7.5

You can download the current version of the cx_Oracle library at the Python Software Foundation’s web site. At the time of writing, the current version of the cx_Oracle is the cx_Oracle 5.2.1 version. The cx_Oracle library is available for download as a Red Hat Package Manager (RPM) module.

You download the cx_Oracle-5.2.1-11g-py26-1.x86_64.rpm to the /tmp directory or to a sudoer-enabled user’s downloads directory. Let’s assume you download the RPM into the /tmp directory. After you download the RPM, you can install it with the yum utility with this syntax:

yum install -y /tmp/cx_Oracle-5.2.1-11g-py27-1.x86_64.rpm

However, the most current version is now 7.0. You want the following file on Fedora 64-bit Linux, which can be found at the Python Package Index web site:

cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl

A wheel file requires that you use the pip utility (make sure to upgrade to the current version), like:

sudo pip install cx_Oracle-7.0.0-cp27-cp27mu*.whl

It should print the following to the console:

Processing ./cx_Oracle-7.0.0-cp27-cp27mu-manylinux1_x86_64.whl                                            
Installing collected packages: cx-Oracle                                                                  
Successfully installed cx-Oracle-7.0.0

The cx_Oracle library depends on the Oracle Client software, which may or may not be installed. It installs without a problem but would raise a runtime error when using the Python software. You can check whether cx_Oracle is installed with the following syntax:

rpm –qa oracle-instantclient11.2-basic

If the oracle-instantclient11.2-basic library isn’t installed, the command returns nothing. If the oracle-instantclient11.2-basic library is installed it returns the following:

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64

Assuming you don’t have the Oracle Client software installed, you should download it from Oracle’s Instant Client Downloads web page. After you download the RPM, you install the Oracle 11g Release 2 Client software with the following syntax:

yum install -y /tmp/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

You now have the necessary software installed and configured to run and test Python programs that work with the Oracle database. Python uses a standard path configuration to look for Python modules or libraries. You can see that set of path values by connecting to the Python IDLE environment, which is the runtime environment. The IDLE environment is very much like the SQL*Plus environment.

You connect to the Python IDLE environment by typing the following:

python

It opens the Python IDLE environment. It should display the following:

Python 2.7.5 (default, Apr 10 2015, 08:09:05) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

You import the sys library and then you can print the path elements with the following command:

>>> import sys
print sys.path

It should print the following for Python 2.7 in Fedora Linux:

['', '/usr/lib64/python27.zip', '/usr/lib64/python2.7', '/usr/lib64/python2.7/plat-linux2', '/usr/lib64/python2.7/lib-tk', '/usr/lib64/python2.7/lib-old', '/usr/lib64/python2.7/lib-dynload', '/usr/lib64/python2.7/site-packages', '/usr/lib64/python2.7/site-packages/gtk-2.0', '/usr/lib/python2.7/site-packages']

You can now test whether the environment works by typing the following commands in the IDLE environment:

>>> import cx_Oracle
db = cx_Oracle.connect("student/student@xe")
print db.version

It prints:

11.2.0.2.0

The other two sections require you to test components inside Python files. That means you need to supplement the default Python path variable. You do that by adding values to the Python environment variable, which is $PYTHONPATH.

The following adds the /home/student/Code/python directory to the Python path variable:

export set PYTHONPATH=/home/student/Code/python

Next, we create an connection.py file, which holds the following:

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Print a message.
  print "Connected to the Oracle " + db.version + " database."
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally
  # Close connection. 
  db.close()

The import statement adds the cx_Oracle library to the program scope. The cx_Oracle library’s connect function takes either the user name and password, or the user name, password, and TNS alias.

The except block differs from what you typically see. The code value maps to the SQLCODE value and the message value maps to the SQLERRM value.

You can test the connection.py file as follows in the /home/student/Code/python directory:

python connection.py

It prints the following:

Connected to the Oracle 11.2.0.2.0 database.

This section has shown you how to setup the cx_Oracle library, and how you can test the cx_Oracle library with Python programs.

How you query data statically or dynamically

The prior section shows you how to connect to an Oracle instance and how to verify the driver version of the cx_Oracle library. Like most ODBC and JDBC software, Python first creates a connection. Then, you need to create a cursor inside a connection.

The basicCursor.py program creates a connection and a cursor. The cursor holds a static SQL SELECT statement. The SELECT statement queries a string literal from the pseudo dual table.

# Import the Oracle library.
import sys
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT 'Hello world!' FROM dual")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
}  db.close()

The connect function assigns a database connection to the local db variable. The cursor function returns a cursor and assigns it to the local cursor variable. The execute function dispatches the query to Oracle’s SQL*Plus and returns the result set into a row element of the local cursor variable. The for-each loop reads the row element from the cursor variable and prints one row at a time. Since the cursor only returns a string literal, there’s only one row to return.

You test the program with this syntax:

python basicConnection.py

It prints:

Hello world!

The next basicTable.py program queries the item table. The item table holds a number of rows of data. The code returns each row inside a set of parentheses.

# Import the Oracle library.
import cx_Oracle
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a query.
  cursor.execute("SELECT item_title " +
                 ",      item_rating " +
                 "FROM   item " +
                 "WHERE  item_type = "
                 "        (SELECT common_lookup_id " +
                 "         FROM   common_lookup " +
                 "         WHERE  common_lookup_type = 'DVD_WIDE_SCREEN')")
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0], row[1]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

The SQL query is split across several lines by using the + operator. The + operator concatenates strings, and it lets you format a long query statement. The range for loop returns tuples from the cursor. The tuples are determined by the SELECT-list of the query.

The query returns the following type of results:

('Casino Royale', 'PG-13')
...
('Star Wars - Episode I', 'PG')
('Star Wars - Episode II', 'PG')
('Star Wars - Episode III', 'PG-13')
('Star Wars - Episode IV', 'PG')
('Star Wars - Episode V', 'PG')
('Star Wars - Episode VI', 'PG')

At this point, you know how to work with static queries. The next example shows you how to work with dynamic queries. The difference between a static and dynamic query is that an element of the string changes.

You have two options for creating dynamic strings. The first lets you glue a string inside a query. The second lets you embed one or more bind variables in a string. As a rule, you should use bind variables because they avoid SQL injection risks.

The following is the basicDynamicTable.py script

# Import the Oracle library.
import cx_Oracle
 
sRate = 'PG-13'
 
try:
  # Create a connection.
  db = cx_Oracle.connect("student/student@xe")
 
  # Define a dynamic statment.
  stmt = "SELECT item_title, item_rating FROM item WHERE item_rating = :rating"
 
  # Create a cursor.
  cursor = db.cursor()
 
  # Execute a statement with a bind variable.
  cursor.execute(stmt, rating = sRate)
 
  # Read the contents of the cursor.
  for row in cursor:
    print (row[0], row[1]) 
 
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print >> sys.stderr, "Oracle-Error-Code:", error.code
  print >> sys.stderr, "Oracle-Error-Message:", error.message
 
finally:
  # Close cursor and connection. 
  cursor.close()
  db.close()

You need to assign a dynamic SQL statement to a local string variable. The bind variable is preceded with a colon (:). The execute function takes a string variable with the dynamic SQL statement. Then, you provide a name and value pair. The name needs to match the bind variable in the dynamic SQL statement. The value needs to map to a local Python variable.

The query should return a full list from the item table for the two item_title and item_rating columns:

('Casino Royale', 'PG-13')
...
('Harry Potter and the Goblet of Fire', 'PG-13')
('Harry Potter and the Order of the Phoenix', 'PG-13')
('The Lord of the Rings - Fellowship of the Ring', 'PG-13')
('The Lord of the Rings - Two Towers', 'PG-13')
('The Lord of the Rings - The Return of the King', 'PG-13')
('The Lord of the Rings - The Return of the King', 'PG-13')

This article should have shown you how to effectively work static and dynamic queries. You can find the scripts on the github.com server.

Anyone using the August 2018 Fedora image should note that I neglected to put the right transaction_upload2.csv file in the /u01/app/oracle/upload directory. You can fix that by navigating to the Lab 12 Instructions web page and click on the zip file link to download the correct file. You will see the following dialog asking whether you want to open the file with the Ark utility, click OK to continue:

After clicking OK to open in Ark, you will see the following Ark dialog:

Click on the Home option in the Places dialog to the left, then click the Downloads option. You should see the following dialog before you click the Extract button.

Open a Konsole session and become the root superuser with the following command:

su - root

Change directory to Lab8_Final_CSV_Files directory where you extracted the transaction_upload2.csv file, like this:

cd /home/student/Downloads/Lab8_Final_CSV_Files

Copy the transaction_upload2.csv file to the /u01/app/oracle/upload directory with the following command:

cp /home/student/Downloads/Lab8_Final_CSV_Files/transaction_upload2.csv /u01/app/oracle/upload/.

Change directory to the /u01/app/oracle/upload directory and run the following long list (ll) command:

ll

You should see the following:

-rw-r--r--. 1 oracle dba      80 Aug 23 22:13 character.csv
drwxr-xr-x. 2 oracle dba    4096 Aug 23 20:44 preproc
drwxr-xr-x. 2 oracle dba    4096 Aug 23 23:35 textfile
-rw-r--r--. 1 oracle dba  128700 Dec  4 15:46 transaction_upload2.csv
-rw-r--r--. 1 oracle dba 1739520 Aug 23 22:04 transaction_upload.csv

The transaction_upload2.csv file contains a value of 3 for the created_by and last_updated_by user values. There shouldn’t be a value of 3 in the system_user_id column of the system_user table. The transaction_upload2.csv file should contain a value of 1002 for the created_by and last_updated_by user values.

You can modify the transaction_upload2.csv file once you’ve put it in the correct directory as the root user with the following command:

cat transaction_upload2.csv | sed -e 's/\,3\,/\,1002\,/g' > x; cp x transaction_upload2.csv; rm x

The new image will correct this problem.