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

A few thoughts on parameterized cursors.

In PL/SQL, parameterized cursors offer a great deal of flexibility and clarity of the code.

Let’s illustrate this point with a specific (though artificial) example.

Let say, we need to show all employees who is paid above average in their respective department. In SQL, the solution would be quite simple:

SQL> SELECT deptno, ename, sal
  2  FROM emp e
  3  WHERE sal>(SELECT AVG(sal)
  4             FROM emp
  5             WHERE deptno=e.deptno)
  6  ORDER BY deptno, sal DESC, ename;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN            1600

We are going to solve the same simple problem in PL/SQL (using anonymous block). The first example will utilize 2 PL/SQL variables instead of cursor parameters:

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_deptno  NUMBER;
  v_avg_sal NUMBER;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_deptno
    AND sal>v_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_deptno:=v1.deptno;
    v_avg_sal:=v1.avg_sal;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

Note the use of the “SET SERVEROUTPUT ON FORMAT WRAPPED” sqlplus command. You can read more about it here.

Alternatively, we can you a record variable:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  v_dept d%ROWTYPE;
  CURSOR e IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=v_dept.deptno
    AND sal>v_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    v_dept:=v1;
    FOR v2 IN e LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;

A better way to pass variables to a cursor is to use cursor parameters:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_deptno NUMBER, c_avg_sal NUMBER) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_deptno
    AND sal>c_avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1.deptno, v1.avg_sal) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

deptno ename       sal
------ -------- ------
    10 KING       5000
    20 FORD       3000
    20 SCOTT      3000
    20 JONES      2975
    30 BLAKE      2850
    30 ALLEN      1600

As you can see, no variable declaration and assignment is needed we; instead, we declare cursor parameters. This gives the code better clarity and readability as both, the cursor parameters and the cursor itself are defined in one place. You don’t need any intermediary variable for opening a nested cursor.

The following example will optimize the last block by using a single cursor parameter:

DECLARE
  CURSOR d IS
  SELECT deptno, AVG(sal) avg_sal
  FROM emp
  GROUP BY deptno
  ORDER BY 1;
  CURSOR e(c_dept d%ROWTYPE) IS
  SELECT ename, sal
  FROM emp
  WHERE deptno=c_dept.deptno
    AND sal>c_dept.avg_sal
  ORDER BY sal DESC, ename;
BEGIN
  DBMS_OUTPUT.PUT_LINE('deptno ename       sal');
  DBMS_OUTPUT.PUT_LINE('------ -------- ------');
  FOR v1 IN d LOOP
    FOR v2 IN e(v1) LOOP
      DBMS_OUTPUT.PUT_LINE(LPAD(v1.deptno,6) || ' ' || RPAD(v2.ename, 9) || LPAD(v2.sal, 6));
    END LOOP;
  END LOOP;
END;
/

So what is the main advantages of using parameterized cursors over using cursors with [bind] variables?

  • Parameterized cursors support default values for cursor parameters
  • The cursors can be referenced more than once with different parameter values
  • A cursor with parameter(s) encapsulates all information necessary for opening and fetching data which makes it safer for use as you don’t need to trace the assignment of cursor parameters/variables all over the place.

 

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.

 

Format dbms_output.put_line for text strings over 255 characters long

Every experienced PL/SQL developer knows the importance of dbms_output.put_line procedure which happened to have a limitation that makes it difficult to print large text strings (over 255 bytes). It only allows to print up to 255 characters on a single line.

In his blog post, Tom Kyte suggests using the following simple procedure:

create or replace procedure p( p_string in varchar2 )
is
   l_string long default p_string;
begin
   loop
     exit when l_string is null;
     dbms_output.put_line( substr( l_string, 1, 250 ) );
     l_string := substr( l_string, 251 );
   end loop;
end;
/

The obvious drawback of this method is the lines wrapped up (i.e. broken up) at every 250th random character.

Below is an improved version of this procedure which I use all the time in my work:

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
                                p_chunk_size NUMBER:=255)
