PL/SQL Procedure Variable instead of column name in insert statement

Question

I would like to insert data into an Oracle database, but the column name where I want to insert isn't known in the first step, it depends on the user's choice.

For example:

colonne varchar(12) :=null;
...
case
  WHEN MOIS1 = 1 THEN 
    colonne :='col1';    
  WHEN MOIS1 = 2 THEN
    colonne :='col2';
  WHEN MOIS1 = 3 THEN
    colonne :='col3';
...    
insert into Mytable ( user_id, name, colonne, ...)

and it depend to the user colonne can be col1, col2 or col3, is it possible to do that?

Alex Poole · Accepted Answer · 2013-05-16 08:38:31Z

1

You can't use a variable as a column name like that, no. You could use dynamic SQL to build the insert statement and then bind the value, but since you have a limited choice you could also do this:

insert into mytable(user_id, name, col1, col2, col3)
values (v_user_id, v_name,
    case when mois1 = 1 then v_value else null end,
    case when mois1 = 2 then v_value else null end,
    case when mois1 = 3 then v_value else null end
);

You insert something into all three columns, but the case statements within the values clause determine whether you insert null or your actual value - you'll only put the real value into one of the three columns.

SQL Fiddle.

edited May 16, 2013 at 8:38

answered May 16, 2013 at 8:30

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

devosJava Over a year ago

I will try it right now, but is it possible to use case inside the sql statement ?

Alex Poole Over a year ago

@devosJava - yes; I've added an SQL Fiddle to demonstrate.

devosJava Over a year ago

Sorry, but i have another constraint,its about insert .... select (...) the logic still the same ?

Alex Poole Over a year ago

@devosJava - yes it is; another SQL Fiddle to demonstrate. Not sure why you didn't just try it though...

Collectives™ on Stack Overflow

PL/SQL Procedure Variable instead of column name in insert statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related