Show Leading Spaces in Character String in SQL*PLus

SQL*Plus with its default settings ignores leading spaces when you attempt to output a character string variable using DBMS_OUTPUT.PUT_LINE procedure:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

As you can see in the above example, two leading spaces are trimmed.

To make SQL*Plus showing the leading space characters, we need to change the following setting:

SET SERVEROUTPUT ON FORMAT WRAPPED

Now, the spaces will be preserved:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

Alternatively, you can set the format to TRUNCATED:

SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

PL/SQL procedure successfully completed.

The default setting is WORD WRAPPED:

SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

PL/SQL procedure successfully completed.

SQL> SHOW SERVEROUTPUT
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Create Database Link using EZConnect Syntax

EZConnect syntax eliminates the need for service name lookup in tnsnames.ora files when connecting to an Oracle Database across a TCP/IP network. In a previous post we have already demonstrated how to use this syntax to establish connection in SQL*Plus.

Now, we are going even further. You can use the same syntax for creating Database Links (assuming that you have a privilege to create database links in general).

CREATE DATABASE LINK dbl_test
CONNECT TO scott
IDENTIFIED BY tiger
USING ‘scott/tiger@192.168.1.180:1521/ORCL’;

Database link created.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the week #4

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 8, or 16 teams (power of 2) in the 2nd round.

Expected Results:

For 6 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-3 vs Team-6
     2 Team-4 vs Team-5
       Team-1 advances to Round 2
       Team-2 advances to Round 2

For 7 teams:

Game # Playoff Round 1 Pairings
------ --------------------------
     1 Team-2 vs Team-7
     2 Team-3 vs Team-6
     3 Team-4 vs Team-5
       Team-1 advances to Round 2

For 8 teams:

Game # Playoff Round 1 Pairings
------ ------------------------
     1 Team-1 vs Team-8
     2 Team-2 vs Team-7
     3 Team-3 vs Team-6
     4 Team-4 vs Team-5

For 1 team (no playoffs needed):

Game # Playoff Round 1 Pairings
------ ---------------------------
       Team-1 is a Champion!

For 11 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-6 vs Team-11
     2 Team-7 vs Team-10
     3 Team-8 vs Team-9
       Team-1 advances to Round 2
       Team-2 advances to Round 2
       Team-3 advances to Round 2
       Team-4 advances to Round 2
       Team-5 advances to Round 2

You can use a substitution variable and run the query in SQL*Plus to test the results.

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Use EZConnect Syntax for Oracle connection string when you cannot change the TNS Names file.

In some companies, there are security regulations that disallow users to change their local TNS Names file, so if there is a need to connect to a host that is not already defined in the TNS Names file, a different approach has to be employed.

There are a couple of ways to work around the above mentioned limitation:

Note: To get to a SQL*Plus command prompt without connecting use “/nolog” attribute with sqlplus:

C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 20 14:22:25 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL>

1) TNS-less connection string:

SQL> conn scott/tiger@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.180)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
Connected.

2) EZConnect connection string:

SQL> conn scott/tiger@192.168.1.180:1521/ORCL
Connected.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

I was able to use both these approaches with SQL*Plus and SQL Loader.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to generate a CSV file from SQL*Plus – Part 2, by Zahar Hilkevich

This is a logical continuation of the topic we touched in a previous post.

This time we will see how to generate a CSV file from a cursor returned by a stored procedure. Our procedure should simply return a ref cursor based on the same query we used before:

SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno, dept.dname
ORDER BY 1

Here is the code for such stored procedure:

