SQLPlus DBMS output not the same as in developer

Question

I'm writing a script that will generate SQLLoader file based on table name. So far, I've managed to do so, but when I run the sql script in SQLPlus I'm not getting the same output as in developer. To be precise, there are new lines inserted when they're not invoked. What am I doing wrong?

Following code is just the beginning without rest of the code. When columns are included I get really messy output with multiple newlines here and there.

Note: I need it to run through SQLPlus as I intend to incorporate the code in Shell script.

SET SERVEROUTPUT ON
DECLARE
    lv_ctl VARCHAR2(32767);
    lv_tableName VARCHAR2(10) := 'MyTable';
BEGIN
    lv_ctl :=  'OPTIONS(SKIP=1)' || CHR(10)
            || 'LOAD DATA' || CHR(10)
            || 'APPEND' || CHR(10)
            || 'INTO TABLE ' || lv_tableName || CHR(10)
            || 'FIELDS TERMINATED BY ","' || CHR(10)
            || 'TRAILING NULLCOLS' || CHR(10)
            || '(' || CHR(10)
            ;   
    
    dbms_output.put_line(lv_ctl);
END;
/
SET SERVEROUTPUT OFF

When run through developer I get (which is the output I want):

OPTIONS(SKIP=1)
LOAD DATA
APPEND
INTO TABLE MyTable
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(

When run through SQLPlus:

OPTIONS(SKIP=1)
LOAD DATA
APPEND
INTO TABLE MyTable
FIELDS TERMINATED BY
","
TRAILING NULLCOLS
(

The problem is I get new lines randomly, can't find the reason behind it. As you see fifth line (FIELDS TERMINATED BY ",") is not inline.

If both scripts give you the same result, does it matter that they're not exactly the same? — Abra
– Abra, Commented Jul 6, 2020 at 13:01
Is it possible that the line is wrapping in sqlplus? What happens if you "SET LINESIZE 100" before running your script? — pmdba
– pmdba, Commented Jul 6, 2020 at 13:05
@pmdba it worked. Please put that as an answer and thanks a lot! — Milan Stojković
– Milan Stojković, Commented Jul 6, 2020 at 13:16

Alex Poole · Accepted Answer · 2020-07-06 13:20:18Z

SQL*Plus doesn't play nicely with dbms_output when it has newline characters within the string. Those are ignored, for some reason, when SQL*Plus does its own line wrapping. You might be able to set linesize to a very large number to avoid this - depending on how big the control file will actually be.

set linesize 4000

You don't really need to include the line breaks though, you can just output the file one line at a time:

DECLARE
    lv_tableName VARCHAR2(10) := 'MyTable';
BEGIN
    dbms_output.put_line('OPTIONS(SKIP=1)');
    dbms_output.put_line('LOAD DATA');
    dbms_output.put_line('APPEND');
    dbms_output.put_line('INTO TABLE ' || lv_tableName);
    dbms_output.put_line('FIELDS TERMINATED BY ","');
    dbms_output.put_line('TRAILING NULLCOLS');
    dbms_output.put_line('(');
    ...
END;
/

Or don't use PL/SQL at all, of course:

SELECT 'OPTIONS(SKIP=1)' || CHR(10)
        || 'LOAD DATA' || CHR(10)
        || 'APPEND' || CHR(10)
        || 'INTO TABLE ' || lv_tableName || CHR(10)
        || 'FIELDS TERMINATED BY ","' || CHR(10)
        || 'TRAILING NULLCOLS' || CHR(10)
        || '(' || CHR(10)
  FROM DUAL
        ;

If the size is an issue, you can make it a CLOB:

SELECT to_clob('OPTIONS(SKIP=1)') || CHR(10)
...

pmdba · Accepted Answer · 2020-07-06 13:18:14Z

1

Make sure SQLPlus isn't wrapping the output. Try "SET LINESIZE 100" before running your script.

answered Jul 6, 2020 at 13:18

pmdba

7,0932 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQLPlus DBMS output not the same as in developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related