Popular Posts
SwiXml SwiX ml , is a small GUI generating engine for Java applications and applets. Graphical User Interfaces are described in XML documents that ... Temporary Tables Temporary Table create table #temptable { id int, name nvarchar(50) }; select * into #temptable from UserTable; Features Table name ... File properties FOR %%? IN (file_to_be_queried) DO ( ECHO File Name Only : %%~n? ECHO File Extension : %%~x? ECHO Name in 8.3 notati...
Blog Archive
Stats
EXECUTE IMMEDIATE : execute string query
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;