CREATE OR REPLACE PROCEDURE sp_get_dept(p_result OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_result FOR
    SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
    FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
    GROUP BY dept.deptno, dept.dname
    ORDER BY 1;
END;

The following script will help you accomplish the task:

accept fname prompt "Please enter full report file name: "

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SET LINESIZE 100
SET PAGESIZE 0
SET VERIFY OFF

spool &fname 

DECLARE
   c SYS_REFCURSOR;
   --The following cursor is needed only to quickly define the type of the variable v;
   CURSOR c_type IS
   SELECT deptno, dname, 100 AS emp_count
   FROM dept
   WHERE 1=2;

   v c_type%ROWTYPE;
BEGIN
   DBMS_OUTPUT.ENABLE(NULL);

   --Dump out the header line:
   DBMS_OUTPUT.PUT('Dept No,');
   DBMS_OUTPUT.PUT('Dept Name,');
   DBMS_OUTPUT.PUT_LINE('Emp Count');
  
   --Call stored procedure
   sp_get_dept(c);   

   --Dump out the records:
   LOOP
        FETCH c INTO v;
        EXIT WHEN c%NOTFOUND;
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
   CLOSE c;
END;
/

spool off

The content of the generated file should look like this:

Dept No,Dept Name,Emp Count                                                                         
"10","ACCOUNTING","3"                                                                               
"20","RESEARCH","5"                                                                                 
"30","SALES","6"                                                                                    
"40","OPERATIONS","0"       

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to generate a CSV file from SQL*Plus – Part 1, by Zahar Hilkevich

SQL Plus was widely used for reporting purposes for years but not any more. However, it can still be very handy if we want the report to be opened in another software product, such as Microsoft Excel.
In this post, I will show you how you can generate an Excel file directly from SQL*Plus.

Let’s say, for simplicity sake, we need to dump the result for the following query in Excel (csv) file:

SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno, dept.dname
ORDER BY 1

The following script will help you accomplish the task:

accept fname prompt "Please enter full report file name: "

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SET LINESIZE 100
SET PAGESIZE 0
SET VERIFY OFF

spool &fname 

DECLARE
   CURSOR c IS
   SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
   FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
   GROUP BY dept.deptno, dept.dname
   ORDER BY 1;

BEGIN
   DBMS_OUTPUT.ENABLE(NULL);

   --Dump out the header line:
   DBMS_OUTPUT.PUT('Dept No,');
   DBMS_OUTPUT.PUT('Dept Name,');
   DBMS_OUTPUT.PUT_LINE('Emp Count');

   --Dump out the records:
   FOR v IN c LOOP
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
END;
/

spool off

The content of the generated file should look like this:

Dept No,Dept Name,Emp Count                                                                         
"10","ACCOUNTING","3"                                                                               
"20","RESEARCH","5"                                                                                 
"30","SALES","6"                                                                                    
"40","OPERATIONS","0"       

Here is how you run it from SQL*Plus (after establishing the connection):

r2

r

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Part 2 is here

How to substitute ids with names in a delimited string with a single SELECT statement

Suppose that you have a comma delimited line of department numbers and you need to get a comma delimited line of department names. Apparently, you could write a PL/SQL block, loop through the list of departments and concatenate them in a single text string. However, with a new powerful built-in function listagg that was introduced in Oracle 11gR2 you can accomplish the task in a single SELECT statement.

The idea is to split the ids first using approach I presented in a previous post and then use LISTAGG function to assemble the result character string.

var department_numbers VARCHAR2(200)

exec :department_numbers:='10,20,40'

col dept_names for a80

WITH x AS (
SELECT REGEXP_SUBSTR(:department_numbers, '[^,]+', 1, LEVEL) deptno
FROM dual
CONNECT BY LEVEL <= LENGTH(:department_numbers)-LENGTH(REPLACE(:department_numbers,','))+1
)
SELECT listagg (dname, ', ') WITHIN GROUP (ORDER BY dname) AS dept_names
FROM dept d JOIN x ON d.deptno=x.deptno;

Result:

DEPT_NAMES
----------------------------------
ACCOUNTING, OPERATIONS, RESEARCH

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Passing comma delimtied string to a stored procedure

It would be nice if we could always avoid dynamic SQL in our PL/SQL. The execution path would be mostly known upfront, it would be so easy debug and troubleshoot the code, etc. However, there are times when it seems almost impossible to write SQL queries in PL/SQL without relying on dynamic SQL functionality. One of the great examples is passing multiple ids separated by some delimiter (mostly by a comma).

Let’s consider a stored procedure with the following signature:

PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)

We need to retrieve all employees who work in one of the departments with department numbers listed in p_dept_ids line.

The query may look something like this:

SELECT empno, ename, job, deptno
FROM emp
WHERE deptno IN ([list of ids])
ORDER BY deptno, ename

Let’s first review a “traditional” approach based on dynamic SQL:

CREATE OR REPLACE PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
  v_sql VARCHAR2(2000);
BEGIN
  v_sql:='SELECT empno, ename, job, deptno
	  FROM emp
	  WHERE deptno IN (' || p_dept_ids || ')
	  ORDER BY deptno, ename';
  OPEN p_result FOR v_sql;

END sp_get_empployees;
/

If you need to test this procedure in SQL*Plus, here is how to do it:

SQL> var c refcursor
SQL> exec sp_get_empployees('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

Everything seems to look good at the first glance, but there are some issues.

What if p_dept_ids value is NULL (empty) which could mean that we don’t want any employees to be returned back?

Passing NULL will lead to an error:

SQL> exec sp_get_empployees('', :c)
BEGIN sp_get_empployees('', :c); END;

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "SCOTT.SP_GET_EMPPLOYEES", line 9
ORA-06512: at line 1

Yes, we could add a validation of the input parameter, and this is not a bad idea in general. The main purpose of this post is to show you a workaround that would not rely on the dynamic SQL at all and also it won’t use the input parameter validation.
We are going to leverage the power of regular expressions to split the comma delimited line:

CREATE OR REPLACE PROCEDURE sp_get_empployees2(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_result FOR 
  SELECT empno, ename, job, deptno
  FROM emp
  WHERE deptno IN (SELECT REGEXP_SUBSTR(p_dept_ids, '[^,]+', 1, LEVEL) deptno
                   FROM dual
                   CONNECT BY LEVEL <= LENGTH(p_dept_ids) - LENGTH(REPLACE(p_dept_ids,','))+1 
                  )
  ORDER BY deptno, ename;
END sp_get_empployees2;
/

Here is the test:

SQL> exec sp_get_empployees2('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

SQL> exec sp_get_empployees2('', :c)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

In subsequent posts I will show some more workarounds to the presented solution.

If you want to learn how to come up with numerous workarounds on your own, check my book “Oracle SQL Tricks and Workarounds” for instructions.