Dynamic Cursor with parameterised schema name and using for BULK INSERT in target table

Question

I have source_table in different 22 schemas and need procedure to create for bulk collect and insert into same target table in oracle stored procedure.

I'm trying and not getting records inserted getting error ORA-00911: invalid character but there is all column from select cursor and traget_table are same in order.

CREATE OR REPLACE PROCEDURE proc_bulk_circle(p_limit         IN PLS_INTEGER DEFAULT 10000,
                                             p_activity_date IN DATE,
                                             p_circle        IN VARCHAR2) AS
  CURSOR act_cur IS
    SELECT activity_date,
           circle
    FROM   circle_load_control
    WHERE  activity_date = p_activity_date
    AND    circle = circle;

  TYPE type_i6 IS TABLE OF act_cur%ROWTYPE INDEX BY BINARY_INTEGER;
  i_tab6 type_i6;

  v_count   NUMBER := 0;
  lv_circle VARCHAR2(2);
  lv_schema VARCHAR2(20);

  TYPE rc IS REF CURSOR;
  con_sap_cur rc;
  TYPE con_sap_resp IS TABLE OF target_table%ROWTYPE INDEX BY BINARY_INTEGER;
  i_tab1      con_sap_resp;
  lv_sql_stmt VARCHAR2(32767);

BEGIN

  IF p_circle = 'MUM'
  THEN
    lv_circle := 'MU';
    lv_schema := 'MUMBAI';
  ELSIF p_circle = 'MAH'
  THEN
    lv_circle := 'MH';
    lv_schema := 'MHRSTR';
  ELSE
    lv_circle := NULL;
  END IF;

  FOR myindex IN act_cur
  LOOP
    i_tab6(v_count) := myindex;
    v_count := v_count + 1;
  END LOOP;

  FOR myindex IN i_tab6.first .. i_tab6.last
  LOOP
    IF i_tab6(myindex).activity_date = p_activity_date
        AND i_tab6(myindex).circle = p_circle
    THEN

      BEGIN
        lv_sql_stmt := 'SELECT acc_id     code,
                               cust_id c_id,
                               addr_1      address2,
                               addr_2      address3,
                               addr_3      address4,
                               (SELECT SUM(abc) FROM ' || lv_schema || '.details WHERE <some condition with t1> GROUP BY <columns>) main_charges,
                               (SELECT SUM(extra_charge) FROM ' || lv_schema || '.details WHERE <some condition with t1>  GROUP BY <columns>) extra_charges 
                         FROM  ' || lv_schema || '.main_source_details t1
                         WHERE t1.activity_date  = ''' || p_activity_date || ''';';

        OPEN con_sap_cur FOR lv_sql_stmt;

        LOOP
          FETCH con_sap_cur BULK COLLECT
            INTO i_tab1 LIMIT p_limit;

          FORALL i IN 1 .. i_tab1.count
            INSERT INTO target_table (column list....)
            VALUES(I_TAB1(i).col1,......;

          EXIT WHEN con_sap_cur%NOTFOUND;
        END LOOP;

        COMMIT;

        CLOSE con_sap_cur;

      EXCEPTION
        WHEN OTHERS THEN
          dbms_output.put_line('ERR target_table: ' || SQLCODE || '-' || SQLERRM);
      END;

    ELSE
      dbms_output.put_line(p_activity_date || ' DATE IS NOT MATCH');
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
END proc_bulk_circle;
/

Answer 1

You're doing an awful lot of work here, if your purpose is to insert some rows.

Instead, you could do the insert and select in one go, something like:

CREATE OR REPLACE PROCEDURE proc_bulk_circle(p_activity_date IN DATE,
                                             p_circle        IN VARCHAR2) AS
  lv_circle VARCHAR2(2);
  lv_schema VARCHAR2(20);

  v_query CLOB;

  e_table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_table_does_not_exist, -00942);
