MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle XE’ Category

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*Plus Tricks

with 3 comments

Have you ever wondered how to leverage substitution variables in anonymous block programs? There are several tricks that you can use beyond passing numeric and string values to local variable. The generic default appears to take a number unless you cast it as a string but that’s not really the whole story. The first two are standard examples of how to use numeric and string substitution values.

The following accept a numeric substitution value:

1
2
3
4
5
6
7
8
9
10
DECLARE
  lv_input  NUMBER;
BEGIN
  /* Assign substitution value to local variable. */
  lv_input := &input;
 
  /* Print the local variable. */
  dbms_output.put_line('['||lv_input||']');
END;
/

The following accept a string substitution value, casts the input as a string, assigns the string value to a 4,000 character length local variable, checks whether the 4,000 character length is greater than 10, and assigns the first 10 characters to the lv_parse_input variable:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
  lv_unparsed_input  VARCHAR2(4000);
  lv_parsed_input    VARCHAR2(10);
BEGIN
  /* Assign substitution value to local variable. */
  lv_unparsed_input := '&input';
 
  /* Check size of input value. */
  IF LENGTH(lv_unparsed_input) > 10 THEN
    lv_parsed_input := SUBSTR(lv_unparsed_input,1,10);
  END IF;
 
  /* Print the local variable. */
  dbms_output.put_line('Print {lv_parsed_input}: ['||lv_parsed_input||']');
END;
/

Next, let’s examine two tricks. The first passes a case insensitive variable name and the second passes a case sensitive variable name as a parameter to an anonymous block program.

This declares an anonymous block program that uses a substitution value as a variable name:

1
2
3
4
5
6
7
DECLARE
  mine  VARCHAR2(10) := 'Default';
BEGIN
  /* Print the local variable's value. */
  dbms_output.put_line('Print {mine} variable value: ['||&input||']');
END;
/

When you run the anonymous block, you’re prompted for an input variable. You provide a case insensitive variable name as the input value:

Enter value for input: MINE
old   5:   dbms_output.put_line('['||&input||']');
new   5:   dbms_output.put_line('['||MINE||']');
Print {mine} variable value: [Default]

The downside of this approach, yields an ORA-06550 and PLS-00201 exception. Neither of these can be caught because Oracle raises the errors during parsing when the variable name isn’t a 100% case insensitive match. The same type of problem occurs in the next example when the input variable isn’t a 100% case sensitive match.

You can rewrite the program to handle case insensitive variables like this:

1
2
3
4
5
6
7
DECLARE
  "Mine"  VARCHAR2(10) := 'Default';
BEGIN
  /* Print the local variable's value. */
  dbms_output.put_line('Print {mine} variable value: ['||"&input"||']');
END;
/

When you run the anonymous block, you’re prompted for an input variable. You provide a case sensitive variable name as the input value:

Enter value for input: Mine
old   5:   dbms_output.put_line('['||&input||']');
new   5:   dbms_output.put_line('['||"Mine"||']');
Print {Mine} variable value: [Default]

Hope this helps those looking for a solution.

Written by maclochlainn

September 24th, 2015 at 1:19 am

Use an object in a query?

without comments

Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:

SELECT	 instance.get_type()
         *
ERROR AT line 4:
ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier

The problem is how Oracle treats runtime views, which appears to me as a casting error. Somewhat like the ORDER BY clause irregularity that I noted in July, the trick is complete versus incomplete syntax. The following query fails and generates the foregoing error:

1
2
3
4
SELECT instance.get_type() AS object_type
,      instance.to_string() AS object_content
FROM  (SELECT dependent()AS instance
       FROM   dual);

If you add a table alias, or name, to the runtime view on line 4, it works fine:

1
2
3
4
SELECT cte.instance.get_type() AS object_type
,      cte.instance.to_string() AS object_content
FROM  (SELECT dependent() AS instance
       FROM   dual) cte;

That is the trick. You use an alias for the query, which assigns the alias like a table reference. The reference lets you access instance methods in the scope of a query. Different columns in the query’s SELECT-list may return different results from different methods from the same instance of the object type.

