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?

William Robertson · Accepted Answer · 2016-10-27 14:06:11Z

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.

Kacper · Accepted Answer · 2016-10-27 12:00:05Z

0

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 || '''';

edited Oct 27, 2016 at 12:00

answered Oct 27, 2016 at 10:32

Kacper

4,8182 gold badges23 silver badges35 bronze badges

2 Comments

Wernfried Domscheit Over a year ago

|| v_name || ; will fail

Kacper Over a year ago

@WernfriedDomscheit yes you're right I was missing quotes

Collectives™ on Stack Overflow

variable inside execute immediate

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related