On Connecting, Pivoting, and Learning New Things

Our technologist bypasses TNSNAMES.ORA, turns rows to columns, and meets the NO_DATA_NEEDED exception.

By Tom Kyte

July/August 2010

I am trying to use SQL*Plus to connect to an Oracle Database instance directly, using just the connect string (that is, without referencing tnsnames.ora).

I have the following in my tnsnames.ora:

POD=(DESCRIPTION=(ADDRESS_LIST=....))

and I can connect to the database with

sqlplus USER/PASSWORD@POD

However, if I try to connect with

sqlplus USER/PASSWORD@//
HOST.NAME:1521/SVC

I see the following error message:

ORA-12154: TNS: could not resolve the connect identifier specified

Is there any way I can connect to the database without referring to tnsnames.ora?

There are multiple answers to this—I’ll present two. First, you seem to be trying to use the easy connect naming method introduced with Oracle Database 10g. This enables you to connect to a database—without using a TNS connect string—by specifying the host and database service to connect to.

So you are receiving this error:

$ sqlplus scott/tiger@//host/ora11g
SQL*Plus: Release 11.2.0.1.0
Production on Fri Apr 2 10:25:12
Copyright (c) 1982, 2009, Oracle.
All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the
connect identifier specified

That means that the Oracle client software could not translate that connect string into something that enabled you to find the listener. The problem lies in your sqlnet.ora file: you need to let the client software know that you want to use EZCONNECT as one of your naming methods. If you add

NAMES.DIRECTORY_PATH=
    (TNSNAMES, EZCONNECT)

to your sqlnet.ora configuration file on the client (see download.oracle.com/docs/cd/E11882_01/network.112/e10836/naming.htm#sthref473 for all the details), you’ll find that this method now works:

$ sqlplus scott/tiger@//host/ora11
g
SQL*Plus: Release 11.2.0.1.0
Production on Fri Apr 2 10:30:33
Copyright (c) 1982, 2009, Oracle.
All rights reserved.
Connected to:
Oracle Database 11
g Enterprise
Edition Release 11.2.0.1.0 -
Production  With the Partitioning,
OLAP, Data Mining and Real
Application Testing options

Alternatively, if you want to connect without both a sqlnet.ora file and a tnsnames.ora file, you can put the details you would normally place in your tnsnames.ora file right on the command line:

$ sqlplus scott/tiger@'(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)
(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SERVER=DEDICATED)
(service_name=ora11gr2)))'

This works nicely when you have no control over the configuration files at all and want to connect to a known host/instance. You should note that in real life, there would be no line breaks, there would be no spaces in the SQL Plus command line, and you would have to escape special characters based on the shell (or command prompt) you were using.

Please show an example of a pivot query—to turn rows into columns. Please also illustrate a pivot query using varrays or nested tables.

I’ll show you a couple of techniques—an Oracle Database 11g Release 1 and above ANSI implementation and a technique that works in Oracle Database 10g Release 2 using DECODE (or CASE)—and how to use the COLLECT aggregate built-in function in Oracle Database 10g and above to return a collection of data.

First, here’s the latest technique using the new PIVOT clause. (Note that the converse—UNPIVOT—turns columns into rows.) In Listing 1, I use the SCOTT.EMP table with the goal of turning the DEPTNO data, which usually goes down the page in rows, into columns.

Code Listing 1: Using PIVOT to turn columns into rows

SQL> select deptno, clerk, salesman,
  2         manager, analyst, president
  3    from (select deptno, job, sal
  4            from emp )
  5   pivot( sum(sal) for job in
  6   ( 'CLERK' as clerk,
  7     'SALESMAN' as salesman,
  8     'MANAGER' as manager,
  9     'ANALYST' as analyst,
 10     'PRESIDENT' as president ) )
 11   order by deptno
 12  /
    DEPTNO      CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
----------    -------   --------    -------    -------  ---------
        10       1300                  2450                  5000
        20       1900                  2975       6000
        30        950       5600       2850

The key in this query is on line 5: the new PIVOT keyword. It works a little like a WHERE clause and GROUP BY all in one. The query starts on lines 3 and 4, where I select the columns I want to work with—DEPTNO, JOB, and SAL. The PIVOT clause includes SUM(SAL) to compute the aggregate SUM(SAL) grouping implicitly by the remaining columns (DEPTNO and JOB). Normally, that result would look like this:

 SQL> select deptno, job, sum(sal)
  2    from emp
  3   group by deptno, job
  4   order by deptno, job
  5  /
    DEPTNO JOB         SUM(SAL)
---------- --------    --------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
9 rows selected.

