variable inside execute immediate

Question

I am using the following Procedure in pl sql

CREATE OR REPLACE "MY_PROCEDURE" (v_id number, v_name varchar2) AS
     BEGIN
        EXECUTE IMMEDIATE ' CREATE TABLE T_TEMPO ( t_id number , t_name varchar2(250) , t_value number )';
        EXECUTE IMMEDIATE 'INSERT INTO T_TEMPO (t_id , t_name, t_value)
                           SELECT id , name , value
                           from TABLE_2
                           where TABLE_2.id = || v_id ||
                           AND TABLE_2.name = || v_name || ';

But this doesn't work I have a missing expression error, I wonder it can't evaluate the value of the variables v_id and v_name inside the execute immediate. Can anyone help please?

Answer 1

You'll either need to quote the values (using quotation marks where necessary):

create or replace procedure my_procedure
    ( v_id number
    , v_name varchar2 )
as
begin
    execute immediate 'CREATE TABLE T_TEMPO (t_id number, t_name varchar2(250), t_value number )';
    execute immediate 'INSERT INTO T_TEMPO (t_id, t_name, t_value)
                       SELECT id, name, value
                       FROM table_2
                       WHERE table_2.id = ' || v_id ||
                     ' AND table_2.name = ''' || v_name || '''';
end my_procedure;

or (generally better) pass them as bind variables:

create or replace procedure my_procedure
    ( v_id number
    , v_name varchar2 )
as
begin
    execute immediate 'CREATE TABLE T_TEMPO (t_id number, t_name varchar2(250), t_value number )';
    execute immediate 'INSERT INTO T_TEMPO (t_id, t_name, t_value)
                       SELECT id, name, value
                       FROM table_2
                       WHERE table_2.id = :id
                       AND table_2.name = :name' using v_id, v_name;
end my_procedure;

btw I would use the v prefix for variables and p for parameters.

Answer 2

Please use variables outside quotation:

   EXECUTE IMMEDIATE ' CREATE TABLE T_TEMPO ( t_id number , t_name varchar2(250) , t_value number )';
    EXECUTE IMMEDIATE 'INSERT INTO T_TEMPO (t_id , t_name, t_value)
                       SELECT id , name , value
                       from TABLE_2
                       where TABLE_2.id = ' || v_id ||
                       ' AND TABLE_2.name = '''|| v_name || '''';