Oracle procedure giving different ASCII outputs

Question

This procedure prints the ASCII code for each character in a string.

If executed in TOAD, it prints these ASCII codes: 55 48 48 32 32 32 32 32 32 32 49 which are the right ones.

If executed via SQLPLUS on the UNIX server and spool the output of the DBMS_OUTPUT.PUT_LINE (v_String); to a text file, copy that output and assign it to the v_String and execute the procedure in TOAD, I get the following ASCII codes 55 48 48 9 32 32 49.

Why it is replacing 32 32 32 32 32 with 9. Essentially a tab.

CREATE OR REPLACE PROCEDURE My_Test
AS
   v_String   VARCHAR2 (25);
BEGIN
   v_String := RPAD ('700', 10) || '1';
   -- v_String:='700   1';
   DBMS_OUTPUT.PUT_LINE (v_String);
   DBMS_OUTPUT.PUT_LINE ('');

   FOR i IN 1 .. LENGTH (v_String)
   LOOP
      DBMS_OUTPUT.PUT_LINE (ASCII (SUBSTR (v_String, i, 1)));
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

Answer 1

That is the default behaviour in SQL*Plus on Unix, controlled by its tab setting:

SET TAB {ON | OFF}

Determines how SQL*Plus formats white space in terminal output. OFF uses spaces to format white space in the output. ON uses the TAB character. TAB settings are every eight characters. The default value for TAB is system dependent.

SQL*Plus is 'helpfully' substituting tabs as it outputs to the terminal. It's nothing to do with PL/SQL or dbms_output - you would see the same thing with just:

select '700       1' from dual;`

You are seeing your first three characters, then a tab instead of the five spaces to take you up to 8 characters, then the last two spaces and the final character.

In your SQL*Plus session, set do set tab off before you start.

You might want to set that in a script, or in a login file so it's always applied.

(Note that this only applies to terminal output, not to spool files; your question refers to spooling but I think you must actually be redirecting or capturing the output some other way.)