You can also raise an exception if you forget the open and close parentheses for a method call to a UDT, which differs from how Oracle treats no argument functions and procedures. That type of error would look like this:

SELECT cte.instance.get_type AS object_type
       *
ERROR AT line 1:
ORA-00904: : invalid identifier

It is an invalid identifier because there’s no public variable get_type, and a method is only found by using the parenthesis and a list of parameters where they’re required.

The object source code is visible by clicking on the expandable label below.

As always, I hope this helps those solving problems.

Written by maclochlainn

August 22nd, 2015 at 5:23 pm

Mac SQL Developer Install

without comments

This how you install SQL Developer on Mac OS Yosemite. The first thing you need to do is download and install Java 8, not Java 7 on your Mac OS Yosemite as suggested on some web sites. You can determine whether or not Java is installed by running the following command:

Mac-Pro-3:~ username$ java -version
No Java runtime present, requesting install.

You must accept the Java license to install Java 8 on the Mac OS X operating system:

YosemiteInstallJava_01

You have the option of installing the Java SDK or JDK. I’ve opted to install Netbeans 8 with JDK 8u45, as you can tell from the screen capture after you launched the file:

YosemiteInstallJava_02

It is a standard Mac OS installation, which is why I didn’t bother showing any dialog messages. After installing the Java JDK or SDK, you should download SQL Developer 4.1 from Oracle’s web site. Below is a screen shot of the Oracle download web page where I’ve accepted the license agreement:

SQLDeveloperDownload

If you attempt to launch the installation and you’ve set your Mac Security to the “Mac App Store and identified developers” setting, you should raise the following exception:

SQLDeveloperInstall_01

If you reset the Mac Security to an “Anywhere” setting, you can install Oracle SQL Developer on Yosemite. Just make sure you reset it to the “Mac App Store and identified developers” setting after you install SQL Developer.

If you launch SQL Developer with the Security “Anywhere” setting, it displays the following dialog:

SQLDeveloperInstall_02

After you launch the program, you will see the following progress dialog:

SQLDeveloperInstall_03

The last step of the installation launches SQL Developer, as shown below:

SQLDeveloperInstall_04

Click the Connections icon to create an initial connection, like the following:

SQLDeveloperInstall_05

After connecting to the database, you can write and execute a query as shown in the next screen capture:

SQLDeveloperInstall_06

As always, I hope that this helps those who require an example to install SQL Server on a Mac OS.

Written by maclochlainn

June 12th, 2015 at 3:08 am

Bash Arrays & Oracle

with 2 comments

Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.

If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.

I’ve copied the basic ERD for the example because of a request from a reader. In their opinion it makes cross referencing the two posts unnecessary.

LittleERDModel

To run the bash shell script, you’ll need the following SQL files, which you can see by clicking not he title below. There are several differences. For example, Oracle doesn’t support a DROP IF EXISTS syntax and requires you to write anonymous blocks in their PL/SQL language; and you must explicitly issue a QUIT; statement even when running in silent mode unlike MySQL, which implicitly issues an exit.

If you don’t have a sample test schema to use to test this script, you can create a sample schema with the following create_user.sql file. The file depends on the existence of a users and temp tablespace.

Click the link below to see the source code for a script that let’s you create a sample user account as the system user:

The following list_oracle.sh shell script expects to receive the username, password, and fully qualified path in that specific order. The script names are entered manually in the array because this should be a unit test script.

This is an insecure version of the list_oracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script.

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
#!/usr/bin/bash
 
# Assign user and password
username="${1}"
password="${2}"
directory="${3}"
 
echo "User name:" ${username}
echo "Password: " ${password}
echo "Directory:" ${directory}
 
# Define an array.
declare -a cmd
 
# Assign elements to an array.
cmd[0]="actor.sql"
cmd[1]="film.sql"
cmd[2]="movie.sql"
 
# Call the array elements.
for i in ${cmd[*]}; do
  sqlplus -s ${username}/${password} @${directory}/${i} > /dev/null
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/tables.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done
 
# Connect and pipe the query result minus errors and warnings to the while loop.
sqlplus -s ${username}/${password} @${directory}/result.sql 2>/dev/null |
 
# Read through the piped result until it's empty.
while IFS='\n' read actor_name; do
  echo $actor_name
