MacLochlainns Weblog

It started with a three part question: What’s the URL to run the DB Console for Oracle Database 11gR2 on Windows 7, and what’s the ORACLE_UNQNAME and why isn’t it defined by the installation. The first part is easy (shown further below), but the second and third parts were more involved.

The ORACLE_UNQNAME is an operating system environment variable that holds the database’s unique name value. You can find it with the following query as the SYSTEM user (through SQL*Plus):

SELECT name, db_unique_name FROM v$database;

By the way, it’s not set as a Windows environment variable by default. You would need to do that manually (an example of setting an environment variable is here). The Oracle Universal Installer (OUI) actually used it to configure the already running DB Console service (with a successful installation). Once there, it didn’t need to set it as a system-level environment variable.

You may be wondering what generated the question if there’s already a configured service. You encounter the error when dropping down to the command line. First, you verify that the ports are running with this command:

C:\> netstat -an | findstr /C:1158
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING

While this blog discusses the hard way to determine whether the DB Console is running, you can simply open the Windows Services to see whether the DB Console is running. Likewise, if you know the URL, enter it in your browser. Assuming you don’t know how to do either or are just a gluten for the command line, the rest of this post is important.

You can see your Windows services by typing services.msc in the Start->Run Command field. That way you don’t need to navigate the various links that differ between Windows releases.

Many know that you can check the status of the running DB Console with the emctl utility at the command line. It lets you find the URL that you should enter for the DB Console in a browser. This knowledge is where users encounter the problem with %ORACLE_UNQNAME% environment variable ($ORACLE_UNQNAME on Linux or Unix).

For example, running the following command raises an error that instructs you to set the %ORACLE_UNQNAME% environment variable. Although, it leaves many wondering what’s the right value to enter.

C:\> emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

If you object to using the Windows services to start and stop the OEM tool, you can do it at the command-line like the status example above. Having set the environment variables, you can start the DB console with this command-line syntax:

C:\> emctl start dbconsole

Having set the environment variables, you can stop the DB console with this command-line syntax:

C:\> emctl stop dbconsole

It’s not hard to find this information when you know how. While the error message complains about one environment variable, there are actually two environment values you need to set. They are: %ORACLE_HOSTNAME% and %ORACLE_UNQNAME%.

You can find them by navigating to the %ORACLE_HOME%\oc4j\j2ee\ folder (or directory). The file name of the DB Console file tells you the values for these environment variables because they’re embedded in the file’s name. A snapshot from Windows Explorer shows them both.

You can set these environment variables as shown below in command shell of Windows (Linux or Unix users should use terminal), and then successfully run emctl from the command line.

C:\>set ORACLE_HOSTNAME=localhost
 
C:\>set ORACLE_UNQNAME=orcl
 
C:\>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory C:\app\McLaughlinM\product\11.2.0\dbhome_1/localhost_orcl/sysman/log

If you’re using Linux or Unix, the export commands differ. You can check this other post for those. They under step 8 in that post.

You then enter the following URL in a browser to use the newly installed DB Console:

https://localhost:1158/em

The browser will prompt you with a security warning like the following:

Click the Add Exception button and you’ll see the following Windows dialog.

Having granted the exception, you arrive at the following credential web page. Connect as the SYSDBA using the SYS user’s account when you require extraordinary privileges. Doing so, shows a security risk in the console. You should connect as the SYSTEM user with NORMAL access generally, as shown below.

The following home page shows after your credentials are validated.

Hope that helps those trying to sort out running the DB Console and finding the magic %ORACLE_UNQNAME% value. Check this other blog post for instructions to reconfigure OEM.

Using a lowercase hostname is typical but I got sloppy on a Windows 7 installation, after all Windows is case insensitive, and I used a mixed case hostname. It raised an interesting error when installing Oracle Database 11g Release 2.

After verifying the ports were available, it required testing the ability to form a socket. The quickest way to do that was installing ActiveState Perl and test the socket.

Server-side Perl code (server.pl):

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

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 LocalAddr => 'mclaughlinmysql',
                 LocalPort => '5500',
                 Proto => 'tcp',
                 Listen => 5500,
                 Reuse => 5500);
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Set socket to listen for incoming request and loop while waiting.
my $new_sock = $sock->accept();
while(<$new_sock>) {
print $_; }
 
# Close the socket.
close($sock);

Server-side Perl code (client.pl):

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

# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 PeerAddr => 'mclaughlinmysql',
                 PeerPort => '5500',
                 Proto => 'tcp');
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Send string to socket.
print $sock "Hello there!\n";
close($sock);

