Redirected o/p from PL/SQL block not in proper format in shell

I have a shell script which connects to an Oracle 19c DB, deploys objects to 2 schemas within the same DB and then compares the two schemas to check for differences in objects between the two schemas. I’d like to get this information into a file. As per my below code, the data is getting saved in a file but there is no formatting. No New Lines. Everything appears like one big paragraph.

Here is my sample code:

#!/bin/sh


DT=`date '+%Y%m%d'`
log_dir=/test/${DT}
dbalogin=admin/pwd@SCOTT
 
Result1=`sqlplus -S $dbalogin << EOF
 whenever sqlerror exit 1;
 set feedback off
 SET SERVEROUTPUT ON;
DECLARE
    V_CNT           NUMBER;
    v_stmt          VARCHAR2 (2000);
    V_CNT1          NUMBER;
    v_stmt1         VARCHAR2 (2000);
    V_CNT2          NUMBER;
    v_stmt2         VARCHAR2 (2000);
    v_missobjects1   VARCHAR2 (32000);
    v_missobjects2   VARCHAR2 (32000);
    v_INVALIDobjects2 VARCHAR2 (32000);
    v_INVALIDobjects1 VARCHAR2 (32000);
    
BEGIN
    FOR v_cur
        IN (SELECT DISTINCT object_type
              FROM all_objects
             WHERE     object_type IN ('SYNONYM',
                                       'PACKAGE BODY',
                                       'TRIGGER',
                                       'PROCEDURE',
                                       'PACKAGE',
                                       'FUNCTION',
                                       'TYPE',
                                       'VIEW'))
    LOOP
        v_stmt1 :=  'select count(*) from all_objects where object_type ='''|| v_cur.object_type|| ''' AND owner=''SCOTTA'' ';

        EXECUTE IMMEDIATE v_stmt1  INTO v_cnt1;

        v_stmt2 :=  'select count(*) from all_objects where object_type ='''|| v_cur.object_type|| ''' AND owner=''SCOTTB'' ';

        EXECUTE IMMEDIATE v_stmt2 INTO v_cnt2;

        IF NVL (v_cnt1, 0) != NVL (v_cnt2, 0)
        THEN
            DBMS_OUTPUT.PUT_LINE (  v_cur.object_type || ' = SCOTTA'|| ' --> ' || v_cnt1 || ' AND SCOTTB ' || ' --> ' || v_cnt2);
            DBMS_OUTPUT.NEW_LINE;
        END IF;

        SELECT LISTAGG (OBJECT_NAME, ' , ')  WITHIN GROUP (ORDER BY OBJECT_NAME DESC)
          INTO v_missobjects1
          FROM (SELECT OBJECT_NAME   FROM all_objects   WHERE     object_type = v_cur.object_type   AND owner = 'SCOTTB'   
                MINUS
                SELECT OBJECT_NAME FROM all_objects WHERE     object_type = v_cur.object_type   AND owner = 'SCOTTA'  );

        IF v_missobjects1 IS NOT NULL
        THEN
            DBMS_OUTPUT.PUT_LINE (  v_cur.object_type|| '(S) present in SCOTTB but not in SCOTTA:  ' || v_missobjects1);
        END IF;

      SELECT LISTAGG (OBJECT_NAME, ' , ')  WITHIN GROUP (ORDER BY OBJECT_NAME DESC)
          INTO v_missobjects2
          FROM (SELECT OBJECT_NAME   FROM all_objects   WHERE     object_type = v_cur.object_type   AND owner = 'SCOTTA'   
                MINUS
                SELECT OBJECT_NAME FROM all_objects WHERE     object_type = v_cur.object_type   AND owner = 'SCOTTB'  );


    IF v_missobjects2 IS NOT NULL
        THEN
            DBMS_OUTPUT.PUT_LINE (  v_cur.object_type|| '(S) present in SCOTTA but not in SCOTTB:  ' || v_missobjects2);
        END IF;
        
        
        
    END LOOP;
    select LISTAGG (OBJECT_NAME, ' , ')  WITHIN GROUP (ORDER BY OBJECT_NAME DESC) INTO v_INVALIDobjects1 from all_objects where status <>'VALID' and owner= 'SCOTTB';

 IF v_INVALIDobjects1 IS NOT NULL
        THEN
            DBMS_OUTPUT.PUT_LINE ( 'Invalid objects in SCOTTB:  ' || v_INVALIDobjects1);
        END IF;

select LISTAGG (OBJECT_NAME, ' , ')  WITHIN GROUP (ORDER BY OBJECT_NAME DESC) INTO v_INVALIDobjects2 from all_objects where status <>'VALID' and owner= 'SCOTTA';
 IF v_INVALIDobjects2 IS NOT NULL
        THEN
            DBMS_OUTPUT.PUT_LINE ( 'Invalid objects in SCOTTA:  ' || v_INVALIDobjects2);
        END IF;
END;
/
EOF`

echo $Result1   >$log_dir/schemas_diff.log

Here is what the O/P in the file looks like:

VIEW = SCOTTB --> 3329 AND SCOTTG --> 3328 VIEW(S) present in SCOTTB but not in SCOTTG: V_EMP_SAL Invalid objects in SCOTTB: TR_TEST_56787 , TR_TEST_7868 , TR_TEST_1267 , TR_TEST_78687 , TR_TEST_SALARY_ADDITIONAL , TR_TEST_456  Invalid objects in B: TR_TEST_3324 , TR_TEST_3423 , TR_TEST_65756

I’d like it in a readable format:

VIEW = SCOTTB --> 3329 AND SCOTTG --> 3328 
VIEW(S) present in SCOTTB but not in SCOTTG: V_EMP_SAL 

Invalid objects in SCOTTB: TR_TEST_56787 , TR_TEST_7868 , TR_TEST_1267 , TR_TEST_78687 , TR_TEST_SALARY_ADDITIONAL , TR_TEST_456  

Invalid objects in B: TR_TEST_3324 , TR_TEST_3423 , TR_TEST_65756 

>Solution :

You need to add quotes:

echo "$Result1" > "$log_dir/schemas_diff.log"

Learn how to quote properly in shell, it’s very important :

"Double quote" every literal that contains spaces/metacharacters and every expansion: "$var", "$(command "$var")", "${array[@]}", "a & b". Use 'single quotes' for code or literal $'s: 'Costs $5 US', ssh host 'echo "$HOSTNAME"'. See
http://mywiki.wooledge.org/Quotes
http://mywiki.wooledge.org/Arguments
http://wiki.bash-hackers.org/syntax/words
when-is-double-quoting-necessary