Oracle EBS
动态SQL/*SELECT msi.inventory_item_id, msi.segment1
FROM mtl_system_items_b msi
WHERE msi.organization_id = 142
AND msi.inventory_item_id = 26505;*/
DECLARE
p_organization_id NUMBER := 142;
p_inventory_item_id NUMBER := 26505;
l_item_number VARCHAR2(240);
BEGIN
EXECUTE IMMEDIATE
'select segment1
from mtl_system_items_b msi
where msi.organization_id = :1 and msi.inventory_item_id = :2'
INTO l_item_number
USING p_organization_id, p_inventory_item_id;
dbms_output.put_line('l_item_number ：' || l_item_number);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line('no data found');
END;
动态游标
可在游标字符串里拼接指定定字符串，
或者通过”：“指定这是一个被拼接的 字符串，最后在打开游标是使用using把确定的字符串拼接进去。
FUNCTION get_old_character_value(l_plan_id IN NUMBER,
l_organizationIN NUMBER,
l_wip_entity_id IN NUMBER,
l_char_name IN VARCHAR2) RETURN VARCHAR2 IS
TYPE result_cur_type IS REF CURSOR;
result_cur result_cur_type;
l_sql VARCHAR2(500);
l_character_number VARCHAR2(20);
l_character_value VARCHAR2(4000);
BEGIN
l_character_number := get_character_number(l_plan_id,
l_char_name);
l_sql := 'SELECT DISTINCT qr.' || l_character_number ||
' FROM qa_results qr
WHERE qr.plan_id = :1
AND qr.organization_id = :2
AND qr.wip_entity_id = :3';
--log(l_sql);
OPEN result_cur FOR l_sql
USING l_plan_id, l_organization, l_wip_entity_id;
FETCH result_cur
INTO l_character_value;
CLOSE result_cur;
RETURN l_character_value;
END;
页:
[1]