These scripts help you check connectivity on a port. Run the server first in one command shell and the client second in another command shell. Then, the server-side program prints the “Hello There!” message sent from the client-side program.

You run the server with the following:

perl server.pl

and the client with this:

perl client.pl

Hope they help you verify viability through server ports.

It’s amazing the moving parts in Oracle Database 11g, and the ones that I don’t use after installation for a while often reach out to teach me something new. That’s what happened when I went to launch SQL Developer on my Windows 7 Professional 64-bit installation.

I got the message Unable to find a Java Virtual Machine, as shown in the image below. It really means unable to find a 32-bit Java 5 or Java 6 (1.6.0_4+) SDK home. Although the installation appeared fine, it pointed the SQL Developer configuration file to an incompatible 64-bit Java 7 SDK.

You fix this error by following these steps:

1. Open the sqldeveloper.conf file and check the SetJavaHome parameter value. You find the sqldeveloper.conf file in the following directory:

%ORACLE_HOME\sqldeveloper\sqldeveloper\bin

2. Install the 32-bit Java 6 (1.6.0_4+) SDK on the operating system if not installed already. You can check whether it’s installed by looking for it in the Program Files (x86) folder.
3. Change the value of the SetJavaHome parameter to point to the new 32-bit Java 6 home directory (or folder). The following change to line 18 in the sqldeveloper.conf file should fix it on your installation (provided that’s your version of the JVM).

18

SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Hope this helps those who run into the same issue.

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

• A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
• A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

1. Download and install the Java Software Development Kit (JSDK) for Java 6.
2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:

set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

5. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
6. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:

javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB

7. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
8. When it runs, you’ll see the following tabbed JOptionPane.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287

// --------------------------------------------------------------------
// WriteReadCLOB.java
// by Michael McLaughlin
//
// This code demonstrates reading a large text file and displaying
// the text stream in a JLabel in a JFrame.
//
// The UPDATE and SELECT statements have dependencies on the
// create_store.sql script.
// --------------------------------------------------------------------
 
// Java Application class imports.
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
 
// Generic JDBC imports.
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
 
// Oracle JDBC import.
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.pool.OracleDataSource;
 
// Include book libraries (available at publisher website).
import plsql.jdbc.DataConnectionPane;
import plsql.fileio.FileIO;
// -------------------------------------------------------------------/
public class WriteReadCLOB extends JFrame {
  // Define database connections.
  private String host;
  private String port;
  private String dbname;
  private String userid;
  private String passwd;
 
  // Define data connection pane.
  private DataConnectionPane message = new DataConnectionPane();
 
  // Construct the class.
  public WriteReadCLOB (String s) {
    super(s);
 
    // Get database connection values or exit.
    if (JOptionPane.showConfirmDialog(this,message
          ,"Set Oracle Connection String Values"
          ,JOptionPane.OK_CANCEL_OPTION) == 0) {
 
      // Set class connection variables.
      host = message.getHost();
      port = message.getPort();
      dbname = message.getDatabase();
      userid = message.getUserID();
      passwd = message.getPassword();
 
      // Print connection to console (debugging tool).
      message.getConnection();
 
      // Create a JPanel for data display.
      ManageCLOB panel = new ManageCLOB();
 
      // Configure the JPanel.
      panel.setOpaque(true);
      setContentPane(panel);
 
      // Configure the JFrame.
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setLocation(100,100);
      pack();
      setVisible(true); }
    else
      System.exit(1); }
  // -------------------------------------------------------------------/
  private class ManageCLOB extends JPanel {
 
    // Define display variables.
    private String clobText;
    private JScrollPane scrollPane;
    private JTextArea textArea;
    // -----------------------------------------------------------------/
    public ManageCLOB () {
      // Set layout manager.
      super(new GridLayout(1,0));
 
      // Assign file read to String.
      clobText = FileIO.openFile(FileIO.findFile(this));
 
      // Insert record before querying it.
      if (clobText.length() > 0) {
        if (insertClob(host,port,dbname,userid,passwd,clobText))
          clobText = getQuery(host,port,dbname,userid,passwd);
        else
          clobText = null; }
      else
        System.exit(2);
 
      // Construct text area and format it.
      textArea = new JTextArea(clobText);
      textArea.setEditable(false);
      textArea.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14));
      textArea.setLineWrap(true);
      textArea.setRows(10);
      textArea.setSize(400,100);
      textArea.setWrapStyleWord(true);
 