BEGIN
  IF p_circle = 'MUM'
  THEN
    lv_circle := 'MU';
    lv_schema := 'MUMBAI';
  ELSIF p_circle = 'MAH'
  THEN
    lv_circle := 'MH';
    lv_schema := 'MHRSTR';
  END IF;

  IF lv_schema IS NOT NULL
  THEN
    -- asserting the schema name to avoid sql injection
    -- also using a bind variable for the activity_daate predicates
    v_query := 'INSERT INTO target_table (<column list>)' || CHR(10) ||
               '  WITH main_dets AS (SELECT acc_id,' || CHR(10) ||
               '                            cust_id,' || CHR(10) ||
               '                            addr_1,' || CHR(10) ||
               '                            addr_2,' || CHR(10) ||
               '                            addr_3,' || CHR(10) ||
               '                            (SELECT SUM(abc) FROM ' || dbms_assert.simple_sql_name(lv_schema) || '.details WHERE <some condition with t1>) main_charges,' || CHR(10) || -- no need for the group by
               '                            (SELECT SUM(extra_charge) FROM ' || dbms_assert.simple_sql_name(lv_schema) || '.details WHERE <some condition with t1>) extra_charges' || CHR(10) || -- no need for the group by
               '                     FROM   ' || dbms_assert.simple_sql_name(lv_schema) || '.main_source_details t1' || CHR(10) ||
               '                     WHERE  activity_date = :p_activity_date)' || CHR(10) ||
               '         circles AS (SELECT activity_date,' || CHR(10) ||
               '                            circle' || CHR(10) ||
               '                     FROM   circle_load_control' || CHR(10) ||
               '                     WHERE  activity_date = :p_activity_date' || CHR(10) ||
               '                     AND    circle = circle)' || CHR(10) || -- did you really mean circle = circle here? This is equivalent to 1=1 (unless circle is null) and is therefore pretty irrelevant! If you want to exclude rows with null values, use "circle is not null" instead
               '  SELECT md.acc_id,' || CHR(10) ||
               '         md.cust_id,' || CHR(10) ||
               '         md.addr_1,' || CHR(10) ||
               '         md.addr_2,' || CHR(10) ||
               '         md.addr_3,' || CHR(10) ||
               '         md.main_charges,' || CHR(10) ||
               '         md.extra_charges' || CHR(10) ||
               '  FROM   main_dets md' || CHR(10) ||
               '         CROSS JOIN circles c';

    EXECUTE v_query USING p_activity_date, p_activity_date;

    COMMIT;
  ELSE
    raise_application_error(-20001, 'Invalid circle specified: "' || p_circle || '"');
  END IF;
END proc_bulk_circle;
/

(N.B. untested.)

I've assumed that activity_date and circle in circle_load_control aren't unique; if they are, you could avoid the cross join and just have an implicit cursor to fetch the row prior to doing the IF p_circle = ... checks.

Answer 2

I believe this comes down to you having a ; in your definition of the sql (see below line)

WHERE t1.activity_date  = ''' || p_activity_date || ''';';

when you are defining SQL for dynamic use (and opening a cursor this way is dynamic) you do not include the ;

To show this I have done a shorter example. The below will error in the same way as yours.

declare

v_sql varchar2(100) default 'select ''X'' from dual;';

TYPE rc IS REF CURSOR;
  v_cur rc;

type l_tab_type is table of varchar2(1);
l_tab l_tab_type;

begin

open v_cur for v_sql;
loop 
   fetch v_cur bulk collect into l_tab;
   exit;
end loop;
CLOSE v_cur;
end;
/

but simply remove the ; from the line

v_sql varchar2(100) default 'select ''X'' from dual;';

end it all works fine, fixed example below.

declare

v_sql varchar2(100) default 'select ''X'' from dual';

TYPE rc IS REF CURSOR;
  v_cur rc;

type l_tab_type is table of varchar2(1);
l_tab l_tab_type;

begin

open v_cur for v_sql;
loop 
   fetch v_cur bulk collect into l_tab;
   exit;
end loop;
CLOSE v_cur;
end;
/