done

The IFS (Internal Field Separator) works with whitespace by default. The IFS on lines 29 and 37 sets the IFS to a line return ('\n'). That’s the trick to display the data, and you can read more about the IFS in this question and answer post.

You can run the shell script with the following syntax:

./list_oracle.sh sample sample /home/student/Code/bash/oracle > output.txt

You can then display the results from the output.txt file with the following command:

cat output.txt command:

It will display the following output:

User name: sample
Password:  sample
Directory: /home/student/Code/bash/oracle
 
Table Name
------------------------------
MOVIE
FILM
ACTOR
 
Actors in Films
----------------------------------------
Chris Hemsworth, Thor
Chris Hemsworth, Thor: The Dark World
Chris Pine, Star Trek
Chris Pine, Star Trek into Darkness
Chris Pratt, Guardians of the Galaxy

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

Written by maclochlainn

May 21st, 2015 at 1:16 am

Leaf node queries

without comments

A reader posted A dynamic level limiting hierarchical query about Oracle’s hierarchical queries. They wanted to know how to capture only the hierarchy above the level where the first leaf node occurs. They gave me the following hierarchy map as an example:

               1                                    2
        +-------------+                       +-----------+
        |             |                       |           |      
        3             5                       4           6
    +---------+    +-----------+           +-----+    +------+
    |         |    |           |           |     |    |      |
    7         9    11          13          8     10   12     14
+-----+   +-----+  +--+    +-------+                       +-----+ 
|     |   |     |     |    |       |                       |     |
15    17  19    21    23   27      29                     16     18
                                                                 +---+
                                                                     |
                                                                     20

You can find the node values and hierarchical level with the following query:

SELECT   tt.child_id
,        LEVEL
FROM     test_temp tt
WHERE    CONNECT_BY_ISLEAF  = 1
START
WITH     tt.parent_id IS NULL 
CONNECT
BY PRIOR tt.child_id = tt.parent_id
ORDER BY 2;

We really don’t need the node values to solve the problem. We only need the lowest LEVEL value returned by the query, which is 3. The combination of the MIN and CONNECT_BY_ISLEAF functions let us solve this problem without writing a PL/SQL solution. The subquery returns the lowest level value, which is the first level where a leaf node occurs.

SELECT   LPAD(' ', 2*(LEVEL - 1)) || tt.child_id AS child_id
FROM     test_temp tt
WHERE    LEVEL <= (SELECT   MIN(LEVEL)
                   FROM     test_temp tt
                   WHERE    CONNECT_BY_ISLEAF  = 1
                   START
                   WITH     tt.parent_id IS NULL 
                   CONNECT
                   BY PRIOR tt.child_id = tt.parent_id)
START
WITH     tt.parent_id IS NULL
CONNECT
BY PRIOR tt.child_id = tt.parent_id;

It returns:

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

While I answered the question in a comment originally, it seemed an important trick that should be shared in its own post.

Written by maclochlainn

April 30th, 2015 at 4:31 pm

SQL Developer – Fedora

with 3 comments

This is the continuation of my efforts to stage an awesome Fedora developer’s instance. It shows you how to install Java 1.8 software development kit, which is nice to have. Though you can’t use Java 1.8 officially with Oracle SQL Developer 4.0.3 it is required for Oracle SQL Developer 4.1. Fortunately, the Oracle Product Manager, Jeff Smith has advised us that you can use Java 1.8 JDK with Oracle SQL Developer 4.0.3, and he’s written a comment to the blog post that it runs better with the Java 1.8 SDK.

After you install Oracle SQL Developer 4.0.3 or Oracle SQL Developer 4.1, you can watch Jeff Smith’s YouTube Video on SQL Developer 3.1 to learn how to use the basics of SQL Developer. I couldn’t find an updated version of the video for SQL Developer 4 but I didn’t try too hard.

You use yum as the root user to install Java SDK 1.8, much like my earlier Installing the Java SDK 1.7 and Java-MySQL Sample Program. The following command installs Java 8:

yum install -y java-1.8*

It produces the following output:

