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.

Get a comma separated line of Oracle table column names

When you have to manually put together an insert statement into a table with large number of columns you may feel some pain – it is tedious to concatenate all the columns in a comma delimited line. The following small procedure may save you some time and effort.

CREATE OR REPLACE PROCEDURE sp_get_column_line(p_table VARCHAR2)
AS
    v_result VARCHAR2(2000);
BEGIN
    SELECT listagg (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
    INTO v_result
    FROM user_tab_cols
    WHERE table_name=UPPER(p_table);

    DBMS_OUTPUT.PUT_LINE(LOWER(v_result));
END;
/

SQL> exec sp_get_column_line('emp')

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> exec sp_get_column_line('emp')
empno, ename, job, mgr, hiredate, sal, comm, deptno

PL/SQL procedure successfully completed.

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.