      // Put the image in container, and add label to panel.
      scrollPane = new JScrollPane(textArea);
      add(scrollPane); }
    // ---------------------------------------------------------------/
    private Boolean insertClob(String host,String port,String dbname
                              ,String user,String pswd,String fileString) {
 
      try
      {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Create statement.
        CallableStatement stmt =
          conn.prepareCall("UPDATE item "+
                           "SET    item_desc = ? "+
                           "WHERE  item_title = "+
                           "'The Lord of the Rings - Fellowship of the Ring'"+
                           "AND    item_subtitle = 'Widescreen Edition'");
 
        // Set string into statement.
        stmt.setString(1,fileString);
 
        // Execute query.
        if (stmt.execute())
          conn.commit();
 
        // Close resources.
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return true; } // End of connection try-block.
 
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return false; }
        else {
          System.out.println(e.getMessage());
 
          // Return an empty String on error.
          return false; }}}
    // -----------------------------------------------------------------/
    private String getQuery(String host,String port,String dbname
                           ,String user,String pswd) {
 
      // Define method variables.
      char[] buffer;
      int count = 0;
      int length = 0;
      String data = null;
      String[] type;
      StringBuffer sb;
 
      try {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Define metadata object.
        DatabaseMetaData dmd = conn.getMetaData();
 
        // Create statement.
        Statement stmt = conn.createStatement();
 
        // Execute query.
        ResultSet rset =
          stmt.executeQuery(
            "SELECT item_desc " +
            "FROM   item " +
            "WHERE  item_title = " +
            "'The Lord of the Rings - Fellowship of the Ring'"+
            "AND    item_subtitle = 'Widescreen Edition'");
 
        // Get the query metadata, size array and assign column values.
        ResultSetMetaData rsmd = rset.getMetaData();
        type = new String[rsmd.getColumnCount()];
        for (int col = 0;col < rsmd.getColumnCount();col++)
          type[col] = rsmd.getColumnTypeName(col + 1);
 
        // Read rows and only CLOB data type columns.
        while (rset.next()) {
          for (int col = 0;col < rsmd.getColumnCount();col++) {
            if (type[col] == "CLOB") {
              // Assign result set to CLOB variable.
              Clob clob = rset.getClob(col + 1);
 
              // Check that it is not null and read the character stream.
              if (clob != null) {
                Reader is = clob.getCharacterStream();
 
                // Initialize local variables.
                sb = new StringBuffer();
                length = (int) clob.length();
 
                // Check CLOB is not empty.
                if (length > 0) {
 
                  // Initialize control structures to read stream.
                  buffer = new char[length];
                  count = 0;
 
                  // Read stream and append to StringBuffer.
                  try {
                    while ((count = is.read(buffer)) != -1)
                      sb.append(buffer);
 
                    // Assign StringBuffer to String.
                    data = new String(sb); }
                  catch (Exception e) {} }
                else
                  data = (String) null; }
              else
                data = (String) null; }
            else {
              data = (String) rset.getObject(col + 1); }}}
 
        // Close resources.
        rset.close();
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return data; }
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return data; }
        else {
          System.out.println(e.getMessage());
          return data; }}
        finally {
          if (data == null) System.exit(1); }}}
  // -----------------------------------------------------------------/
  public static void main(String[] args) {
    // Define window.
    WriteReadCLOB frame = new WriteReadCLOB("Write & Read CLOB Text"); }}

Six years ago, I wrote Oracle Database 10g Express Edition PHP Web Programming for the release of the express edition. It was a lot of fun to write because I enjoy the PHP programming language, but unfortunately sales didn’t measure up too well. That’s probably because the population of PHP developers working with Oracle was small.

Today it seems there are more PHP developers working with Oracle 11g. While the population of PHP community for Oracle 11g is still smaller than for MySQL, it continues to grow year-over-year.

The FREE Underground PHP and Oracle Manual can help those converting PHP to run in the Oracle Call Interface, which is the replacement for MySQLi Interface. Chris Jones (an Oracle Open Source Product Manager) and Alison Holloway (an Oracle Senior Product Manager) write and maintain this book. It’s a great place to start if you’re migrating to Oracle Database 11g from MySQL.

In my database class, students write solutions as group exercises against the Oracle 11g XE database and then they port the solution individually to the MySQL 5.5 database. One of the students copied over a query like the one below to MySQL (a query used to track the expected number of row returns).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population);

It got an error they didn’t understand:

ERROR 1248 (42000): Every derived TABLE must have its own alias

Providing a dt query alias fixes the problem in MySQL for the following query. The fact that it was just an alias was a revelation to the student. That’s because Oracle databases don’t require aliases for inline views (what Oracle calls MySQL derived tables and Microsoft calls a Common Table Expression (CTE)).

