已重定向o/p(来自PL/SQL block not in proper format in shell)[重复]

hrysbysz  于 2023-05-07  发布在  Shell
关注(0)|答案(1)|浏览(83)

此问题已在此处有答案

I just assigned a variable, but echo $variable shows something else(7个回答)
昨天关门了。
我有一个shell脚本,它连接到Oracle 19 c DB,将对象部署到同一DB中的2个模式,然后比较这两个模式以检查这两个模式之间的对象差异。我想把这些资料归档。按照我下面的代码,数据被保存在一个文件中,但没有格式化。无新行。一切都像一个大段落。
下面是我的示例代码:

#!/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

下面是文件中的O/P:

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

我希望它是可读的格式:

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
41ik7eoe

41ik7eoe1#

您需要添加引号:

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

学习如何在shell中正确引用,这非常重要:
“双引号”每个包含空格/元字符和 every 扩展的文字:"$var""$(command "$var")""${array[@]}""a & b"。使用'single quotes'作为代码或文字$'s: 'Costs $5 US'ssh host 'echo "$HOSTNAME"'。看
http://mywiki.wooledge.org/Quotes
http://mywiki.wooledge.org/Arguments
http://wiki.bash-hackers.org/syntax/words
when-is-double-quoting-necessary

相关问题