Loaded plugins: langpacks, refresh-packagekit
fedora/20/x86_64/metalink                                   |  18 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     
pgdg93                                                      | 3.6 kB  00:00     
updates/20/x86_64/metalink                                  |  16 kB  00:00     
updates                                                     | 4.9 kB  00:00     
(1/2): mysql-tools-community/20/x86_64/primary_db           |  21 kB  00:00     
(2/2): updates/20/x86_64/primary_db                         |  13 MB  00:09     
updates/20/x86_64/pkgtags
updates
(1/2): updates/20/x86_64/pkgtags                            | 1.4 MB  00:02     
(2/2): updates/20/x86_64/updateinfo                         | 1.9 MB  00:04     
Package 1:java-1.8.0-openjdk-headless-1.8.0.31-1.b13.fc20.x86_64 already installed and latest version
Package 1:java-1.8.0-openjdk-javadoc-1.8.0.31-1.b13.fc20.noarch already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package java-1.8.0-openjdk.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-accessibility.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-demo.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-devel.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
---> Package java-1.8.0-openjdk-src.x86_64 1:1.8.0.31-1.b13.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                          Arch   Version                  Repository
                                                                           Size
================================================================================
Installing:
 java-1.8.0-openjdk               x86_64 1:1.8.0.31-1.b13.fc20    updates 201 k
 java-1.8.0-openjdk-accessibility x86_64 1:1.8.0.31-1.b13.fc20    updates  12 k
 java-1.8.0-openjdk-demo          x86_64 1:1.8.0.31-1.b13.fc20    updates 1.9 M
 java-1.8.0-openjdk-devel         x86_64 1:1.8.0.31-1.b13.fc20    updates 9.2 M
 java-1.8.0-openjdk-src           x86_64 1:1.8.0.31-1.b13.fc20    updates  45 M
 
Transaction Summary
================================================================================
Install  5 Packages
 
Total download size: 56 M
Installed size: 92 M
Downloading packages:
(1/5): java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20 |  12 kB  00:00     
(2/5): java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64.rpm    | 201 kB  00:02     
(3/5): java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64.r | 1.9 MB  00:03     
(4/5): java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64. | 9.2 MB  00:07     
(5/5): java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64.rp |  45 MB  05:05     
--------------------------------------------------------------------------------
Total                                              187 kB/s |  56 MB  05:05     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : 1:java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64              1/5 
  Installing : 1:java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64        2/5 
  Installing : 1:java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64         3/5 
  Installing : 1:java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20.x86   4/5 
  Installing : 1:java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64          5/5 
  Verifying  : 1:java-1.8.0-openjdk-devel-1.8.0.31-1.b13.fc20.x86_64        1/5 
  Verifying  : 1:java-1.8.0-openjdk-demo-1.8.0.31-1.b13.fc20.x86_64         2/5 
  Verifying  : 1:java-1.8.0-openjdk-1.8.0.31-1.b13.fc20.x86_64              3/5 
  Verifying  : 1:java-1.8.0-openjdk-accessibility-1.8.0.31-1.b13.fc20.x86   4/5 
  Verifying  : 1:java-1.8.0-openjdk-src-1.8.0.31-1.b13.fc20.x86_64          5/5 
 
Installed:
  java-1.8.0-openjdk.x86_64 1:1.8.0.31-1.b13.fc20                               
  java-1.8.0-openjdk-accessibility.x86_64 1:1.8.0.31-1.b13.fc20                 
  java-1.8.0-openjdk-demo.x86_64 1:1.8.0.31-1.b13.fc20                          
  java-1.8.0-openjdk-devel.x86_64 1:1.8.0.31-1.b13.fc20                         
  java-1.8.0-openjdk-src.x86_64 1:1.8.0.31-1.b13.fc20                           
 
Complete!

Then, you go to Oracle’s SQL Developer 4.0.3 web page or Oracle’s Beta SQL Developer 4.1 web page and download the SQL Developer RPM. At the time of writing, you download the following SQL Developer 4.0.3 RPM:

sqldeveloper-4.0.3.16.84-1.noarch.rpm

Assuming you download the sqldeveloper-4.0.3.16.84-1.noarch.rpm file to the student user’s account. It will download into the /home/student/Downloads directory. You run the SQL Developer RPM file with the following syntax as the root user:

