关于Forms_ddl的应用
最近开发一个Form,需要用动态SQL,在网上搜索,发现可以用Forms_ddl来实现。试了很久,不成功,后发现是字符串连接的方式有问题,修正后终于OK. 以下是代码,供大家参考!
DECLARE
CURSOR c1
IS
SELECT COUNT (1)
FROM inv_customs_items
WHERE inventory_items = :find.inventory_items;
v_count NUMBER;
p_attribute_no VARCHAR2 (30);
p_attribute VARCHAR2 (30);
p_sql VARCHAR2 (200);
p_inventory_items VARCHAR2 (30);
p_user_id NUMBER;
BEGIN
IF :find.inventory_items IS NULL
THEN
show_alert_message ('Inventory_items 必须输入!');
RAISE form_trigger_failure;
END IF;
OPEN c1;
FETCH c1
INTO v_count;
CLOSE c1;
IF v_count < 1
THEN
INSERT INTO inv_customs_items
(inventory_items, created_by, creation_date
)
VALUES (:find.inventory_items, :parameter.user_id, SYSDATE
);
END IF;
p_user_id:= :parameter.user_id;
GO_BLOCK ('item');
FIRST_RECORD;
LOOP
EXIT WHEN :item.attribute_name IS NULL;
p_attribute_no := :item.attribute_no;
p_attribute := :item.ATTRIBUTE;
p_inventory_items := :find.inventory_items;
BEGIN
p_sql :=
'update INV_CUSTOMS_ITEMS set LAST_UPDATE_DATE=sysdate,LAST_UPDATED_BY='
|| to_char(p_user_id)
|| ','
|| p_attribute_no
|| '= '''
|| p_attribute
|| ''' where inventory_items ='''
|| p_inventory_items
|| '''';
FORMS_DDL (p_sql);
EXCEPTION
WHEN OTHERS
THEN
show_alert_message ('保存失败!');
RAISE form_trigger_failure;
END;
NEXT_RECORD;
END LOOP;
COMMIT_FORM;
FIRST_RECORD;
show_alert_message ('恭喜您,保存成功');
END;