SET SERVEROUTPUT ON;
/* execute query */
DECLARE
BEGIN
EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL';
END;
/* execute query & set value */
DECLARE
v_time DATE;
BEGIN
DBMS_OUTPUT.ENABLE;
EXECUTE IMMEDIATE 'SELECT CURRENT_DATE FROM DUAL' INTO v_time;
DBMS_OUTPUT.PUT_LINE(v_time);
END;
/* execute query with parameter */
DECLARE
v_emp_id VARCHAR2(20);
v_name VARCHAR2(20);
BEGIN
v_name := 'Bruce';
v_emp_id := '00987';
EXECUTE IMMEDIATE 'UPDATE employee SET employee_name = :1 WHERE employee_id = :2'
USING v_name, v_emp_id;
COMMIT;
END;
/* execute procedure */
DECLARE
v_start_index NUMBER := 24;
v_end_index NUMBER:= 587;
v_sum NUMBER;
v_status NUMBER(1,0);
BEGIN
DBMS_OUTPUT.ENABLE;
EXECUTE IMMEDIATE 'BEGIN get_lot_amount(:1, :2, :3); END;'
USING IN v_start_index, IN v_end_index, OUT v_sum, IN OUT v_status;
IF v_status = 0 THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END IF;
END;