Dynamically select column name within the Oracle insert statement

Question

I have a table which has the structure as shown below:

SALES_RECS
---------------------------------------------------------------------
| DEPT | LOCATION | NUMBER1 | NUMBER2 | NUMBER3 | NUMBER4 | NUMBER5 |
---------------------------------------------------------------------

I have a procedure wherein I would be inserting data into this table. But while inserting the data I need to choose between column NUMBER1 to NUMBER5 based on certain criteria. So I have set this column to be chosen dynamically as shown below:

-- BELOW VALUE WOULD BE RETRIEVED DYNAMICALLY
num_val := 4 

INSERT INTO SALES_RECS(DEPT, LOCATION, NUMBER||num_val)
VALUES ('CC', 'HOUSTON', 5000);

I'm getting the following error as listed below:

PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

I'm not sure, how to choose/set the column name dynamically within the insert statement.

Thank you so much for your time and help in advance!

Littlefoot · Accepted Answer · 2020-04-08 15:02:26Z

It is dynamic SQL you need. Here's an example.

SQL> create table test (dept varchar2(2), number1 number, number2 number);

Table created.

Based on PAR_DEPT parameter value, procedure will insert either into number1 or number2 columns.

SQL> create or replace procedure p_test (par_dept in varchar2)
  2  is
  3    l_Str  varchar2(200);
  4    l_col  varchar2(30) := 'NUMBER';
  5  begin
  6    if par_dept = 'CC' then
  7       l_col := l_col || '1';
  8    else
  9       l_col := l_col || '2';
 10    end if;
 11
 12    l_str := 'insert into test (dept, ' || l_col || ')' ||
 13             '  values (' || chr(39) || par_dept || chr(39) || ', 5000)';
 14    execute immediate l_str;
 15  end;
 16  /

Procedure created.

Testing:

SQL> exec p_test('CC');

PL/SQL procedure successfully completed.

SQL> select * From test;

DE    NUMBER1    NUMBER2
-- ---------- ----------
CC       5000

SQL> exec p_test('AB');

PL/SQL procedure successfully completed.

SQL> select * From test;

DE    NUMBER1    NUMBER2
-- ---------- ----------
CC       5000
AB                  5000

SQL>

Popeye · Accepted Answer · 2020-04-08 15:33:15Z

1

You can use the static sql using DECODE as follows:

INSERT INTO SALES_RECS(DEPT, LOCATION, NUMBER1,NUMBER2,NUMBER3,NUMBER4,NUMBER5)
Select 'CC', 'HOUSTON',
       DECODE(NUM_VAL,1,5000),
       DECODE(NUM_VAL,2,5000),
       DECODE(NUM_VAL,3,5000),
       DECODE(NUM_VAL,4,5000),
       DECODE(NUM_VAL,5,5000)
  FROM DUAL;

answered Apr 8, 2020 at 15:33

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Dynamically select column name within the Oracle insert statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related