SELECT   COUNT(*)
FROM    (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) dt;

MySQL requires that every derived table have its own alias. This make sense when you think about query optimization engines work, but that’s a story for a much longer post.

You can find more about inline views, derived tables, and common table expressions in this older post of mine. This post was promised so that future students can simply google and answer rather than ask me or a class tutor.

Somebody wanted to understand why you can backquote a single wildcard operator (that’s the underscore _ character) in MySQL, but can’t in Oracle. The answer is you can in Oracle when you know that you required an additional clause.

While I prefer using regular expression resolution, the LIKE operator is convenient. Here’s an example of backquoting an underscore in MySQL, where it looks for any string with an underscore anywhere in the string:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%';

You can gain the same behavior in Oracle by appending the ESCAPE '\' clause, like this:

SELECT   common_lookup_type
FROM     common_lookup
WHERE    common_lookup_type LIKE '%\_%' ESCAPE '\';

The ESCAPE '\' clause is one of those Oracle details that often gets lost. It only works when the SQL*Plus ESCAPE parameter is set to OFF.

The SQL*Plus ESCAPE parameter default value is a backslash. and when the ESCAPE parameter is enabled a statement like this raises the following exception:

ERROR at line 3:
ORA-01425: escape CHARACTER must be CHARACTER string OF LENGTH 1

If you drop the ESCAPE '\' clause with the ESCAPE parameter enabled it will return all rows from the table not just those strings with an underscore in the string. Hope this helps if need to look for an underscore in a table.

A question posed by a student: “Why are Oracle Native Dynamic SQL (NDS) USING clause parameters IN, IN OUT, or OUT when the RETURNING INTO clause manages output values?” It a great question, isn’t it? The followup question was also great, “How do you implement an example of NDS IN OUT parameters?”

The answer is two fold. First, you should use the USING clause for parameter list input values and the RETURNING INTO clause for return values whenever possible. Second, when it’s not possible you’re generally passing parameters into and out of an NDS PL/SQL anonymous block.

The basic prototype for passing and retrieving values from an NDS statement is:

EXECUTE IMMEDIATE sql_stmt
  USING { IN | IN OUT | OUT } local_variable [, ...]
  RETURNING INTO { IN OUT | OUT } local_variable [, ...];

A quick and hopefully fun example is this parody on Marvel’s The Avengers. The program creates an anonymous block with a super hero of Thor and super villain of Loki, then it uses a USING clause with IN OUT parameters to an anonymous block statement. That’s basically the trick to how you use IN OUT parameters in NDS statements.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- Enable SERVEROUTPUT.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Declare an anonymous testing block.
DECLARE
 
  -- Declare two local variables.
  lv_super_hero     VARCHAR2(20) := 'Thor';
  lv_super_villain  VARCHAR2(20) := 'Loki';
 
  -- Declare a null statement variable.
  lv_stmt  VARCHAR2(32767);
 
  -- Declare a local procedure to parse the NDS block.
  PROCEDURE print_code_block (pv_block VARCHAR2) IS
    -- Declare local parsing variables.
    lv_length   INTEGER := 1;
    lv_start    INTEGER := 1;
    lv_end      INTEGER := 1;
  BEGIN
    -- Read line by line on a line return character.
    WHILE NOT (lv_end = 0) LOOP
      -- Check for line returns.
      lv_end := INSTR(lv_stmt,CHR(10),lv_start);
      -- Check whether line return has been read.
      IF NOT lv_end = 0 THEN     
        -- Reset the ending substring value and print substring.
        lv_end := INSTR(lv_stmt,CHR(10),lv_start);
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,lv_end - lv_start));
      ELSE
        -- Print the last substring with a semicolon and exit the loop.      
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,LENGTH(lv_stmt) - lv_start)||';');
      END IF;
      -- Reset the beginning of the string.
      lv_start := lv_end + 1;      
    END LOOP;    
  END print_code_block;
 
