Re: DBMS_SQL - dynamic SQL

[Quoted] 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;