Archive for the ‘Oracle’ Category
AlmaLinux+SQLDeveloper
This post makes the assumption that you’ve installed the current version of Java and the Java SDK. If you haven’t, you can find instructions on my blog. You will also need to have an installation of the Oracle database running on your server or a remote service, or cloud micro service.
The remainder of this post deals with downloading, installing, and configuring Oracle’s SQL Developer for AlmaLinux 9, which is the GNU version of Red Hat Enterprise 9.
- Go to Oracle’s download site and download the sqldeveloper RPM file. You will need to provide your Oracle credentials to download SQL Developer. It will download to your sudoer account’s Download directory. In this example the sudoer user is the student user.
You should see the following web page and click on the Download link, provided you’re installing on Linux it’ll look like the next image.
Then, you need to accept the license and click the Download button. Oracle will prompt you for your credentials if you’re not logged in on the web page already.
- Next, you need to navigate to the Downloads directory and install the sqldeveloper RPM. Assuming your sudoer user is student, you can get to the Downloads directory with the following command.
cd ~student/Downloads
Assuming, you downloaded the SQL Developer package, you can use the following command to install any downloaded version of sqldeveloper package.
sudo rpm -Uvh `ls sqldeveloper*.rpm`
The log file for this is:
Display detailed console log →
warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch Verifying... ################################# [100%] Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing... ################################# [100%] warning: RPM v3 packages are deprecated: sqldeveloper-22.2.1-234.1810.noarch Updating / installing... 1:sqldeveloper-22.2.1-234.1810 ################################# [100%]
- Click on Activities link in the upper left corner and then the clustered nine dots to view applications. Choose the SQL Developer icon and double click and you should see the following dialog if you’re a first time user. Unless you’re upgrading, click the No button to proceed.
- The first official screen after checking whether you need to transfer existing settings wants to know whether or allow or disallow user tracking. Click the OK button if you don’t mind Oracle tracking your use, or click the Allow automated usage reporting to Oracle checkbox to disallow Oracle tracking your use before you click the OK button.
- The next screen lets you set up a TNS names file or use an existing file. I clicked on the XE existing database to continue.
This is a password prompt for the TNS name resolution of XE, which should point to the Oracle Database 11g Express Edition. (I’d use a more current version but I couldn’t resist using the smaller footprint of the pre-containerized Oracle databases.)
Replace XE with the name of a sandboxed user, like student, and the password for the student user before you click the OK button. (If you don’t know what a sandboxed user is, you should. It’s a user with limited access to a database of the same name in the context of an Oracle database. A non-sandboxed user has global privileges like the system user.)
- The next screen lets you enter SQL statements agains the student database. You can click the X button in the top right corner to close the application.
You’ve now installed SQL Developer. However, sometimes I want to start SQL Developer from the command-line interface (CLI) but you’ll get a bunch of warnings and unnecessary Java non-critical errors. So, I create an alias to avoid the extraneous noise. I create the sqldeveloper alias in the .bashrc file for it. You can create a sqldeveloper alias by adding the following line to your .bashrc file:
The unnecessary noise when you don’t create a sqldeveloper alias.
Display detailed console log →
Oracle SQL Developer Copyright (c) 2005, 2021, Oracle and/or its affiliates. All rights reserved. OpenJDK 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release. WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper/netbeans/platform/lib/boot.jar) WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager WARNING: System::setSecurityManager will be removed in a future release WARNING: A terminally deprecated method in java.lang.System has been called WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper/ide/extensions/oracle.ide.jar) WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore WARNING: System::setSecurityManager will be removed in a future release java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) java.lang.IllegalAccessException: class oracle.ideimpl.config.EnvironOptionsPanel cannot access class com.sun.java.swing.plaf.gtk.GTKLookAndFeel (in module java.desktop) because module java.desktop does not export com.sun.java.swing.plaf.gtk to unnamed module @49c746f at java.base/jdk.internal.reflect.Reflection.newIllegalAccessException(Reflection.java:392) at java.base/java.lang.reflect.AccessibleObject.checkAccess(AccessibleObject.java:674) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:489) at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128) at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347) at java.base/java.lang.Class.newInstance(Class.java:645) at oracle.ideimpl.config.EnvironOptionsPanel._initLafCombo(EnvironOptionsPanel.java:540) at oracle.ideimpl.config.EnvironOptionsPanel.initComponents(EnvironOptionsPanel.java:238) at oracle.ideimpl.config.EnvironOptionsPanel.<init>(EnvironOptionsPanel.java:99) at oracle.dbtools.raptor.config.EnvironOptionsPanelWrapper.<init>(EnvironOptionsPanelWrapper.java:30) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at javax.ide.util.MetaClass.newInstance(MetaClass.java:145) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:122) at oracle.dbtools.raptor.standalone.IndexedPreferencesCommand$IndexPreferencesTask.doWork(IndexedPreferencesCommand.java:65) at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) |
You create the alias like this:
alias sqldeveloper="sqldeveloper 2>/dev/null &" |
That completes the instructions. Good luck with SQL Developer. It’s a great tool.
Wrapping sqlplus
After sorting out the failures of Oracle Database 11g (11.2.0) on AlmaLinux, I grabbed the Enterprise Linux 9 rlwrap library. The rlwrap is a ‘readline wrapper’ that uses the GNU readline library to
allow the editing of keyboard input for any other command. Input history is remembered across invocations, separately for each command; history completion and search work as in bash and completion word
lists can be specified on the command line.
Installed it with the dnf utility:
dnf install -y rlwrap |
It gave me this log file:
Last metadata expiration check: 0:53:30 ago on Fri 02 Dec 2022 01:07:54 AM EST. Dependencies resolved. ================================================================================================================================ Package Architecture Version Repository Size ================================================================================================================================ Installing: rlwrap x86_64 0.45.2-3.el9 epel 132 k Transaction Summary ================================================================================================================================ Install 1 Package Total download size: 132 k Installed size: 323 k Downloading Packages: rlwrap-0.45.2-3.el9.x86_64.rpm 162 kB/s | 132 kB 00:00 -------------------------------------------------------------------------------------------------------------------------------- Total 117 kB/s | 132 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Regex version mismatch, expected: 10.40 2022-04-14 actual: 10.37 2021-05-26 Preparing : 1/1 Installing : rlwrap-0.45.2-3.el9.x86_64 1/1 Running scriptlet: rlwrap-0.45.2-3.el9.x86_64 1/1 Verifying : rlwrap-0.45.2-3.el9.x86_64 1/1 Installed: rlwrap-0.45.2-3.el9.x86_64 Complete! |
Then, I added this sqlplus function to the student account’s .bashrc file:
sqlplus () { # Discover the fully qualified program name. path=`which rlwrap 2>/dev/null` file='' # Parse the program name from the path. if [ -n ${path} ]; then file=${path##/*/} fi; # Wrap when there is a file and it is rewrap. if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then rlwrap sqlplus "${@}" else echo "Command-line history unavailable: Install the rlwrap package." $ORACLE_HOME/bin/sqlplus "${@}" fi } |
Then, I connected to the old, but tiny, footprint of Oracle Database 11g XE for testing, which worked:
Yes, I couldn’t resist. After all Version 11 was the last non-pluggable release and it’s been 11 years since its release. A double lucky 11.
Naturally, you can always use vi (or vim) to edit the command history provided you include the following command in your .bashrc file:
set -o vi |
Next, I’ll build a new VM instance with the current version of Oracle Database XE for student testing.
As always, I hope this helps those working with Oracle’s database products.
Oracle Library Missing
It was always aware of a problem with Oracle 11g XE on various Linux platforms from 10 years ago. I knew it was misleading but never found the time to explain the error that occurred during the cloning of the instance.
While it would occur when you were on an unsupported version of Linux, it was easy to fix. For example, after downloading the old compressed oracle-xe-11.2.0-1.0.x86_64.rpm.zip file, you uncompress it. Then, you run the file with the following command:
rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm |
This command will install the packages in verbose syntax and display the following messages:
[sudo] password for mclaughlinm: Preparing packages for installation... oracle-xe-11.2.0-1.0 Executing post-install steps... You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database. |
Connect as the root user to another instance of the terminal and run the following command:
/etc/init.d/oracle-xe configure |
You will see the following control output:
Oracle Database 11g Express Edition Configuration ------------------------------------------------- This will configure on-boot properties of Oracle Database 11g Express Edition. The following questions will determine whether the database should be starting upon system boot, the ports it will use, and the passwords that will be used for database accounts. Press <Enter> to accept the defaults. Ctrl-C will abort. Specify the HTTP port that will be used for Oracle Application Express [8080]: Specify a port that will be used for the database listener [1521]: Specify a password to be used for database accounts. Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after initial configuration: Confirm the password: Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y Starting Oracle Net Listener...Done Configuring database...grep: /u01/app/oracle/product/11.2.0/xe/config/log/*.log: No such file or directory grep: /u01/app/oracle/product/11.2.0/xe/config/log/*.log: No such file or directory Done /bin/chmod: cannot access '/u01/app/oracle/diag': No such file or directory Starting Oracle Database 11g Express Edition instance...Done Installation completed successfully. |
This looks like an unsolvable problem, and for many it was too hard to solve. Most never knew the next step to take to discover the missing library. The failure actually occurs when the configuration tries to launch SQL*Plus. You can test that by creating the following oracle_env.sh parameter script:
# Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe; export ORACLE_HOME ORACLE_SID=XE; export ORACLE_SID NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`; export NLS_LANG ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi |
Then, source the oracle_env.sh file like this:
. ./oracle_env.sh |
As the oracle user, try to connect to the sqlplus executable with this command:
sqlplus / as sysdba |
It’ll raise the following error:
sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory |
You won’t find the /usr/lib64/libnsl.so.1 because it’s a symbolic link to the /usr/lib64/libnsl-2.29.so shared library file, which you can find on older Fedora installations. AlmaLinux has libnsl2, which you can download from the pgks.org.
After finding the library and installing it in the /usr/lib64 directory, the balance of the fix is to run the cloning manually. This type of error can occur for newer version of the database but it’s easiest to highlight with the Oracle 11g XE installation.
You also can find it in the libnsl2-devel development libraries on the pkgs.org web site:
You may need to build the libnsl.so.1 symbolic link as the root user with the following command:
ln -s libnsl-2.29.so libnsl.so.1 |
Ensure the file permissions for these files are:
-rwxr-xr-x. 1 root root 218488 Dec 2 01:33 libnsl-2.29.so lrwxrwxrwx. 1 root root 14 Dec 2 01:39 libnsl.so.1 -> libnsl-2.29.so |
After you create the database, you can provision a student user and database, like so:
Oracle Database 11g (Pre-containerization)
After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.
- 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;
- 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;
Oracle Database 21c (Post-containerization)
After you create and provision the Oracle Database 21c Express Edition (XE), you can create a c##student container user with the following two step process.
- Create a c##student Oracle user account with the following command:
CREATE USER c##student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
- Grant necessary privileges to the newly created c##student user:
GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO c##student;
As always, it should help you solve new problems.
Debugging PL/SQL Functions
Teaching student how to debug a PL/SQL function takes about an hour now. I came up with the following example of simple deterministic function that adds three numbers and trying to understand how PL/SQL implicitly casts data types. The lecture follows a standard Harvard Case Study, which requires the students to suggest next steps. The starting code is:
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN a + b + c; END; / |
Then, we use one test case for two scenarios:
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2 ----------- ----------- 4 5 |
Then, I ask why does that work? Somehow many students can’t envision how it works. Occasionally, a student will say it must implicitly cast the INTEGER to a DOUBLE PRECISION data type and add the numbers as DOUBLE PRECISION values before down-casting it to an INTEGER data type.
Whether I have to explain it or a student volunteers it, the next question is: “How would you build a test case to see if the implicit casting?” Then, I ask them to take 5-minutes and try to see how the runtime behaves inside the function.
At this point in the course, they only know how to use dbms_output.put_line to print content from anonymous blocks. So, I provide them with a modified adding function:
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 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
The time limit ensures they spend their time typing the code from the on screen display and limits testing to the dbms_output.put_line attempt. Any more time and one or two of them would start using Google to find an answer.
I introduce the concept of a Black Box as their time expires, and typically use an illustration like the following to explain that by design you can’t see inside runtime operations of functions. Then, I teach them how to do exactly that.
You can test the runtime behaviors and view the variable values of functions by doing these steps:
- Create a debug table, like
CREATE TABLE debug ( msg VARCHAR2(200));
- Make the function into an autonomous transaction by:
- Adding the PRAGMA (or precompiler) instruction in the declaration block.
- Adding a COMMIT at the end of the execution block.
- Use an INSERT statement to write descriptive text with the variable values into the debug table.
Here’s the refactored test code:
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 | CREATE OR REPLACE FUNCTION adding ( a DOUBLE PRECISION , b INTEGER , c DOUBLE PRECISION ) RETURN INTEGER DETERMINISTIC IS /* Define a double precision temporary result variable. */ temp_result NUMBER; /* Define an integer return variable. */ temp_return INTEGER; /* Precompiler Instrunction. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* * Perform the calculation and assign the value to the temporary * result variable. */ temp_result := a + b + c; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Result Value: ['||temp_result||']'); /* * Assign the temporary result variable to the return variable. */ temp_return := temp_result; /* Insert the temporary result variable into the debug table. */ INSERT INTO debug (msg) VALUES ('Temporary Return Value: ['||temp_return||']'); /* Commit to ensure the write succeeds in a separate process scope. */ COMMIT; /* Return the integer return variable as the function result. */ RETURN temp_return; END; / |
While an experienced PL/SQL developer might ask while not introduce conditional computation, the answer is that’s for another day. Most students need to uptake pieces before assembling pieces and this example is already complex for a newbie.
The same test case works (shown to avoid scrolling up):
SELECT adding(1.25, 2, 1.24) AS "Test Case 1" , adding(1.25, 2, 1.26) AS "Test Case 2" FROM dual; |
It returns:
Test Case 1 Test Case 2 ----------- ----------- 4 5 |
Now, they can see the internal step-by-step values with this query:
COL msg FORMAT A30 HEADING "Internal Variable Auditing" SELECT msg FROM debug; |
It returns:
Internal Variable Auditing ------------------------------ Temporary Result Value: [4.49] Temporary Return Value: [4] Temporary Result Value: [4.51] Temporary Return Value: [5] 4 rows selected. |
What we learn is that:
- Oracle PL/SQL up-casts the b variable from an integer to a double precision data type before adding the three input variables.
- Oracle PL/SQL down-casts the sum of the three input variables from a double precision data type to an integer by applying traditionally rounding.
I hope this helps those trying to understand implicit casting and discovering how to unhide an opaque function’s operations for debugging purposes.
Oracle PLS-00103 Gotcha
Teaching PL/SQL can be fun and sometimes challenging when you need to troubleshoot a student error. Take the Oracle PLS-00103 error can be very annoying when it return like this:
24/5 PLS-00103: Encountered the symbol "LV_CURRENT_DATE" WHEN expecting one OF the following: language |
Then, you look at the code and see:
22 23 24 25 | , pv_user_id NUMBER ) IS /* Declare local constants. */ lv_current_date DATE := TRUNC(SYSDATE); |
Obviously, there’s nothing wrong on the line number that the error message pointed. Now, here’s where it gets interesting because of a natural human failing. The student thought they had something wrong with declaring the variable and tested as stand alone procedure and anonymous block. Naturally, they were second guessing what they knew about the PL/SQL.
That’s when years of experience with PL/SQL kicks in to solve the problem. The trick is recognizing two things:
- The error message points to the first line of code in a package body.
- The error is pointing to the first character on the line after the error.
That meant that the package body was incorrectly defined. A quick check to the beginning of the package body showed:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
The student failed to designate the package as an implementation by omitting the keyword BODY from line 2. The proper definition of the package body should be:
1 2 3 4 5 6 | CREATE OR REPLACE PACKAGE BODY account_creation AS PROCEDURE insert_contact ( pv_first_name VARCHAR2 , pv_middle_name VARCHAR2 := NULL |
That’s the resolution for the error message. I wrote this because I checked if they should have been able to find a helpful article with a google search. I discovered that there wasn’t an answer like this that came up after 10 minutes of various searches.
As always, I hope this helps those writing PL/SQL.
PL/SQL Overloading
So, I wrote an updated example of my grandma and tweetie_bird for my students. It demonstrates overloading with the smallest parameter lists possible across a transaction of two tables. It also shows how one version of the procedure can call another version of the procedure.
The tables are created with the following:
/* Conditionally drop grandma table and grandma_s sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP IF i.object_type = 'TABLE' THEN /* Use the cascade constraints to drop the dependent constraint. */ EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , created_by NUMBER CONSTRAINT grandma_nn3 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); /* Create the sequence. */ CREATE SEQUENCE grandma_seq; /* Conditionally drop a table and sequence. */ BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / /* Create the table with primary and foreign key out-of-line constraints. */ CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , created_by NUMBER CONSTRAINT tweetie_bird_nn4 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); /* Create sequence. */ CREATE SEQUENCE tweetie_bird_seq; |
The sylvester package specification holds the two overloaded procedures, like:
CREATE OR REPLACE PACKAGE sylvester IS /* Three variable length strings. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ); /* Two variable length strings and a number. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ); END sylvester; / |
The sylvester package implements two warner_brother procedures. One takes the system user’s ID and the other takes the system user’s name. The procedure that accepts the system user name queries the system_user table with the system_user_name to get the system_user_id column and then calls the other version of itself. This demonstrates how you only write logic once when overloading and let one version call the other with the added information.
Here’s the sylvester package body code:
CREATE OR REPLACE PACKAGE BODY sylvester IS /* Procedure warner_brother with user name. */ PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_id NUMBER ) IS /* Declare a local variable for an existing grandma_id. */ lv_grandma_id NUMBER; FUNCTION get_grandma_id ( pv_grandma_house VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_grandma_id ( cv_grandma_house VARCHAR2 ) IS SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_grandma_id; BEGIN /* Set the savepoint. */ SAVEPOINT starting; /* * Identify whether a member account exists and assign it's value * to a local variable. */ lv_grandma_id := get_grandma_id(pv_grandma_house); /* * Conditionally insert a new member account into the member table * only when a member account does not exist. */ IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_id , grandma_house , created_by ) VALUES ( grandma_seq.NEXTVAL , pv_grandma_house , pv_system_user_id ); /* Assign grandma_seq.currval to local variable. */ lv_grandma_id := grandma_seq.CURRVAL; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_id , tweetie_bird_house , grandma_id , created_by ) VALUES ( tweetie_bird_seq.NEXTVAL , pv_tweetie_bird_house , lv_grandma_id , pv_system_user_id ); /* If the program gets here, both insert statements work. Commit it. */ COMMIT; EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; PROCEDURE warner_brother ( pv_grandma_house VARCHAR2 , pv_tweetie_bird_house VARCHAR2 , pv_system_user_name VARCHAR2 ) IS /* Define a local variable. */ lv_system_user_id NUMBER := 0; FUNCTION get_system_user_id ( pv_system_user_name VARCHAR2 ) RETURN NUMBER IS /* Initialized local return variable. */ lv_retval NUMBER := 0; -- Default value is 0. /* A cursor that lookups up a grandma's ID by their name. */ CURSOR find_system_user_id ( cv_system_user_id VARCHAR2 ) IS SELECT system_user_id FROM system_user WHERE system_user_name = pv_system_user_name; BEGIN /* Assign a grandma_id as the return value when a row exists. */ FOR i IN find_system_user_id(pv_system_user_name) LOOP lv_retval := i.system_user_id; END LOOP; /* Return 0 when no row found and the grandma_id when a row is found. */ RETURN lv_retval; END get_system_user_id; BEGIN /* Convert a system_user_name to system_user_id. */ lv_system_user_id := get_system_user_id(pv_system_user_name); /* Call the warner_brother procedure. */ warner_brother ( pv_grandma_house => pv_grandma_house , pv_tweetie_bird_house => pv_tweetie_bird_house , pv_system_user_id => lv_system_user_id ); EXCEPTION /* When anything is broken do this. */ WHEN OTHERS THEN /* Until any partial results. */ ROLLBACK TO starting; END; END sylvester; / |
The following anonymous block test case works with the code:
BEGIN sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Cage' , pv_system_user_name => 'DBA 3' ); sylvester.warner_brother( pv_grandma_house => 'Blue House' , pv_tweetie_bird_house => 'Tree House' , pv_system_user_id => 4 ); END; / |
You can now query the results with this SQL*PLus formatting and query:
/* Query results from warner_brother procedure. */ COL grandma_id FORMAT 9999999 HEADING "Grandma|ID #" COL grandma_house FORMAT A14 HEADING "Grandma House" COL created_by FORMAT 9999999 HEADING "Created|By" COL tweetie_bird_id FORMAT 9999999 HEADING "Tweetie|Bird ID" COL tweetie_bird_house FORMAT A18 HEADING "Tweetie Bird House" SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON g.grandma_id = tb.grandma_id; |
You should see the following data:
Grandma Created Tweetie Grandma Created ID # Grandma House By Bird ID Tweetie Bird House ID # By -------- -------------- -------- -------- ------------------ -------- -------- 1 Blue House 3 1 Cage 1 3 1 Blue House 3 2 Tree House 1 4
As always, I hope complete code samples help solve real problems.
PL/pgSQL Transactions
There are many nuances that I show students about PL/pgSQL because first I teach them how to use PL/SQL. These are some of the differences:
- PL/SQL declares the function or procedure and then uses the IS keyword; whereas, PL/pgSQL uses the AS keyword.
- PL/SQL uses the RETURN keyword for functions declarations, like:
RETURN [data_type} IS
Whereas, PL/pgSQL uses the plural RETURNS keyword in the function declaration, like:
RETURNS [data_type] AS
- PL/SQL considers everything after the function or procedure header as the implicit declaration section; whereas, PL/pgSQL requires you block the code with something like $$ (double dollar symbols) and explicitly use the DECLARE keyword.
- PL/SQL supports local functions (inside the DECLARE block of a function or procedure); whereas, PL/pgSQL doesn’t.
- PL/SQL puts the variable modes (IN, INOUT, OUT) between the parameter name and type; whereas, PL/pgSQL puts them before the variable name.
- PL/SQL declares cursors like:
CURSOR cursor_name (parameter_list) IS
Whereas, PL/pgSQL declares them like
cursor_name CURSOR (parameter_list) FOR
- PL/SQL terminates and runs the block by using an END keyword, an optional module name, a semicolon to terminate the END; statement, and a forward slash to dispatch the program to PL/SQL statement engine:
END [module_name]; /
Whereas, PL/pgSQL terminates and runs the block by using an END keyword, a semicolon to terminate the END; statement, two dollar signs to end the PL/pgSQL block, and a semicolon to dispatch the program.
END LANGUAGE plpgsql; $$;
After all that basic syntax discussion, we try to create a sample set of tables, a function, a procedure, and a test case in PL/pgSQL. They’ve already done a virtually equivalent set of tasks in PL/SQL.
Here are the steps:
- Create the grandma and tweetie_bird tables:
/* Conditionally drop grandma table and grandma_s sequence. */ DROP TABLE IF EXISTS grandma CASCADE; /* Create the table. */ CREATE TABLE GRANDMA ( grandma_id SERIAL , grandma_house VARCHAR(30) NOT NULL , PRIMARY KEY (grandma_id) ); /* Conditionally drop a table and sequence. */ DROP TABLE IF EXISTS tweetie_bird CASCADE; /* Create the table with primary and foreign key out-of-line constraints. */ SELECT 'CREATE TABLE tweetie_bird' AS command; CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id SERIAL , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INTEGER NOT NULL , PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES grandma (grandma_id) );
- Create a get_grandma_id function that returns a number, which should be a valid primary key value from the grandma_id column of the grandma table.
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
CREATE OR REPLACE FUNCTION get_grandma_id ( IN pv_grandma_house VARCHAR ) RETURNS INTEGER AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Local return variable. */ lv_retval INTEGER := 0; -- Default value is 0. /* Use a cursor, which will not raise an exception at runtime. */ find_grandma_id CURSOR ( cv_grandma_house VARCHAR ) FOR SELECT grandma_id FROM grandma WHERE grandma_house = cv_grandma_house; BEGIN /* Assign a value when a row exists. */ FOR i IN find_grandma_id(pv_grandma_house) LOOP lv_retval := i.grandma_id; END LOOP; /* Return 0 when no row found and the ID # when row found. */ RETURN lv_retval; END; $$ LANGUAGE plpgsql;
- Create a Warner_brother procedure that writes data across two tables as a transaction. You con’t include any of the following in your functions or procedures because all PostgreSQL PL/pgSQL functions and procedures are transaction by default:
- SET TRANSACTION
- START TRANSACTION
- SAVEPOINT
- COMMIT
A ROLLBACK should be placed in your exception handler as qualified on lines #33 thru #36. The warner_brother procedure inserts rows into the grandma and tweetie_bird tables.
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
/* Create or replace procedure warner_brother. */ CREATE OR REPLACE PROCEDURE warner_brother ( pv_grandma_house VARCHAR , pv_tweetie_bird_house VARCHAR ) AS $$ /* Required for PL/pgSQL programs. */ DECLARE /* Declare a local variable for an existing grandma_id. */ lv_grandma_id INTEGER; BEGIN /* Check for existing grandma row. */ lv_grandma_id := get_grandma_id(pv_grandma_house); IF lv_grandma_id = 0 THEN /* Insert grandma. */ INSERT INTO grandma ( grandma_house ) VALUES ( pv_grandma_house ) RETURNING grandma_id INTO lv_grandma_id; END IF; /* Insert tweetie bird. */ INSERT INTO tweetie_bird ( tweetie_bird_house , grandma_id ) VALUES ( pv_tweetie_bird_house , lv_grandma_id ); EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE NOTICE '[%] [%]', SQLERRM, SQLSTATE; END; $$ LANGUAGE plpgsql;
You should take note of the RETURNING-INTO statement on line #22. The alternative to this clause isn’t pretty if you know that PostgreSQL uses a table name, column name, and the literal seq value separated by underscores (that is, snake case), like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL('grandma_grandma_id_seq');
It would be even uglier if you had to look up the sequence name, like:
/* Assign current value to local variable. */ lv_grandma_id := CURRVAL(pg_get_serial_sequence('grandma','grandma_id'));
- You can test the combination of these two stored procedures with the following DO-block:
/* Test the warner_brother procedure. */ DO $$ BEGIN /* Insert the yellow house. */ CALL warner_brother( 'Yellow House', 'Cage'); CALL warner_brother( 'Yellow House', 'Tree House'); /* Insert the red house. */ CALL warner_brother( 'Red House', 'Cage'); CALL warner_brother( 'Red House', 'Tree House'); END; $$ LANGUAGE plpgsql;
Then, query the results:
SELECT * FROM grandma g INNER JOIN tweetie_bird tb ON. g.grandma_id = tb.grandma_id;
It should return:
grandma_id | grandma_house | tweetie_bird_id | tweetie_bird_house | grandma_id ------------+---------------+-----------------+--------------------+------------ 1 | Red House | 1 | Cage | 1 1 | Red House | 2 | Tree House | 1 2 | Yellow House | 3 | Cage | 2 2 | Yellow House | 4 | Tree House | 2 (4 rows)
As always, I hope writing a clear and simple examples helps those looking for sample code.
Oracle Partitioned Tables
Oracle Partitioned Tables
Learning Outcomes
- Learn about List Partitioning.
- Learn about Range Partitioning.
- Learn about Hash Partitioning.
- Learn about Composite Partitioning.
Lesson Material
Partitioning is the process of breaking up a data source into a series of data sources. Partitioned tables are faster to access and transact against. Partitioning data becomes necessary as the amount of data grows in any table. It speeds the search to find rows and insert, update, or delete rows.
Oracle Database 21c supports four types of table partitioning: list, range, hash, and composite partitioning.
List Partitioning
A list partition works by identifying a column that contains a value, such as a STATE column in an ADDRESS table. Partitioning clauses follow the list of columns and constraints.
A list partition could use a STATE column, like the following (the complete example is avoided to conserve space, and the three dots represent the balance of partitions not shown):
CREATE TABLE franchise ( franchise_id NUMBER CONSTRAINT pk_franchise PRIMARY KEY , franchise_name VARCHAR(20) , city VARCHAR(20) , state VARCHAR(20)) PARTITION BY LIST(state) ( PARTITION offshore VALUES('Alaska', 'Hawaii') , PARTITION west VALUES('California', 'Oregon', 'Washington') , PARTITION desert VALUES ('Arizona','New Mexico') , PARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ); |
This can be used with other values such as ZIP codes with great effect, but the maintenance of list partitioning can be considered costly. Cost occurs when the list of values changes over time. Infrequent change means low cost, while frequent change means high costs. In the latter case, you should consider other partitioning strategies. Although an Oracle database supports partitioning on a variable-length string, MySQL performs list partitioning only on integer columns.
Range Partitioning
Range partitioning is very helpful on any column that contains a continuous metric, such as dates or time. It works by stating a minimum set that is less than a certain value, and then a group of sets of higher values until you reach the top most set of values. This type of partition helps you improve performance by letting you search ranges rather than complete data sets. Range partitioning is also available in MySQL.
A range example based on dates could look like this:
PARTITION BY RANGE(rental_date) ( PARTITION rental_jan2011 VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY') , PARTITION rental_feb2011 VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY') , PARTITION rental_mar2011 VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY') , ... ); |
The problem with this type of partitioning, however, is that the new months require constant management. Many North American businesses simply add partitions for all months in the year as an annual maintenance task during the holidays in November or December. Companies that opt for bigger range increments reap search and access benefits from range partitioning, while minimizing ongoing maintenance expenses.
Hash Partitioning
Hash partitioning is much easier to implement than list or range partitioning. Many DBAs favor it because it avoids the manual maintenance of list and range partitioning. Oracle Database 21c documentation recommends that you implement a hash for the following reasons:
- There is no concrete knowledge about how much data maps to a partitioning range.
- The sizes of partitions are unknown at the outset and difficult to balance as data is added to the database.
- A range partition might cluster data in an ineffective way.
This next statement creates eight partitions and stores them respectively in one of the eight tablespaces. The hash partition manages nodes and attempts to balance the distribution of rows across the nodes.
PARTITION BY HASH(store) PARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3, tablespace4 ,tablespace5, tablespace6, tablespace7, tablespace8); |
As you can imagine the maintenance for this type of partitioning is low. Some DBAs choose this method to get an initial sizing before adopting a list or range partitioning plan. Maximizing the physical resources of the machine ultimately rests with the DBAs who manage the system. Developers need to stand ready to assist DBAs with analysis and syntax support.
Composite Partitioning
Composite partitioning requires a partition and subpartition. The composites are combinations of two types of partitioning—typically, list and range partitioning, or range and hash composite partitioning. Which of these you should choose depends on a few considerations. List and range composite partitioning is done for historical information and is well suited for data warehouses. This method lets you partition on unordered or unrelated column values.
A composite partition like this uses the range as the partition and the list as the subpartition, like the following:
PARTITION BY RANGE (rental_date) SUBPARTITION BY LIST (state) (PARTITION FQ1_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY')) (SUBPARTITION offshore VALUES('Alaska', 'Hawaii') , SUBPARTITION west VALUES('California', 'Oregon', 'Washington') , SUBPARTITION desert VALUES ('Arizona','New Mexico') , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ) ,(PARTITION FQ2_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY')) (SUBPARTITION offshore VALUES('Alaska', 'Hawaii') , SUBPARTITION west VALUES('California', 'Oregon', 'Washington') , SUBPARTITION desert VALUES ('Arizona','New Mexico') , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming') , ... ) , ... ) |
Range and hash composite partitioning is done for historical information when you also need to stripe data. Striping is the process of creating an attribute in a table that acts as a natural subtype or separator of data. Users typically view data sets of one subtype, which means organizing the data by stripes (subtypes) can speed access based on user access patterns.
Range is typically the partition and the hash is the subpartition in this composite partitioning schema. The syntax for this type of partition is shown next:
PARTITION BY RANGE (rental_date) SUBPARTITION BY HASH(store) SUBPARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3 ,tablespace4, tablespace5, tablespace6 ,tablespace7, tablespace8) ( PARTITION rental_jan2011 VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY') , PARTITION rental_feb2011 VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY') , PARTITION rental_mar2011 VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY') , ... ) |
PL/SQL Table Function
An Oracle example was requested as a comparison against the quick tutorial I wrote on how to do this in PostgreSQL’s PL/pgSQL. Unfortunately, there are many more moving parts to deliver this type of solution in Oracle’s PL/SQL.
The functions is same and simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German. The additional moving parts are the required User-Defined Types (UDTs); one is a record structure and the other is a list (or Oracle parlance table).
The drops are unconditional and as such will trigger errors the first time they’re run but including PL/SQL blocks to make them conditional would have made the code much larger. It’s already larger because Oracle doesn’t support comma-delimited lists in the VALUES clause.
I’ll stage this with the same conquistador table used in the last post. Then, connect to the psql shell and run the following script file:
/* Drop the conquistador table. */ DROP TABLE conquistador; /* Create the conquistador table. */ CREATE TABLE conquistador ( conquistador_id NUMBER , conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30) , lang VARCHAR(2)); /* Drop the conquistador sequence. */ DROP SEQUENCE conquistador_seq; /* Create the conquistador_seq with a 1001 start value. */ CREATE SEQUENCE conquistador_seq START WITH 1001; /* Insert 9 rows into the table. */ INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Juan de Fuca','Ioánnis Fokás','Greek','el'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Nicolás de Federmán','Nikolaus Federmann','German','de'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Sebastián Caboto','Sebastiano Caboto','Venetian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Jorge de la Espira','Georg von Speyer','German','de'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Wenceslao Linck','Wenceslaus Linck','Bohemian','cs'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Fernando Consag','Ferdinand KonÅ¡cak','Croatian','sr'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Américo Vespucio','Amerigo Vespucci','Italian','it'); INSERT INTO conquistador ( conquistador_id, conquistador, actual_name, nationality, lang ) VALUES (conquistador_seq.NEXTVAL,'Alejo GarcÃa','Aleixo Garcia','Portuguese','pt'); |
While unnecessary in PL/pgSQL, you must create User-Defined Types (UDTs) to write a table function. You must also create a local procedure to avoid allocating memory before assigning values to the list. These are the UDTs required:
/* Drop the dependency before the dependent type. */ DROP TYPE conquistador_table; DROP TYPE conquistador_struct; /* Create the UDT for a record structure accessible in SQL. */ CREATE OR REPLACE TYPE conquistador_struct IS OBJECT ( conquistador VARCHAR(30) , actual_name VARCHAR(30) , nationality VARCHAR(30)); / /* Create a list of the UDT. */ CREATE OR REPLACE TYPE conquistador_table IS TABLE OF conquistador_struct; / |
Drop any existing function or procedure of the same name before you try to build it. Oracle’s OR REPLACE fails when you try to use it for a function when there is already a procedure using the same name, and vice versa.
/* Drop the function to avoid any conflict with a procedure of the same name. */ DROP FUNCTION getConquistador; |
Now, you can build another script file to create the getConquistador function, like:
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 | /* Create the function. */ CREATE OR REPLACE FUNCTION getConquistador (pv_lang IN VARCHAR) RETURN conquistador_table IS /* Declare a return variable. */ lv_retval CONQUISTADOR_TABLE := conquistador_table(); /* Declare a dynamic cursor. */ CURSOR get_conquistador ( cv_lang VARCHAR2 ) IS SELECT c.conquistador , c.actual_name , c.nationality FROM conquistador c WHERE c.lang = cv_lang; /* Local procedure to add to the song. */ PROCEDURE ADD ( pv_input CONQUISTADOR_STRUCT ) IS BEGIN lv_retval.EXTEND; lv_retval(lv_retval.COUNT) := pv_input; END ADD; BEGIN /* Read through the cursor and assign to the UDT table. */ FOR i IN get_conquistador(pv_lang) LOOP add(conquistador_struct( i.conquistador , i.actual_name , i.nationality )); END LOOP; /* Return collection. */ RETURN lv_retval; END; / |
While there is some white space for readability, the Oracle version is basically twice as long as the PL/pgSQL version. It also requires you to add UDTs to the data dictionary to make it work. PL/pgSQL actually doesn’t let you add references to type definitions and requires you use enumerated descriptions with column definitions.
Then, you can test it with the following syntax. The TABLE function is required to convert the list to a SQL consumable result set:
COL conquistador FORMAT A21 COL actual_name FORMAT A21 COL nationality FORMAT A12 SELECT * FROM TABLE(getConquistador('de')); |
It will return the following:
CONQUISTADOR ACTUAL_NAME NATIONALITY --------------------- --------------------- ------------ Nicolás de Federmán Nikolaus Federmann German Jorge de la Espira Georg von Speyer German 2 rows selected. |
As always, I hope this helps with a technique that’s useful.
PL/SQL List to Struct
Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.
Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.
Oracle does this for two reasons:
- They handle lists of standard types in a difference C++ class than they do UDT types.
- They rigidly adhere to Interface Definition Language (IDL) principles.
The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.
You need to build a UDT object type and lists of both ADT and UDT data types.
/* Create a table of strings. */ CREATE OR REPLACE TYPE tre AS TABLE OF VARCHAR2(20); / /* Create a structure of a date, number, and string. */ CREATE OR REPLACE TYPE struct IS OBJECT ( xdate DATE , xnumber NUMBER , xstring VARCHAR2(20)); / /* Create a table of tre type. */ CREATE OR REPLACE TYPE structs IS TABLE OF struct; / |
The cast_strings function is defined below:
CREATE OR REPLACE FUNCTION cast_strings ( pv_list TRE ) RETURN struct IS /* Declare a UDT and initialize an empty struct variable. */ lv_retval STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); BEGIN /* Loop through list of values to find only the numbers. */ FOR i IN 1..pv_list.LAST LOOP /* Ensure that a sparsely populated list can't fail. */ IF pv_list.EXISTS(i) THEN /* Order if number evaluation before string evaluation. */ CASE WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN lv_retval.xnumber := pv_list(i); WHEN verify_date(pv_list(i)) THEN IF lv_retval.xdate IS NULL THEN lv_retval.xdate := pv_list(i); ELSE lv_retval.xdate := NULL; END IF; WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN lv_retval.xstring := pv_list(i); ELSE NULL; END CASE; END IF; END LOOP; /* Print the results. */ RETURN lv_retval; END; / |
There are three test cases for this function:
- The first use-case checks whether the input parameter is a sparsely or densely populated list:
DECLARE /* Declare an input variable of three or more elements. */ lv_list TRE := tre('Berlin','25','09-May-1945','45'); /* Declare a variable to hold the compound type values. */ lv_struct STRUCT; BEGIN /* Make the set sparsely populated. */ lv_list.DELETE(2); /* Test the cast_strings function. */ lv_struct := cast_strings(lv_list); /* Print the values of the compound variable. */ dbms_output.put_line(CHR(10)); dbms_output.put_line('xstring ['||lv_struct.xstring||']'); dbms_output.put_line('xdate ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']'); dbms_output.put_line('xnumber ['||lv_struct.xnumber||']'); END; /
It should return:
xstring [Berlin] xdate [09-MAY-1945] xnumber [45]
The program defines two numbers and deletes the first number, which is why it prints the second number.
- The second use-case checks with a list of only one element:
SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate , xnumber , xstring FROM TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));
It should return:
XDATE XNUMBER XSTRING -------------------- ---------- -------------------- 25-NOV-1945 25 catch22
The program returns a structure with values converted into their appropriate data type.
- The third use-case checks with a list of two elements:
SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate , xnumber , xstring FROM TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945')) ,cast_strings(tre('31-APR-2017','1918','areodromes'))));
It should return:
XDATE XNUMBER XSTRING -------------------- ---------- -------------------- 25-NOV-1945 25 catch22 1918 areodromes
The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.
As always, I hope this helps those looking for how to solve this type of problem.