BEGIN
 
  -- Demonstrate good triumps over evil.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
  -- Assign the anonymous block to the local statement variable.
  lv_stmt := 'DECLARE'||CHR(10)
          || '  lv_super_hero     VARCHAR2(20);'||CHR(10)
          || '  lv_super_villain  VARCHAR2(20);'||CHR(10)
          || 'BEGIN'||CHR(10)
          || '  lv_super_hero '||CHR(58)||'= :pv_super_hero;'||CHR(10)
          || '  lv_super_villain '||CHR(58)||'= :pv_super_villain;'||CHR(10)
          || '  :pv_super_hero '||CHR(58)||'= lv_super_villain;'||CHR(10)
          || '  :pv_super_villain '||CHR(58)||'= lv_super_hero;'||CHR(10)
          || 'END;';
 
  -- Run the NDS program.
  EXECUTE IMMEDIATE lv_stmt USING IN OUT lv_super_hero
                                , IN OUT lv_super_villain;
 
  -- Print the diagnostic code block, that's why it used line returns afterall.  
  dbms_output.put_line('--------------------------------------------------');
  print_code_block(lv_stmt);
  dbms_output.put_line('--------------------------------------------------');
 
  -- Demonstrate the world is upside down without Johnny Depp playing Capt'n Jack.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
END;
/

You’ll get the following printed output:

The good Thor beats up the bad Loki!
--------------------------------------------------
| DECLARE
|   lv_super_hero     VARCHAR2(20);
|   lv_super_villain  VARCHAR2(20);
| BEGIN
|   lv_super_hero := :pv_super_hero;
|   lv_super_villain := :pv_super_villain;
|   :pv_super_hero := lv_super_villain;
|   :pv_super_villain := lv_super_hero;
| END;
--------------------------------------------------
The good Loki beats up the bad Thor!

As always, I hope it helps you understand the concept of the USING clause with IN OUT parameters but I hope there’s always better way.

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

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

PLS-00999: Implementation Restriction (may be temporary)

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

You can implement a schema RESULT_CACHE function like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE FUNCTION full_name
( pv_first_name   VARCHAR2
, pv_last_name    VARCHAR2 )
RETURN VARCHAR2 RESULT_CACHE IS
BEGIN  
  RETURN pv_first_name || ' ' || pv_last_name;
END full_name;
/

You would call it like this from a query:

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

You can declare a published package RESULT_CACHE function like this:

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE;
END cached_function;
/

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

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE PACKAGE BODY cached_function IS
  FUNCTION full_name
  ( pv_first_name   VARCHAR2
  , pv_last_name    VARCHAR2 )
  RETURN VARCHAR2 RESULT_CACHE IS
  BEGIN  
    RETURN pv_first_name || ' ' || pv_last_name;
  END full_name; 
END cached_function;
/

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

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

I hope this answers the question.

Somebody asked me for a useful example of Oracle 11gR2′s new analytical LISTAGG function that uses a WITHIN GROUP syntax. They’d noticed an update to the askTom that showed how to use it. This post shows how to list values without a displayed aggregation column and how to use a JOIN and GROUP BY clause with the new analytical feature.

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

COLUMN list      FORMAT A10
COLUMN last_name FORMAT A10
COLUMN names     FORMAT A42
COLUMN members   FORMAT 9,990
 
 
SELECT   m.account_number AS account
,        c.last_name AS last_name
,        LISTAGG(c.first_name||DECODE(c.middle_name,NULL,NULL,' '||SUBSTR(c.middle_name,1,1)||'.'),', ')
           WITHIN GROUP (ORDER BY 2) AS names
,        COUNT(*) AS members
FROM     contact c INNER JOIN member m USING (member_id)
GROUP BY m.account_number
,        c.last_name
ORDER BY c.last_name;

It produces the following output:

ACCOUNT    LAST_NAME  NAMES                                      MEMBERS
---------- ---------- ------------------------------------------ -------
SLC-000021 Jonah      Gretelz S.                                       1
SLC-000020 Moss       Jane W.                                          1
SLC-000023 Nathan     Smith B.                                         1
SLC-000024 Potter     Albus S., Ginny, Harry, James S., Lily L.        5
SLC-000022 Royal      Jennifer E.                                      1
SJC-000003 Sweeney    Ian M., Matthew, Meaghan                         3
SJC-000002 Vizquel    Doreen, Oscar                                    2
SLC-000018 Ward       Clinton G.                                       1
SLC-000019 Ward       Brandt H.                                        1
SJC-000001 Winn       Brian, Randi                                     2
 
10 rows selected.

I also found some existing examples you might like, at Oracle-Base, and there they’re showing you how to make it work in prior releases of the database without the new LISTAGG function.

Let’s say you just wanted a concatenated list of users, you could use the following in-line view approach:

1
2
3
4
5

SELECT   list.names
FROM    (SELECT   'List' AS list
         ,        LISTAGG(last_name ||', '||first_name||DECODE(middle_name,NULL,NULL,' '||SUBSTR(middle_name,1,1)||'.'),'; ')
                  WITHIN GROUP (ORDER BY 2) AS names
         FROM     contact) list;

As always, I hope this helps somebody.