AS
    v_len           NUMBER:=LENGTH(p_text);
    v_cnt           NUMBER:=0;
    v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
    v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
    IF p_text IS NULL THEN
        RETURN;
    END IF;
    
    IF v_len<=v_chunk_size THEN
        DBMS_OUTPUT.PUT_LINE(p_text);
        RETURN;
    END IF;
    
    IF v_LF_postion<=v_chunk_size THEN
        v_chunk_size:=v_LF_postion-1;
    ELSE
        FOR i IN REVERSE 1..v_chunk_size LOOP
            IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
                v_chunk_size:=i;
                EXIT;
            END IF;
        END LOOP;    
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
    pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
END;
/

This procedure uses recursive calls to itself and splits the line at every line feed character (ASCII CODE 10) if the line is under 255 characters long, otherwise, it splits it at the most recent space or comma characters that precede 255th symbol.

For the demonstration purpose, let’s see how it will print out its own text:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      pl(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
p_chunk_size NUMBER:=255)
AS
v_len           NUMBER:=LENGTH(p_text);
v_cnt           NUMBER:=0;
v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
BEGIN
IF p_text IS NULL THEN
RETURN;
END IF;
IF v_len<=v_chunk_size THEN
DBMS_OUTPUT.PUT_LINE(p_text);
RETURN;
END IF;
IF v_LF_postion<=v_chunk_size THEN
v_chunk_size:=v_LF_postion-1;
ELSE
FOR i IN REVERSE 1..v_chunk_size LOOP
IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma
v_chunk_size:=i;
EXIT;
                END IF;
            END LOOP;

        END IF;


DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));
        pl(p_text=>
SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
    END;

PL/SQL procedure successfully completed.

The output is not nicely formatted but it is compilable.
Let’s compare it to the output of the p procedure:

SQL> DECLARE
  2    v_sql VARCHAR2(4000):=
  3    'CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,
  4                                  p_chunk_size NUMBER:=255)
  5      AS
  6          v_len           NUMBER:=LENGTH(p_text);
  7          v_cnt           NUMBER:=0;
  8          v_chunk_size    NUMBER:=LEAST(p_chunk_size, 255);
  9          v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
 10      BEGIN
 11          IF p_text IS NULL THEN
 12              RETURN;
 13          END IF;
 14
 15          IF v_len<=v_chunk_size THEN
 16              DBMS_OUTPUT.PUT_LINE(p_text);
 17              RETURN;
 18          END IF;
 19
 20          IF v_LF_postion<=v_chunk_size THEN  21              v_chunk_size:=v_LF_postion-1;  22          ELSE  23              FOR i IN REVERSE 1..v_chunk_size LOOP  24                  IF SUBSTR(p_text, i, 1) IN (CHR(32), CHR(44)) THEN --space or comma  25                      v_chunk_size:=i;  26                      EXIT;  27                  END IF;  28              END LOOP;  29  30          END IF;  31  32          DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1, v_chunk_size));  33          pl(p_text=> SUBSTR(p_text, v_chunk_size+2), p_chunk_size=>p_chunk_size);
 34      END;';
 35  BEGIN
 36      p(v_sql);
 37  END;
 38  /

CREATE OR REPLACE PROCEDURE pl (p_text       VARCHAR2,

p_chunk_size NUMBER:=255)
    AS
        v_len
NUMBER:=LENGTH(p_text);
        v_cnt           NUMBER:=0;
        v_chunk_size
NUMBER:=LEAST(p_chunk_s
ize, 255);
        v_LF_postion    NUMBER:=INSTR(p_text, CHR(10));
    BEGIN

IF p_text IS NULL THEN
            RETURN;
        END IF;

        IF
v_len<=v_chunk_size THEN
            DBMS_OUTPUT.PUT_LINE(p_text);

RETURN;

END IF;

        IF v_LF_postion<=v_chunk_size THEN

v_chunk_size:=v_LF_postion-1;
        ELSE
            FOR i IN REVERSE
1..v_chunk_size LOOP
                IF SUBSTR(p_text, i, 1) IN (CHR(32),
CHR(44)) THEN --space or comma

v_chunk_size:=i;
                    EXIT;
                END IF;

END LOOP;

        END IF;

        DBMS_OUTPUT.PUT_LINE(SUBSTR(p_text, 1,
v_chunk_size));
        pl(p_text=> SUBSTR(p_text, v_chunk_size+2),
p_chunk_size=>p_
chunk_size);
    END;

PL/SQL procedure successfully completed.

It’s not so bad, but you cannot simply copy the output and compile it.

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