How to programmatically set table name in PL/SQL?

Question

I created the following simple PL/SQL stored procedure example to ask a specific question. This procedure inserts an employee name and id number into a table called employees_???. The ??? is explained below.

PROCEDURE hire_employee (emp_id IN INTEGER, name IN VARCHAR2, country IN VARCHAR2) 
AS
BEGIN
    INSERT INTO employees_??? VALUES (emp_id, name, 1000);
END hire_employee;

What I need is to set the table name based on the IN variable country. For example,

If country = 'usa', I want the INSERT line to read:

INSERT INTO employees_usa VALUES (emp_id, name, 1000);

If country = 'germany', I want the INSERT line to read:

INSERT INTO employees_germany VALUES (emp_id, name, 1000);

If country = 'france', I want the INSERT line to read:

INSERT INTO employees_france VALUES (emp_id, name, 1000);

etc...

Is there a way to do this in PL/SQL by substituting something in place of employee_??? so only one line of code for INSERT is used? Or is using a case or if/then/else statement the best way?

Your DB model is completely broken. You should only have a single table employee with a column named country. Instead of fighting the problems that you get with this wrong model you should immediately re-design it. — user330315
– user330315, Commented Mar 31, 2012 at 17:24
Yes, I agree -- it's just an example created to figure out how to get the IN parameter onto a variable name in pl/sql. — ggkmath
– ggkmath, Commented Mar 31, 2012 at 17:27
Please provide an example that truly describes what you're trying to accomplish. — DCookie
– DCookie, Commented Mar 31, 2012 at 17:33
OK, I've created a new question here: stackoverflow.com/questions/9959799/… — ggkmath
– ggkmath, Commented Mar 31, 2012 at 21:23

Ben · Accepted Answer · 2021-01-05 09:23:18Z

To answer your question, you have to use execute immediate and create your statement dynamically.

create or replace procedure hire_employee (
        emp_id IN INTEGER
      , name IN VARCHAR2
      , country IN VARCHAR2 ) is

   -- maximum length of an object name in Oracle is 30
   l_table_name varchar2(30) := 'employees_' || country;

begin
    execute immediate 'insert into ' || l_table_name
                       || ' values (:1, :2, 1000)'
      using emp_id, name;
end hire_employee;

However, this is a massively over-complicated way of storing the data. If you want to select all data you have to union large numbers of tables.

It would be far better to normalise the database properly and add country to an employees table.

Something like the following:

create table employees (
    emp_id number(16)
  , country varchar2(3) -- ISO codes
  , name varchar2(4000) -- maximum who knows what name people might have
  , < other_columns >
  , constraint pk_employees primary key ( emp_id )
    );

Your procedure then becomes a very simple insert statement:

create or replace procedure hire_employee (
       emp_id in integer
     , name in varchar2
     , country in varchar2 ) is

    insert into employees
    values ( emp_id, country, name, 1000 );

end hire_employee;

Chetter Hummin · Accepted Answer · 2012-03-31 17:18:14Z

2

You can use dynamic SQL and the EXECUTE IMMEDIATE construct. In this, you construct the query as a string and then execute it. A good example is at http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

answered Mar 31, 2012 at 17:18

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to programmatically set table name in PL/SQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related