Re: DBMS_SQL - dynamic SQL

Thanks I'll check it out and hopefully get this thing working

Sean

"J. McLean" wrote:

> Here is an example of some stuffed I've used.
> ---------------------------------------------------------------
>
> v_sql := 'select st, ot, dt, wdate from falcon.employee_time_sheet
> where employee_id = :a and wdate >= :b and wdate <= :b + 6 and job_id = :c
> and task_id = :d and ';
>
> cursor1 := dbms_sql.open_cursor;
>
> if data_block(i).subtask_id is null then
> v_sql := v_sql || ' subtask_id is null and ';
> else
> v_sql := v_sql || ' subtask_id = :e and ';
> end if;
>
> if data_block(i).ecr_id is null then
> v_sql := v_sql || ' ecr_id is null and ';
> else
> v_sql := v_sql || ' ecr_id = :f and ';
> end if;
>
> if data_block(i).ja_id is null then
> v_sql := v_sql || ' ja_id is null';
> else
> v_sql := v_sql || ' ja_id = :g';
> end if;
>
> dbms_sql.parse(cursor1, v_sql, 2);
>
> if data_block(i).subtask_id is not null then
> dbms_sql.bind_variable(cursor1, ':e', data_block(i).subtask_id);
> end if;
> if data_block(i).ecr_id is not null then
> dbms_sql.bind_variable(cursor1, ':f', data_block(i).ecr_id);
> end if;
>
> if data_block(i).ja_id is not null then
> dbms_sql.bind_variable(cursor1, ':g', data_block(i).ja_id);
> end if;
>
> dbms_sql.bind_variable(cursor1, ':a', data_block(i).employee_id);
> dbms_sql.bind_variable(cursor1, ':b', p_monday);
> dbms_sql.bind_variable(cursor1, ':c', data_block(i).job_id);
> dbms_sql.bind_variable(cursor1, ':d', data_block(i).task_id);
>
> dbms_sql.define_column(cursor1, 1, col_st);
> dbms_sql.define_column(cursor1, 2, col_st);
> dbms_sql.define_column(cursor1, 3, col_st);
> dbms_sql.define_column(cursor1, 4, col_date);
>
> n_returned := dbms_sql.execute (cursor1);
>
> --get st/ot/dt for the settings for each day of the week
> while dbms_sql.fetch_rows (cursor1) > 0
> loop
> dbms_sql.column_value(cursor1, 4, d_temp);
> v_day := rtrim(to_char(d_temp, 'day'));
> if v_day = 'monday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).monday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).monday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).monday_dt);
> elsif v_day = 'tuesday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).tuesday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).tuesday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).tuesday_dt);
> elsif v_day = 'wednesday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).wednesday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).wednesday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).wednesday_dt);
> elsif v_day = 'thursday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).thursday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).thursday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).thursday_dt);
> elsif v_day = 'friday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).friday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).friday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).friday_dt);
> elsif v_day = 'saturday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).saturday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).saturday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).saturday_dt);
> elsif v_day = 'sunday' then
> dbms_sql.column_value(cursor1, 1, data_block(i).sunday_st);
> dbms_sql.column_value(cursor1, 2, data_block(i).sunday_ot);
> dbms_sql.column_value(cursor1, 3, data_block(i).sunday_dt);
> end if;
> end loop;
> dbms_sql.close_cursor(cursor1);
> i := i + 1;
> end loop;
> close settings;
Received on Wed Aug 23 2000 - 16:07:51 CEST