Another way to pivot columns into rows that works in releases preceding Oracle Database 11g would be to use DECODE (or CASE) and an aggregation to pivot. Using the last query, I’ll add the necessary DECODE function call to achieve pivoting. I’ll group only by DEPTNO this time, because the goal is to turn the three rows for DEPTNO 10 into one row, as shown in Listing 2.

Code Listing 2: Using DECODE and aggregation to turn columns into rows

SQL> select deptno,
  2    sum( decode( job, 'CLERK', sal ) ) clerk,
  3    sum( decode( job, 'SALESMAN', sal ) ) salesman,
  4    sum( decode( job, 'MANAGER', sal ) ) manager,
  5    sum( decode( job, 'ANALYST', sal ) ) analyst,
  6    sum( decode( job, 'PRESIDENT', sal ) ) president
  7    from scott.emp
  8   group by deptno
  9   order by deptno
 10  /
    DEPTNO      CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
----------    -------   --------    -------    -------  ---------
        10       1300                  2450                  5000
        20       1900                  2975       6000
        30        950       5600       2850

As you can see, the code in Listing 2 achieved the same result as the new PIVOT clause (in Listing 1). The trick is to create a sparse matrix and then use aggregation to collapse the redundant rows, aggregating the SAL column as you go along. Without the aggregation, the data would look like it does in Listing 3.

Code Listing 3: Using DECODE (without aggregation) to turn columns into rows

SQL> select deptno,
  2    ( decode( job, 'CLERK', sal ) ) clerk,
  3    ( decode( job, 'SALESMAN', sal ) ) salesman,
  4    ( decode( job, 'MANAGER', sal ) ) manager,
  5    ( decode( job, 'ANALYST', sal ) ) analyst,
  6    ( decode( job, 'PRESIDENT', sal ) ) president
  7    from scott.emp
  8   order by deptno
  9  /
    DEPTNO      CLERK   SALESMAN    MANAGER    ANALYST  PRESIDENT
----------    -------   --------    -------    -------  ---------
        10                             2450
        10                                                   5000
        10       1300
        20                             2975
        20                                        3000
        20       1100
        20        800
        20                                        3000
        30                  1250
        30                  1500
        30                  1600
        30        950
        30                             2850
        30                  1250
14 rows selected.

All you need to do after that is aggregate by DEPTNO (achieving the one row per department) and sum up the salary.

The last request is to pivot with a collection. Fortunately, the COLLECT aggregate built-in function has been available since Oracle Database 10g. You might think you could use it as shown in Listing 4, but you’ll notice a problem right away: you don’t know what jobs those salaries represent. They are ordered by the job, but you don’t know the job they are associated with. In order to make that association, you need to go a step further and use your own types that preserve the needed information:

SQL> create or replace type
  2  myScalarType as object
  3  ( job varchar2(9), sal number )
  4  /
Type created.
SQL> create or replace type
  2  myTableType
  3  as table of myScalarType
  4  /
Type created.

Code Listing 4: First attempt to pivot with the COLLECT aggregate built-in function

SQL> select deptno,
  2         cast(collect(sum_sal order by job) as sys.odciNumberList) sals
  3    from (select deptno, job, sum(sal) sum_sal
  4            from emp
  5                   group by deptno, job )
  6  group by deptno
  7  /
    DEPTNO   SALS
-----------  ----------------------------------
        10   ODCINUMBERLIST(1300, 2450, 5000)
        20   ODCINUMBERLIST(6000, 1900, 2975)
30 ODCINUMBERLIST(950, 2850, 5600)

Now you have a collection that can represent the job and the salary associated with that job. You can modify your COLLECT query now to use it, as shown in Listing 5. And you get the information you need.

Code Listing 5: Using the COLLECT aggregate built-in function—with types—to pivot

SQL> select deptno,
  2         cast(
  3          collect( myScalarType(job,sum_sal) order by job )
  4              as myTableType) sals
  5    from (select deptno, job, sum(sal) sum_sal
  6            from emp group by deptno, job)
  7  group by deptno
  8  /
    DEPTNO   SALS(JOB, SAL)
