TECHNOLOGY: Ask Tom
On Connecting, Pivoting, and Learning New Things
By Tom Kyte
Our technologist bypasses TNSNAMES.ORA, turns rows to columns, and meets the NO_DATA_NEEDED exception.
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:
and I can connect to the database with
However, if I try to connect with
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 184.108.40.206.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
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.
Pivot Query Examples
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 Learn Something New
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.
Tom Kyte is a database evangelist in Oracle’s Server Technologies division and has worked for Oracle since 1993. He is the author of Expert Oracle Database Architecture: 9 i and 10 g Programming Techniques and Solutions (Apress, 2005) and Effective Oracle by Design (Oracle Press, 2003), among other books.