How to use PL SQL Constants in Insert statement

Question

I am new to PL SQL Code and need help writing Insert query statement . eg: I want to insert Employee in Employees table where only employee name changes but the city is constant.

DECLARE
  emp_var_prefix varchar2(12) := 'QATEST';
  emp_var_temp varchar2(12) := '';
  city constant varchar(30) := 'dallas'

begin
  DBMS_OUTPUT.ENABLE;
  for i in 1..2 loop
    emp_var_temp  := emp_var_prefix;
    emp_var_temp := emp_var_prefix ||i;

    INSERT INTO EMPLOYEE TABLE ('EMPLOYEE_NAME', 'CITY') values ('emp_var_temp', '<what should I put here for constant dallas for city name>');

    DBMS_OUTPUT.PUT_LINE(emp_var_temp);
  end loop;

END;

Justin Cave · Accepted Answer · 2011-04-04 20:38:54Z

2

It appears that you would want something like

DECLARE
  l_emp_var_prefix varchar2(12) := 'QATEST';
  l_emp_var_temp varchar2(12) := '';
  l_city constant varchar(30) := 'dallas'
begin
  DBMS_OUTPUT.ENABLE;
  for i in 1..2 loop
    l_emp_var_temp := l_emp_var_prefix ||i;

    INSERT INTO EMPLOYEE(employee_name, city) 
      values (l_emp_var_temp, l_city );

    DBMS_OUTPUT.PUT_LINE(l_emp_var_temp);
  end loop;

END;

It is a bad idea in general to use variable names that are also column names-- that makes it exceedingly likely that you're going to encounter scope bugs where you intend to refer to a variable and the compiler inadvertently assumes that you meant to refer to the column. Those are rather nasty to debug. It's easier to adopt some convention like prefixing local variables with l_ to differentiate them from column names or parameters that can be prefixed with p_.

answered Apr 4, 2011 at 20:38

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

4 Comments

Secpert1 Over a year ago

Thanks justin. Will keep that in mind. Although my original question still remains " how to insert constant value in insert select statement : In above case, city

Justin Cave Over a year ago

@Secpert1 - Unless I'm missing something, this is showing you how to insert the constant l_city in your INSERT statement. Are you explicitly trying to understand how to force Oracle to use a variable with the same name as a column in the INSERT statement?

Secpert1 Over a year ago

My bad. I defined local variables with l_ and ran the insert statement again and it ran successfully and I was able to insert constant in the table. Thanks a lot Justin! Appreciate it .

Ronnis Over a year ago

+1 for mentioning the scoping issues. It can turn very nasty indead.

Collectives™ on Stack Overflow

How to use PL SQL Constants in Insert statement

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related