I have a table d_default:

And procedure:

create or replace procedure cm_rtm.ins_test_subsc(p_table       in varchar2,
                                                  p_branch_code in varchar2,
                                                  p_start_date  in DATE) is
    l_tab  varchar2(30);
    l_cols varchar2(200);
    l_vals varchar2(200);
    l_str  varchar2(2000);
  begin
    select table_name,
           listagg(column_name, ',') within group (order by rowid),
           chr(39) || listagg(column_value, chr(39) ||','|| chr(39)) within group (order by rowid) ||chr(39)
    into l_tab, l_cols, l_vals
    from d_default
    where table_name = 'GCG'
    group by table_name;

    l_str := 'insert into ' || l_tab || ' (' ||
              l_cols ||') values (' || l_vals || ')';

    execute immediate l_str;
  end;

Before insering new data I need to replace BC_ID and START_DATE.

Something like like this:

update d_default
set COLUMN_VALUE = p_branch_code
where TABLE_NAME = p_table and COLUMN_NAME = 'BC_ID';

update d_default
set COLUMN_VALUE = p_start_date
where TABLE_NAME = p_table and COLUMN_NAME = 'BC_ID';

But I don't think that it's a good way.

May be there is a way, where Ican change in variables l_vals or l_str?

CodePudding user response：

Assuming that you want to use p_table rather than hardcoding GCG then you can use (with added checks for SQL injection):

create procedure /*cm_rtm.*/ins_test_subsc(
  p_table       in varchar2,
  p_branch_code in varchar2,
  p_start_date  in DATE
)
IS
  l_tab  varchar2(30);
  l_cols varchar2(200);
  l_vals varchar2(200);
  l_str  varchar2(2000);
BEGIN
  SELECT table_name,
         listagg(
           DBMS_ASSERT.ENQUOTE_NAME(column_name),
           ','
         ) within group (order by rowid),
         listagg(
           '''' || REPLACE(column_value, '''', '''''') || '''',
           ','
         ) within group (order by rowid)
  INTO   l_tab,
         l_cols,
         l_vals
  FROM   d_default
  WHERE  table_name = p_table
  AND    column_name NOT IN ('BC_ID', 'START_DATE')
  GROUP BY table_name;

  l_str := 'insert into ' || DBMS_ASSERT.ENQUOTE_NAME(p_table)
           || ' (BC_ID, START_DATE, ' || l_cols ||')'
           || ' values (:bc, :sd, ' || l_vals || ')';

  EXECUTE IMMEDIATE l_str USING p_branch_code, p_start_date;
END;
/

db<>fiddle here

CodePudding user response：

Rather than replacing them, you could - assuming those columns are always present, which is suggested by always passing in the values for them - omit them from the look-up and instead bind the passed-in values:

...
    from d_default
    where table_name = 'GCG' -- should be p_table?
    and column_name not in ('BC_ID', 'START_DATE')
    group by table_name;

    l_str := 'insert into ' || l_tab || ' (BC_ID, START_DATE, ' ||
              l_cols ||') values (:bc_id, :start_date, ' || l_vals || ')';

    execute immediate l_str using p_branch_code, p_start_date;

That will generate a dynamic statement with bind placeholders:

insert into GCG (BC_ID, START_DATE, ID) values (:bc_id, :start_date, '-1')

with the values then supplied by using.

db<>fiddle

As you're getting l_tab from the table that matches p_table, you dont' need that variable, which simplifies the query a bit too:

create or replace procedure ins_test_subsc(p_table       in varchar2,
                                           p_branch_code in varchar2,
                                           p_start_date  in DATE) is
    l_cols varchar2(200);
    l_vals varchar2(200);
    l_str  varchar2(2000);
  begin
    select listagg(column_name, ',') within group (order by rowid),
           chr(39) || listagg(column_value, chr(39) ||','|| chr(39)) within group (order by rowid) ||chr(39)
    into l_cols, l_vals
    from d_default
    where table_name = p_table
    and column_name not in ('BC_ID', 'START_DATE');

    l_str := 'insert into ' || p_table || ' (BC_ID, START_DATE, ' ||
              l_cols ||') values (:bc_id, :start_date, ' || l_vals || ')';

    dbms_output.put_line (l_str);

    execute immediate l_str using p_branch_code, p_start_date;
  end;
/

db<>fiddle

Ideally all the values would be bound, and the right data type, but that's not going to be simple with your approach. Avoiding dynamic SQL would be even better, of course.