rpm -Uhv /home/student/Downloads/sqldeveloper-4.0.3.16.84-1.noarch.rpm

Running the SQL Developer RPM produces the following output:

Preparing...                          ################################# [100%]
Updating / installing...
   1:sqldeveloper-4.0.3.16.84-1       ################################# [100%]

You can now run the sqldeveloper.sh file as the root user with the following syntax:

/opt/sqldeveloper/sqldeveloper.sh

At this point, it’s important to note that my download from the Oracle SQL Developer 4.1 page turned out to be SQL Developer 4.0.3. It prompts you for the correct Java JDK, as shown below. You may opt to enter the path to the Java JDK 1.8 for SQL Developer 4.1 because until today you downloaded the Oracle SQL Developer 4.0.3 version from the Oracle SQL Developer 4.1 page. Naturally, the Oracle SQL Developer 4.1 instructions say to use the Java 1.8 JDK on the RPM for Linux Installation Notes web page, as shown below:

SQLDevRPMLinuxNotes

If you assume from the instructions on the Oracle instruction page above that Oracle SQL Developer 4.0.3 and Oracle SQL Developer 4.1 support Java 1.8 JDK, you may enter the location for the Java JDK 1.8 when prompted. Jeff Smith, the Product Manager wrote this blog post on Oracle SQL Developer 4: Windows and the JDK. Unfortunately, you’ll see the following message if you attempt to run Oracle SQL Developer 4.0.3 with the Java 1.8 SDK at the command-line:

 Oracle SQL Developer
 Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.
 
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /root/.sqldeveloper/4.0.0/product.conf
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.31.x86_64
OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=256M; support was removed in 8.0

It also raises the following error message dialog:

SQLDev_JVMErrorMsg

Text version of Unsupported JDK Version error message:

You are attempting to run with Java 1.8.0_31.

Running this product is supported with a minimum Java version of 1.7.0_51 and a maximum version less than 1.8.

Update the SetJavaHome in “/root/.sqldeveloper/4.0.0/product.conf” to point to another Java.

This produce will not be supported, and may not run correctly if you proceed. Continue anyway?

The error dialog message tells us that the instructions on the RPM for Linux Installation Notes web page can be misleading. You really need to use the Java JDK 1.7 to be supported officially, but you can safely ignore the error.

If you want a certified component, leave the “Skip This Message Next Time” checkbox unchecked and click the “No” button to continue. At this point, there’s no automatic recovery. You need to open the following file:

/root/.sqldeveloper/4.0.0/product.conf

You need to change the SetJavaHome parameter in the file to the following:

# SetJavaHome /path/jdk
SetJavaHome /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64

After making the change, you can re-run the sqldeveloper.sh shell as follows:

/opt/sqldeveloper/sqldeveloper.sh

It launches the following dialog message:

SQLDeveloperInstall01

The installation pauses to ask you if you want to transfer an existing SQL Developer configuration by raising the following dialog. Assuming this is a new installation, the installer won’t find a prior configuration file. You need to click the “No” button to proceed.

SQLDevInstallPreferences

The installation continues and launches SQL Developer. The first time launch shows you the following Oracle Usage Tracking dialog. If you don’t want your use monitored, uncheck the “Allow automated usage reporting to Oracle” checkbox. Click the “OK” button to continue.

SQLDevUsageTracking

After dismissing the Oracle Usage Tracking dialog, you see the SQL Developer environment:

SQLDeveloper

After installing SQL Developer in the root account, you can install it as the student user. You use this command as the student user:

/opt/sqldeveloper/sqldeveloper.sh

It returns the following error because it’s the second installation and SQL Developer doesn’t prompt you to configure the user’s product.conf file with the working JDK location:

 Oracle SQL Developer
 Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.
 
Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/student/.sqldeveloper/4.0.0/product.conf
Error:  Unable to get APP_JAVA_HOME input from stdin after 10 tries

You need to edit the /home/student/.sqldeveloper/4.0.0/product.conf file, and add the following line to the file:

# SetJavaHome /path/jdk
SetJavaHome /usr/lib/jvm/java-1.7.0-openjdk-1.7.0.79-2.5.5.0.fc20.x86_64

