FND_MESSAGE_PUB.get及error_handler.get_message用法

1. FND_MESSAGE_PUB.get用法,调用标准API时，抓取错误信息时可以用到，如导入供应商API：IBY_EXT_BANKACCT_PUB

DECLARE
  x_msg_count NUMBER;

  x_msg_data  VARCHAR2(3000);

  v_msg_index_out NUMBER;
  v_data          VARCHAR2(1000);
BEGIN

  fnd_message.set_name('IBY', 'IBY_UNIQ_ACCOUNT');--设置消息名称
  fnd_message.set_name('IBY', 'test');
  fnd_msg_pub.add;--添加到Global Area

  fnd_message.set_name('FND', 'AFDICT- DATABASE STATS');--设置消息名称
  fnd_message.set_token('UPDATES', 'dragon123');--设置消息内容中变量的值
  fnd_message.set_token('INSERTS', 'dragon321');--设置消息内容中变量的值
  fnd_msg_pub.add;--添加到Global Area

--当x_msg_count 为1时，直接输出x_msg_data，否则需要循环输出
  fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
                            p_count   => x_msg_count,
                            p_data    => x_msg_data);

  dbms_output.put_line('x_msg_count:' || x_msg_count);
  dbms_output.put_line('x_msg_data:' || x_msg_data);

  FOR k IN 1 .. x_msg_count LOOP
    fnd_msg_pub.get(p_msg_index     => k,
                    p_encoded       => fnd_api.g_false,
                    p_data          => v_data,
                    p_msg_index_out => v_msg_index_out);
 dbms_output.put_line('(' || v_msg_index_out || ')->' || v_data);
  END LOOP;
  fnd_msg_pub.delete_msg();

  --app_exception.raise_exception;
END;

2. error_handler.get_message用法，调用BOM导入API时，抓取错误信息

IF x_msg_count > 0 THEN
    FOR l_index IN 1 .. x_msg_count LOOP
      IF l_index>0 THEN
      DECLARE
        x_index VARCHAR2(10);
        x_msg_data_tmp VARCHAR2(2000);
        x_entity_id VARCHAR2(10);
        x_message_type VARCHAR2(10);
      BEGIN
        error_handler.get_message
                     (x_entity_index => x_index,
                      x_message_text => x_msg_data_tmp,
                      x_entity_id    => x_entity_id,
                      x_message_type => x_message_type);
     --   dbms_output.put_line(l_index||':'||x_index);
     dbms_output.put_line(l_index||x_entity_id||':'||x_msg_count);
        x_msg_data := x_msg_data || x_msg_data_tmp;

        FOR i IN 1 .. round(length(x_msg_data_tmp) / 255) + 1 LOOP
          dbms_output.put_line(substr(x_msg_data_tmp, (i - 1) * 254, 254));
        END LOOP;
      END;
      END IF;
    END LOOP;

  END IF;

3. error_handler.get_message_list用法

x_message_list  error_handler.error_tbl_type;

error_handler.get_message_list(x_message_list => x_message_list);
      FOR i IN 1 .. x_message_list.COUNT LOOP
        dbms_output.put_line('BO Identifier   : ' || x_message_list(i)
                             .bo_identifier);
        dbms_output.put_line('Organization ID : ' || x_message_list(i)
                             .organization_id);
        dbms_output.put_line('Entity ID       : ' || x_message_list(i)
                             .entity_id);
        dbms_output.put_line('Message Text    : ' ||
                             substr(x_message_list(i).message_text, 1, 200));
        dbms_output.put_line('Entity Index    : ' || x_message_list(i)
                             .entity_index);
        dbms_output.put_line('Message Type    : ' || x_message_list(i)
                             .message_type);
        dbms_output.put_line('Table Name      : ' || x_message_list(i)
                             .table_name);
        dbms_output.put_line('Row Identifier  : ' || x_message_list(i)
                             .row_identifier);
      END LOOP;