Sample code to create where condition dynamically in a function , which can be used as dynamic cursors.
CREATE OR REPLACE PROCEDURE apps.check_refcur (
where_in IN VARCHAR2 := NULL
) IS
TYPE cv_type IS REF CURSOR;
CV cv_type;
v_inventory_item_id NUMBER;
v_segment1 VARCHAR2 (100);
l_v_sql_string VARCHAR2 (2000);
BEGIN
l_v_sql_string :=
'SELECT msik.inventory_item_id
, msik.segment1
FROM mtl_system_items_kfv msik
WHERE 1 = 1
AND msik.organization_id = 83
AND msik.inventory_item_id IN ( '
|| where_in
|| ')';
OPEN CV FOR l_v_sql_string;
LOOP
FETCH CV
INTO v_inventory_item_id
,v_segment1;
EXIT WHEN CV%NOTFOUND;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO TEST
VALUES ( 'Exception'
|| SYSDATE
);
COMMIT;
END;
/
Hi
ReplyDeleteThanks for providing the code to create a dynamic cursor. Will this code easily work on the latest version too ? Also please so provide some details of this code to explain what actually is happening. It becomes little uneasy to understand without proper comments.
ReplyDeleteThanks for taking time to provide the steps.
ReplyDelete