Now, you can launch SQL Developer with the following command:

/opt/sqldeveloper/sqldeveloper.sh

Alternatively, you can add the following alias to the student user’s .bashrc file:

# Set alias for SQL Developer tool.
alias sqldeveloper="/opt/sqldeveloper/sqldeveloper.sh"

You can now launch the SQL Developer tool, like this as the student user:

sqldeveloper

You see the following when SQL Developer launches:

SQLDevInterface

As always, I hope this helps those trying to sort out installing SQL Developer on a Fedora server.

Written by maclochlainn

April 25th, 2015 at 2:38 am

Oracle Cleanup a Schema

with one comment

Back in January 2014, I wrote a script to cleanup an Oracle student schema. It worked well until I started using APEX 4 in my student schema. You create the following 75 objects when you create an APEX 4 schema.

OBJECT TYPE    TOTAL
------------ -------
TABLE		  17
INDEX		  28
SEQUENCE	   5
TRIGGER 	  14
LOB		   9
FUNCTION	   2

Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a student workspace:

BEGIN
  FOR i IN (SELECT    object_name
            ,         object_type
            ,         last_ddl_time
            FROM      user_objects
            WHERE     object_name NOT IN
                       ('APEX$_WS_WEBPG_SECTION_HISTORY','APEX$_WS_WEBPG_SECTIONS_T1'
                       ,'APEX$_WS_WEBPG_SECTIONS_PK','APEX$_WS_WEBPG_SECTIONS'
                       ,'APEX$_WS_WEBPG_SECHIST_IDX1','APEX$_WS_TAGS_T1'
                       ,'APEX$_WS_TAGS_PK','APEX$_WS_TAGS_IDX2','APEX$_WS_TAGS_IDX1'
                       ,'APEX$_WS_TAGS','APEX$_WS_ROWS_T1','APEX$_WS_ROWS_PK'
                       ,'APEX$_WS_ROWS_IDX','APEX$_WS_ROWS','APEX$_WS_NOTES_T1'
                       ,'APEX$_WS_NOTES_PK','APEX$_WS_NOTES_IDX2','APEX$_WS_NOTES_IDX1'
                       ,'APEX$_WS_NOTES','APEX$_WS_LINKS_T1','APEX$_WS_LINKS_PK'
                       ,'APEX$_WS_LINKS_IDX2','APEX$_WS_LINKS_IDX1','APEX$_WS_LINKS'
                       ,'APEX$_WS_HISTORY_IDX','APEX$_WS_HISTORY','APEX$_WS_FILES_T1'
                       ,'APEX$_WS_FILES_PK','APEX$_WS_FILES_IDX2','APEX$_WS_FILES_IDX1'
                       ,'APEX$_WS_FILES','APEX$_ACL_T1','APEX$_ACL_PK','APEX$_ACL_IDX1'
                       ,'APEX$_ACL','CUSTOM_AUTH','CUSTOM_HASH','DEPT','EMP'
                       ,'UPDATE_ORDER_TOTAL')
            AND NOT ((object_name LIKE 'DEMO%' OR
                      object_name LIKE 'INSERT_DEMO%' OR
                      object_name LIKE 'BI_DEMO%') AND
                      object_type IN ('TABLE','INDEX','SEQUENCE','TRIGGER'))
            AND NOT (object_name LIKE 'SYS_LOB%' AND object_type = 'LOB')
            AND NOT (object_name LIKE 'SYS_C%' AND object_type = 'INDEX')
            ORDER BY object_type DESC) LOOP
 
    /* Drop types in descending order. */
    IF i.object_type = 'TYPE' THEN
 
      /* Drop type and force operation because dependencies may exist. Oracle 12c
         also fails to remove object types with dependents in pluggable databases
         (at least in release 12.1). Type evolution works in container database
         schemas. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE';
 
    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         don't prevent the action. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE';
 
      /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated
         sequence values because dropping the table doesn't automatically 
         remove them from the active session.
         CRITICAL: Remark out the following when working in Oracle Database 11g. */
      EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
 
    ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN
 
      /* A system generated LOB column or INDEX will cause a failure in a
         generic drop of a table because it is listed in the cursor but removed
         by the drop of its table. This NULL block ensures there is no attempt
         to drop an implicit LOB data type or index because the dropping the
         table takes care of it. */
      NULL;
 
    ELSE
 
      dbms_output.put_line('DROP '||i.object_type||' '||i.object_name||';');
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