----------   ---------------------------------
        10   MYTABLETYPE(MYSCALARTYPE('CLERK'
             , 1300), MYSCALARTYPE('MANAGER',
              2450), MYSCALARTYPE('PRESIDENT'
             , 5000))
        20   MYTABLETYPE(MYSCALARTYPE('ANALYST',
              6000), MYSCALARTYPE('CLERK',
              1900), MYSCALARTYPE('MANAGER',
              2975))
        30   MYTABLETYPE(MYSCALARTYPE('CLERK'
             , 950), MYSCALARTYPE('MANAGER',
             2850), MYSCALARTYPE('SALESMAN',

So, that shows three methods of “pivoting” a result set—a very common topic on asktom.oracle.com .

I am known for saying, “I learn something new about Oracle Database pretty much every day.” Recently I learned something new about PL/SQL pipelined functions. I’ve been using pipelined functions since they were first introduced in Oracle9i Database, almost 10 years ago. I thought I knew everything I needed to know about them, but I was wrong.

Recently on asktom.oracle.com , I was asked a question about the predefined exception NO_DATA_NEEDED. At first I thought that it was a typo and that the person really meant NO_DATA_FOUND, because I hadn’t heard of or read about that exception. But in investigating a little deeper, I discovered what it was.

NO_DATA_NEEDED is a very important exception to be aware of if you ever write a pipelined function. In fact, if you haven’t heard of it and you have written a pipelined function, there is a very good chance you have a bug lurking in your code! This exception is raised only in the context of a pipelined PL/SQL function and only when the pipelined function is willing to return more data but the invoking SQL statement doesn’t need any more data (hence the name NO_DATA_NEEDED). In general, a pipelined function would look like this:

create or replace function
foo( inputs ... )
return some_type
PIPELINED
as
    /* declaration */
begin
    /* initialization */
    /* process a loop */
        pipe row(i);
    end loop;
    /* clean up */
    return;
end;
/

The /* clean up */ code would be used to release any resources allocated in the initialization code. For example, it would close any open cursors, close any open files, and close down any open resources cleanly.

So what would happen if the /* clean up */ code were not invoked—not because of an error but because the calling SQL statement didn’t ever exhaust the output of the function? For example

SQL> create or replace function
  2  generate_data( n in number )
  3  return sys.odciNumberList
  4  PIPELINED
  5  as
  6  begin
  7      dbms_output.put_line
  8      ( '===>>> INITIALIZE' );
  9      for i in 1..generate_data.n
 10      loop
 11          dbms_output.put_line
 12          ( '===>>> PROCESS' );
 13          pipe row(i);
 14      end loop;
 15      dbms_output.put_line
 16      ( '===>>> CLEAN UP' );
 17      return;
 18  end;
 19  /
Function created.

That is a pretty straightforward PL/SQL pipelined function. If you run it to completion, you will see this output:

SQL> select *
  2    from table(generate_data(2));
COLUMN_VALUE
----------------
           1
           2
===>>> INITIALIZE
===>>> PROCESS
===>>> PROCESS
===>>> CLEAN UP

This is expected. But what if you don’t fetch two rows from that function; what if you fetch only one?

SQL> select *
  2    from table(generate_data(2))
  3   where rownum = 1;
COLUMN_VALUE
----------------
           1
===>>> INITIALIZE
===>>> PROCESS

An exception was raised, however. It was an exception that does not have to be caught, and it will be ignored entirely if it is not caught. It differs from every other exception in this regard: we would all expect an unhandled exception to propagate to the client and appear as an error. Let’s see what happens with the code if I implement this error handler:

SQL> create or replace function
  2  generate_data( n in number )
  3  return sys.odciNumberList
  4  PIPELINED
  5  as
  6  begin
  7      dbms_output.put_line
  8      ( '===>>> INITIALIZE' );
  9      for i in 1..generate_data.n
 10      loop
 11          dbms_output.put_line
 12          ( '===>>> PROCESS' );
 13          pipe row(i);
 14      end loop;
 15      dbms_output.put_line
 16      ( '===>>> CLEAN UP' );
 17      return;
 18  exception
 19      when no_data_needed
 20      then
 21          dbms_output.put_line
 22          ( '***>>> CLEAN UP' );
 23          return;
 24  end;
 25  /
Function created.

On line 19, I catch the predefined NO_DATA_NEEDED exception, and on line 21, I announce that I am cleaning up (releasing any resources that need to be released). Now when I run this pipelined function without exhausting it, I see

SQL> select *
  2    from table(generate_data(2))
  3   where rownum = 1;
COLUMN_VALUE
----------------
           1
===>>> INITIALIZE
===>>> PROCESS
***>>> CLEAN UP

As you can see, my special cleanup code (I used ***>>> to announce it) was executed and I could clean up any resources I allocated.

Now this question might arise: what if I use a WHEN OTHERS exception block instead? The answer is, in this case , the output would be the same as WHEN NO_DATA_NEEDED was used, but it would be the wrong approach , the wrong way to code. WHEN OTHERS is far too powerful here and far too broad-ranging in general. Here I would like to clean up when the invoker does not need all the results, and this exception—NO_DATA_NEEDED—is very specifically for this purpose.

So in the future, when you’re looking at pipelined functions, remember NO_DATA_NEEDED and use it when appropriate.