exception - PL SQL 函数没有引发用户定义的异常
问题描述
我已经定义了一个用户定义的异常,但我的函数没有引发异常。
****规格*****
FUNCTION get_property_id (
p_owner IN app.bc_entities.entity_id%TYPE,
p_object_type_id IN app.bc_custom_attribute_masters.object_type_id%TYPE,
p_custom_attribute_master_id IN
app.bc_custom_attribute_masters.custom_attribute_master_id%TYPE,
p_non_rev_controlled_p IN NUMBER DEFAULT 0
)
RETURN NUMBER;
****身体*****
FUNCTION get_custom_attribute_master_id (
p_owner IN app.bc_entities.entity_id%TYPE,
p_object_type_id IN app.bc_custom_attribute_masters.object_type_id%TYPE,
p_property_id IN NUMBER,
p_non_rev_controlled_p IN NUMBER DEFAULT 0
)
RETURN app.bc_custom_attribute_masters.custom_attribute_master_id%TYPE
IS
l_custom_attribute_master_id
APP.BC_CUSTOM_ATTRIBUTE_MASTERS.CUSTOM_ATTRIBUTE_MASTER_ID%TYPE;
l_object_property_id NUMBER := 0;
v_sql varchar2(32767);
r_error bc_errors%rowtype;
TYPE templatecurtype IS REF CURSOR;
c_result templatecurtype;
l_column_name VARCHAR2(50);
invalid_property_id EXCEPTION ;
BEGIN
r_error.code_unit := 'Get_Custom_Attribute_Master_Id';
r_error.error_context := 'Calling Get_Custom_Attribute_Master_Id ';
r_error.error_data := ' Get_Custom_Attribute_Master_Id :Property Id :'||to_char(p_property_id)||', OBJECT_ID:'||to_char(p_object_type_id);
FOR V_ROW IN
(SELECT * FROM app.CUSTOM_PROPERTY_TABLES_DEFS WHERE object_type_id =
p_object_type_id AND
(
(p_object_type_id = 20 AND REV_CONTROLLED_P = p_non_rev_controlled_p) OR (p_object_type_id <> 20 AND NVL(REV_CONTROLLED_P,0) = NVL(p_non_rev_controlled_p,0) ) )
)
LOOP
IF v_row.FIRST_PROPERTY_COLUMN_NAME IS NOT NULL THEN
l_column_name := v_row.FIRST_PROPERTY_COLUMN_NAME;
ELSE
l_column_name := v_row.PROPERTY_COLUMN_NAME;
END IF;
v_sql := ' SELECT civ.'||l_column_name || ' FROM '||v_row.table_name ||' civ WHERE '||
v_row.property_column_name ||' = :property_id ';
dbms_output.put_line ( '1-v_sql '||v_sql);
r_error.error_data := v_sql;
OPEN c_result FOR v_sql
USING p_property_id ;
FETCH c_result INTO l_object_property_id ;
CLOSE c_result ;
IF l_object_property_id IS NULL THEN
RAISE invalid_property_id;
END IF;
v_sql :='Select civ.custom_attribute_master_id FROM ' || v_row.table_name||' civ WHERE civ.' || l_column_name || '= :property_id and civ.custom_attribute_master_id is not null
and rownum = 1';
dbms_output.put_line ( '2-v_sql '||v_sql);
OPEN c_result FOR v_sql USING l_object_property_id ;
FETCH c_result INTO l_custom_attribute_master_id;
CLOSE c_result ;
RETURN l_custom_attribute_master_id ;
END LOOP;
EXCEPTION
WHEN invalid_property_id THEN
r_error.error_stack := dbms_utility.format_call_stack()||chr(10)||dbms_utility.format_error_backtrace();
r_error.message := ' p_property_id ( '||p_property_id||' ) is invalid ';
r_error.error_code := SQLCODE;
r_error.notified_p := null;
PKG_ERROR.LOG(p_code_unit=>r_error.code_unit
,p_message=>r_error.message
,p_application=>V_APPLICATION
,p_database_name=>V_DB_NAME
,p_error_owner=>p_owner
,p_error_user=>p_owner
,p_error_module=>V_MODULE
,p_error_object=>V_APP_OBJECT
,p_error_type=>app.pkg_error.error
,p_error_code=>r_error.error_code
,p_error_stack=>r_error.error_stack
,p_error_context=>r_error.error_context
,p_error_data=>r_error.error_data
,p_notified_p=>r_error.notified_p);
RAISE_APPLICATION_ERROR ( -20098, r_error.message ) ;
WHEN OTHERS THEN
r_error.error_stack := dbms_utility.format_call_stack()||chr(10)||dbms_utility.format_error_backtrace();
r_error.message := SQLERRM;
r_error.error_code := SQLCODE;
r_error.notified_p := null;
PKG_ERROR.LOG(p_code_unit=>r_error.code_unit
,p_message=>r_error.message
,p_application=>V_APPLICATION
,p_database_name=>V_DB_NAME
,p_error_owner=>p_owner
,p_error_user=>p_owner
,p_error_module=>V_MODULE
,p_error_object=>V_APP_OBJECT
,p_error_type=>app.pkg_error.error
,p_error_code=>r_error.error_code
,p_error_stack=>r_error.error_stack
,p_error_context=>r_error.error_context
,p_error_data=>r_error.error_data
,p_notified_p=>r_error.notified_p);
RAISE;
END get_custom_attribute_master_id;
解决方案
推荐阅读
- firebase - 在同一应用程序中将 google 数据存储更改为 Firestore
- regex - 在 Hive 中使用正则表达式获取匹配数?
- excel - 粘贴范围返回变量范围的语法错误
- java - 合并排序和数组
- macos - Mac OS X 10.6 Swift 4.2 Xcode 10.0:此类与键的键值编码不兼容
- javascript - WKWebview evaluateJavaScript in swift 仅适用于某些网站
- android - 在 Android 的另一个程序中访问选定的文本
- c# - 带有图形的自动滚动面板
- javascript - “beforeunload”和“unload”事件如何工作?
- ios - Swift 4:无法让 NSPredicate 与 searchBar 和本地领域一起使用