As always, I hope this helps others.

Written by maclochlainn

April 19th, 2015 at 7:13 pm

OUT Parameter Trick

without comments

Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an OUT-only mode parameter from a procedure.

That’s a great question, and it’s actually simple once you understand the difference between Oracle object types and other data types. Oracle object types must always be initiated before you use them, which means you must initialize any OUT-only mode parameters at the top of your execution section, like this:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE PROCEDURE reset_troll
( pv_troll OUT TROLL_OBJECT ) IS
  /* Troll default name. */
  lv_troll_name  VARCHAR2(20) := 'Bert';
BEGIN
  /* Initialize the incoming parameter by allocating memory to it. */
  pv_troll := troll_object();
 
  /* Set the name to something other than the 'Tom' default value. */
  pv_troll.set_troll(lv_troll_name);
END reset_troll;
/

Line 7 shows you the trick, initialize the incoming parameter because there isn’t an incoming parameter for an OUT-only mode parameter. The calling parameter to an OUT-only mode parameter is only a reference where PL/SQL will copy the internal object reference. While the calling parameter has been initialized, the reference to the call parameter’s object is where the internal object will be copied. The local program must first ensure a new memory location for a new instance of the object type before it can act on or return an object instance to the external reference. More or less, the internal object is copied to the calling object instance’s memory location when the procedure completes its execution.

Here’s the source code for the troll_object object type and body:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE OR REPLACE TYPE troll_object IS OBJECT
( troll VARCHAR2(20)
, CONSTRUCTOR FUNCTION troll_object
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION troll_object
  ( troll VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_troll RETURN VARCHAR2
, MEMBER PROCEDURE set_troll (troll VARCHAR2)
, MEMBER FUNCTION to_string RETURN VARCHAR2)
INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE TYPE BODY troll_object IS
  /* Default no-argument constructor. */
  CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT IS
    troll TROLL_OBJECT := troll_object('Tom');
  BEGIN
    SELF := troll;
    RETURN;
  END troll_object;
  /* Single argument constructor. */
  CONSTRUCTOR FUNCTION troll_object (troll VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    SELF.troll := troll;
    RETURN;
  END troll_object;
  /* A getter function. */
  MEMBER FUNCTION get_troll RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.troll;
  END get_troll;
  /* A setter procedure. */
  MEMBER PROCEDURE set_troll (troll VARCHAR2) IS
  BEGIN
    SELF.troll := troll;
  END set_troll;
  /* A function that returns the formatted object type's contents. */
  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello '||SELF.troll;
  END to_string;
END;
/

You can test the reset_troll procedure with the following anonymous block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* Enable printing from a PL/SQL block. */
SET SERVEROUTPUT ON SIZE UNLIMITED
/* Anonymous testing block. */
DECLARE
  lv_troll  TROLL_OBJECT := troll_object('Bill');
BEGIN
  dbms_output.put_line('--------------------');
  /* Prints 'Hello William' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
  reset_troll(lv_troll);
  /* Prints 'Hello Bert' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
END;
/

If you remark out line 7 from the reset_troll procedure, you’d raise the following exception by the call on line 10 because the local object hasn’t been instantiated (given life). It means there’s no memory location allocated for the instantiated (instance of an object type).

--------------------
Hello Bill
--------------------
DECLARE
*
ERROR at line 1:
ORA-30625: method dispatch ON NULL SELF argument IS disallowed
ORA-06512: at "VIDEO.RESET_TROLL", line 10
ORA-06512: at line 8

Hope this helps those trying to solve the same problem.

Written by maclochlainn

December 19th, 2013 at 6:10 pm

Object Table Function View

with 2 comments

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

1
2
3
4
CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1
2
CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

Lastly, you create an object table 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
38
39
CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3
CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

Written by maclochlainn

March 17th, 2013 at 10:59 pm