oracle 奇怪的函数异常返回问题

qlckcl4x  于 2023-04-05  发布在  Oracle
关注(0)|答案(1)|浏览(181)

我有一个检查Oracle数据库缺口的功能。它在11 G，12 C上工作，但升级到19 C后开始给予。
第1行ORA-06503错误：PL/SQL：返回的函数没有值ORA-06512：在“XYZ.ABCFNC_DATAGUARD_INFO”，第374行ORA-06512：在12行
另外，当我改变异常返回空仍然不工作。
有人能帮帮我吗？

CREATE OR REPLACE FUNCTION XYZ.ABCFNC_DATAGUARD_INFO(VTYPE IN CHAR DEFAULT 'P',VINTERVAL IN CHAR DEFAULT 'D',VMAIL IN VARCHAR2 DEFAULT 'MAIL_TO_ABC') RETURN CLOB IS
CURSOR C1 IS
    SELECT
      a.resetlogs_id,
      a.thread# THREAD_ID,
      b.last_seq last_occured,
      a.applied_seq last_applied,
      (b.last_seq - a.applied_seq) difference,
      TO_CHAR(a.last_applied_time, 'YYYY-MM-DD HH24:MI:SS') last_applied_time,
      dest_id
    FROM
      (
        SELECT
          resetlogs_id,
          thread#,
          MAX (sequence#) applied_seq,
          MAX (next_time) last_applied_time,
          dest_id
        FROM
          gv$archived_log
        WHERE 
          applied = 'YES'
          and resetlogs_id=(
                              select
                                max(resetlogs_id)
                              FROM
                                gv$archived_log
                            )
        GROUP BY resetlogs_id, thread#, dest_id
      ) a, 
      (
        SELECT
          resetlogs_id, thread#, MAX (sequence#) last_seq
        FROM
          gv$archived_log
        WHERE
          resetlogs_id=(
                          select
                            max(resetlogs_id)
                          from 
                          gv$archived_log
                        )
        GROUP BY resetlogs_id, thread#
      ) b
    WHERE
      a.thread# = b.thread#
      and dest_id in (
                        SELECT 
                          DEST_ID
                        FROM
                          SYS.GV_$ARCHIVE_DEST
                        WHERE
                          TARGET = 'STANDBY' and (STATUS = 'VALID' or STATUS = 'ERROR')
                      );
  VTRACK NUMBER := 0;
  VTRACK2 NUMBER := 0;  
  VMPREFIX VARCHAR2(1000);
  VMHOSTNAME VARCHAR2(1000);
  VMDBNAME VARCHAR2(1000);
  VMDATE VARCHAR2(1000);
  VMTOPIC VARCHAR2(1000) := 'DATA GUARD INFO';
  C1_OUTPUT CLOB;
  C2_OUTPUT CLOB;
  VMSUBJECT VARCHAR2(4000);
  feature_usage     varchar2(1000);
  log_transport     varchar2(25);
  num_arch          number;
  num_casc_stby     number;
  num_compression   number;
  num_dgconfig      number;
  num_far_sync      number;
  num_fast_sync     number;
  num_lgwr_async    number;
  num_lgwr_sync     number;
  num_realtime_apply    number;
  num_redo_apply    number;
  num_snapshot      number;
  num_sql_apply     number;
  num_standbys      number;
  num_terminal_db   number;
  protection_mode   varchar2(24);
  use_broker        varchar2(5);
  use_compression   varchar2(8);
  use_far_sync      varchar2(5);
  use_fast_sync     varchar2(5);
  use_flashback     varchar2(18);
  use_fs_failover   varchar2(22);
  use_realtime_apply    varchar2(5);
  use_redo_apply    varchar2(5);
  use_snapshot      varchar2(5);
  use_sql_apply     varchar2(5);
  aux_count        NUMBER;
  feature_info     CLOB;
  vversion         varchar2(100);
  vdgused          varchar2(30);
  VDBROLE          varchar2(100);
  VM_ERR_CNT NUMBER;
  VM_ERR_TYPE VARCHAR2(300):='DATAGUARD';
BEGIN
  DBMS_LOB.CREATETEMPORARY (C1_OUTPUT, TRUE);
  DBMS_LOB.CREATETEMPORARY (C2_OUTPUT, TRUE);
  C1_OUTPUT :=  '<font size="2" color="red">'||RPAD('~~~~~~~~~~~~~~~ '||VMTOPIC||' ~',50,'~')||'</font>';
  C2_OUTPUT :=  '<font size="2" color="red">'||RPAD('~~~~~~~~~~~~~~~ '||VMTOPIC||' ~',50,'~')||'</font>';
  aux_count := 0;
  log_transport := NULL;
  num_arch := 0;
  num_casc_stby := 0;
  num_compression := 0;
  num_dgconfig :=0;
  num_far_sync := 0;
  num_fast_sync := 0;
  num_lgwr_async := 0;
  num_lgwr_sync := 0;
  num_realtime_apply := 0;
  num_redo_apply := 0;
  num_snapshot := 0;
  num_sql_apply := 0;
  num_standbys := 0;
  num_terminal_db := 0;
  use_broker := 'FALSE';
  use_compression := 'FALSE';
  use_far_sync := 'FALSE';
  use_fast_sync := 'FALSE';
  use_flashback := 'FALSE';
  use_fs_failover := 'FALSE';
  use_realtime_apply := 'FALSE';
  use_redo_apply := 'FALSE';
  use_snapshot := 'FALSE';
  use_sql_apply := 'FALSE';
  vdgused := 'FALSE';
  SELECT DATABASE_ROLE INTO VDBROLE FROM SYS.V_$DATABASE;
  IF VDBROLE='PRIMARY' THEN
    execute immediate 'select count(*) from v$archive_dest ' ||
    'where target = ''STANDBY'''
    into num_standbys;
    if (num_standbys > 0) then
    vdgused := 'TRUE';
    select version into vversion from v$instance;
    if vversion>='13' THEN
    execute immediate 'select count(*) from v$dataguard_config'
      into num_dgconfig;
    end if;
    if (num_dgconfig > 0) then
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config '||
      'where (DEST_ROLE like ''% STANDBY'')'
      into num_standbys;
      execute immediate 'select count(unique(PARENT_DBUN)) ' ||
      'from v$dataguard_config ' ||
      'where (PARENT_DBUN not in ' ||
      '(select DB_UNIQUE_NAME from v$database) and ' ||
      'PARENT_DBUN != ''NONE'' and PARENT_DBUN != ''UNKNOWN'')'
      into num_casc_stby;
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config ' ||
      'where (DB_UNIQUE_NAME not in ' ||
      '(select PARENT_DBUN from v$dataguard_config) and ' ||
      'PARENT_DBUN != ''UNKNOWN'')'
      into num_terminal_db;
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config' ||
      'where (DEST_ROLE = ''PHYSICAL STANDBY'')'
      into num_redo_apply;
      if (num_redo_apply > 0) then
        use_redo_apply := 'TRUE';
      end if;
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config '||
      'where (DEST_ROLE = ''LOGICAL STANDBY'')'
      into num_sql_apply;
      if (num_sql_apply > 0) then
        use_sql_apply := 'TRUE';
      end if;
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config ' ||
      'where (DEST_ROLE = ''FAR SYNC INSTANCE'')'
      into num_far_sync;
      if (num_far_sync > 0) then
        use_far_sync := 'TRUE';
      end if;
      execute immediate 'select count(unique(DB_UNIQUE_NAME)) ' ||
      'from v$dataguard_config ' ||
      'where (DEST_ROLE = ''SNAPSHOT STANDBY'')'
      into num_snapshot;
      if (num_snapshot > 0) then
        use_snapshot := 'TRUE';
      end if;
    else
      execute immediate 'select count(*) from v$archive_dest_status ' ||
      'where status = ''VALID'' and type = ''PHYSICAL'''
      into num_redo_apply;
      if (num_redo_apply > 0) then
        use_redo_apply := 'TRUE';
      end if;
      execute immediate 'select count(*) from v$archive_dest_status ' ||
      'where status = ''VALID'' and type = ''LOGICAL'''
      into num_sql_apply;
      if (num_sql_apply > 0) then
        use_sql_apply := 'TRUE';
      end if;
      execute immediate 'select count(*) from v$archive_dest_status ' ||
      'where status = ''VALID'' and type = ''FAR SYNC'''
      into num_far_sync;
      if (num_far_sync > 0) then
        use_far_sync := 'TRUE';
      end if;
      num_casc_stby := num_far_sync;
      execute immediate 'select count(*) from v$archive_dest_status ' ||
      'where status = ''VALID'' and type = ''SNAPSHOT'''
      into num_snapshot;
      if (num_snapshot > 0) then
        use_snapshot := 'TRUE';
      end if;
    end if;
    execute immediate 'select value from v$system_parameter ' ||
      'where name = ''dg_broker_start'''
      into use_broker;
    execute immediate 'select count(*) from v$archive_dest ' ||
      'where status = ''VALID'' and target = ''STANDBY'' ' ||
      'and archiver like ''ARC%'''
      into num_arch;
    if (num_arch > 0) then
      log_transport := 'ARCH ';
    end if;
    execute immediate 'select count(*) from v$archive_dest ' ||
      'where status = ''VALID'' and target = ''STANDBY'' ' ||
      'and archiver = ''LGWR'' ' ||
      'and (transmit_mode = ''SYNCHRONOUS'' or ' ||
      '     transmit_mode = ''PARALLELSYNC'')'
      into num_lgwr_sync;
    if (num_lgwr_sync > 0) then
      log_transport := log_transport || 'LGWR SYNC ';
    end if;
    execute immediate 'select count(*) from v$archive_dest ' ||
      'where status = ''VALID'' and target = ''STANDBY'' ' ||
      'and archiver = ''LGWR'' ' ||
      'and transmit_mode = ''ASYNCHRONOUS'''
      into num_lgwr_async;
    if (num_lgwr_async > 0) then
        log_transport := log_transport || 'LGWR ASYNC';
    end if;
    execute immediate 'select protection_mode from v$database'
      into protection_mode;
    if (protection_mode = 'MAXIMUM AVAILABILITY') then
      execute immediate 'select count(*) from v$archive_dest ' ||
      'where status = ''VALID'' and target = ''STANDBY'' ' ||
      'and archiver = ''LGWR'' ' ||
      'and (transmit_mode = ''SYNCHRONOUS'' or ' ||
      '     transmit_mode = ''PARALLELSYNC'') ' ||
      'and affirm = ''NO'' '
      into num_fast_sync;
      if (num_fast_sync > 0) then
        use_fast_sync := 'TRUE';
      end if;
    end if;
    execute immediate 'select fs_failover_status from v$database'
      into use_fs_failover;
    if (use_fs_failover != 'DISABLED') then
      use_fs_failover := 'TRUE';
    else
      use_fs_failover := 'FALSE';
    end if;
    execute immediate 'select count(*) from v$archive_dest_status ' ||
      'where status = ''VALID'' '||
      'and recovery_mode like ''%REAL TIME APPLY'''
      into num_realtime_apply;
    if (num_realtime_apply > 0) then
      use_realtime_apply := 'TRUE';
    end if;
    if vversion>='11' THEN
    execute immediate 'select count(*) from v$archive_dest ' ||
      'where status = ''VALID'' and target = ''STANDBY'' ' ||
      'and compression = ''ENABLE'''
      into num_compression;
      else
      num_compression:=0;
    end if;
    if (num_compression > 0) then
      use_compression := 'TRUE';
    end if;
    execute immediate 'select flashback_on from v$database'
      into use_flashback;
    if (use_flashback = 'YES') then
      use_flashback := 'TRUE';
    else
      use_flashback := 'FALSE';
    end if;
    feature_usage :=
               'Number of Standby Databases   : ' || to_char(num_standbys) ||
    CHR(13)||CHR(10) || 'Number of Cascading Databases : ' || to_char(num_casc_stby) ||
    CHR(13)||CHR(10) || 'Number of Terminal Databases  : ' || to_char(num_terminal_db) ||
    CHR(13)||CHR(10) || 'Redo Apply                    : ' || upper(use_redo_apply) ||
    CHR(13)||CHR(10) || 'SQL Apply                     : ' || upper(use_sql_apply) ||
    CHR(13)||CHR(10) || 'Far Sync Instance             : ' || upper(use_far_sync) ||
    CHR(13)||CHR(10) || 'Snapshot Standby              : ' || upper(use_snapshot) ||
    CHR(13)||CHR(10) || 'Data Guard Broker             : ' || upper(use_broker) ||
    CHR(13)||CHR(10) || 'Protection Mode               : ' || upper(protection_mode) ||
    CHR(13)||CHR(10) || 'Log Transport                 : ' || upper(log_transport) ||
    CHR(13)||CHR(10) || 'Fast Sync                     : ' || upper(use_fast_sync) ||
    CHR(13)||CHR(10) || 'Fast-Start Failover           : ' || upper(use_fs_failover) ||
    CHR(13)||CHR(10) || 'Real-Time Apply               : ' || upper(use_realtime_apply) ||
    CHR(13)||CHR(10) || 'Compression                   : ' || upper(use_compression) ||
    CHR(13)||CHR(10) || 'Flashback                     : ' || upper(use_flashback);
    feature_info := to_clob(feature_usage);
    else
      feature_info := to_clob('No Data Guard configuration detected.');
    end if;
  END IF;
  IF vdgused = 'TRUE' THEN
    FOR C1REC IN C1 LOOP
      VTRACK := VTRACK+1;
      IF VTRACK = 1 THEN
        DBMS_LOB.APPEND(C1_OUTPUT,CHR(13)||CHR(10)||CHR(13)||CHR(10)||'RESETLOGS_ID THREAD_ID LAST_OCCURED LAST_APPLIED LAST_APPLIED_TIME   DIFFERENCE DEST_ID');
        DBMS_LOB.APPEND(C1_OUTPUT,         CHR(13)||CHR(10)||'------------ --------- ------------ ------------ ------------------- ---------- -------');
        DBMS_LOB.APPEND(C2_OUTPUT,CHR(13)||CHR(10)||CHR(13)||CHR(10)||'RESETLOGS_ID THREAD_ID LAST_OCCURED LAST_APPLIED LAST_APPLIED_TIME   DIFFERENCE DEST_ID');
        DBMS_LOB.APPEND(C2_OUTPUT,CHR(13)||CHR(10)||'------------ --------- ------------ ------------ ------------------- ---------- -------');
      END IF;
      DBMS_LOB.APPEND(C1_OUTPUT, CHR(13)||CHR(10)||LPAD(C1REC.RESETLOGS_ID,12)||' '||LPAD(C1REC.THREAD_ID,9)||' '||LPAD(C1REC.LAST_OCCURED,12)||' '||LPAD(C1REC.LAST_APPLIED,12)||' '||RPAD(C1REC.LAST_APPLIED_TIME,19)||' '||LPAD(C1REC.DIFFERENCE,10)||' '||LPAD(C1REC.DEST_ID,7));
      IF C1REC.DIFFERENCE > 19 THEN
        VTRACK2 := 1;
        DBMS_LOB.APPEND(C2_OUTPUT, CHR(13)||CHR(10)||LPAD(C1REC.RESETLOGS_ID,12)||' '||LPAD(C1REC.THREAD_ID,9)||' '||LPAD(C1REC.LAST_OCCURED,12)||' '||LPAD(C1REC.LAST_APPLIED,12)||' '||RPAD(C1REC.LAST_APPLIED_TIME,19)||' '||LPAD(C1REC.DIFFERENCE,10)||' '||LPAD(C1REC.DEST_ID,7));
    END IF;
  END LOOP;
ELSE
  IF VINTERVAL!='M' THEN
  DBMS_LOB.APPEND(C1_OUTPUT,CHR(13)||CHR(10)||CHR(13)||CHR(10)||'No data to display.');
  END IF;
END IF;
  IF VINTERVAL='M' THEN
    DBMS_LOB.APPEND(C1_OUTPUT,CHR(13)||CHR(10)||CHR(13)||CHR(10)||feature_info);
  END IF;
  IF VTYPE = 'C' THEN
    IF VTRACK2 = 1 THEN
      DBMS_LOB.APPEND(C2_OUTPUT,CHR(13)||CHR(10)||CHR(13)||CHR(10)||'Data Guard may need attention.');
      SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') INTO VMDATE FROM DUAL;
      SELECT VVALUE INTO VMPREFIX FROM XYZ.ABCTBL_PARAMETERS WHERE VNAME = 'MAIL_PREFIX' and VENABLED='Y';
      SELECT HOST_NAME INTO VMHOSTNAME FROM SYS.V_$INSTANCE;
      SELECT DB_UNIQUE_NAME INTO VMDBNAME FROM SYS.V_$DATABASE;
      VMSUBJECT := VMDATE||'_'||VMPREFIX||'_'||VMHOSTNAME||'_'||VMDBNAME||' - '||VMTOPIC;
      XYZ.ABCPRO_SEND_MAIL(VMSUBJECT,C2_OUTPUT,VMAIL);
      SELECT COUNT(*) INTO VM_ERR_CNT FROM XYZ.ABCTBL_ERROR_HISTORY WHERE V_INSERT_DATE>SYSDATE-1 and V_ERR_TYPE='DATAGUARD';
      IF VM_ERR_CNT=0 THEN
          XYZ.ABCPRO_SEND_MAIL(VMSUBJECT,C2_OUTPUT,'MAIL_TO_HLP');
          execute immediate 'alter session set nls_date_format=''YYYY-MM-DD HH24:MI''';
          XYZ.ABCPRO_IMMEDIATEEXECUTION('INSERT INTO XYZ.ABCTBL_ERROR_HISTORY VALUES(SYSDATE,'''||VM_ERR_TYPE||''','''||C2_OUTPUT||''')');
      END IF;
      C1_OUTPUT := 'Mail sent.';
    ELSE
      C1_OUTPUT := 'No errors found.';
    END IF;
    IF vdgused = 'FALSE' THEN
      C1_OUTPUT := 'No data to display.';
    END IF;
  ELSIF VTYPE = 'M' THEN
    SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') INTO VMDATE FROM DUAL;
    SELECT VVALUE INTO VMPREFIX FROM XYZ.ABCTBL_PARAMETERS WHERE VNAME = 'MAIL_PREFIX' and VENABLED='Y';
    SELECT HOST_NAME INTO VMHOSTNAME FROM SYS.V_$INSTANCE;
    SELECT DB_UNIQUE_NAME INTO VMDBNAME FROM SYS.V_$DATABASE;
    VMSUBJECT := VMDATE||'_'||VMPREFIX||'_'||VMHOSTNAME||'_'||VMDBNAME||' - '||VMTOPIC;
    XYZ.ABCPRO_SEND_MAIL(VMSUBJECT,C1_OUTPUT,VMAIL);
    C1_OUTPUT := 'Mail sent.';
  ELSIF VTYPE = 'P' THEN
    NULL;
  ELSIF VTYPE = 'W' THEN
      C1_OUTPUT :=  C1_OUTPUT||CHR(13)||CHR(10)||CHR(13)||CHR(10)||CHR(13)||CHR(10);
  ELSE
    C1_OUTPUT := 'Wrong input!';
  END IF;
  RETURN C1_OUTPUT;
EXCEPTION
  WHEN OTHERS THEN
    C1_OUTPUT := C1_OUTPUT||sqlerrm;
    dbms_output.put_line(sqlerrm);
END;
/

第1行ORA-06503错误：PL/SQL：返回的函数没有值ORA-06512：在“XYZ.ABCFNC_DATAGUARD_INFO”，第374行ORA-06512：在12行
另外，当我改变异常返回空仍然不工作。

oracle

1条答案

按热度按时间
h9vpoimq

h9vpoimq1#

在EXCEPTION块中没有RETURN语句。如果你抛出一个异常，这是可以的-在这种情况下，函数将出错。但是在你的例子中，你捕获了异常，所以不会出错，它应该返回。

RETURN C1_OUTPUT;
EXCEPTION
  WHEN OTHERS THEN
    C1_OUTPUT := C1_OUTPUT||sqlerrm;
    dbms_output.put_line(sqlerrm);
    -- add a return statement. It's a function, something needs to be returned...
    RETURN C1_OUTPUT;
END;
/
赞(0）分享  回复(0）举报  2023-04-05
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com