DEFINE vs DECLARE - escaping quotes

Question

I have defined a variable

define myStrings = "'abc','def'"

which I later need to use inside a procedure block and convert into a table of varchars

declare
    type varcharListType is table of varchar(200);
    myList varcharListType;

begin
    myList := varcharListType(&myStrings);
    .
    .
    .
end;
/

I am attempting to use either the variable or the table inside an IN clause in a create query within the procedure block

execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (&myStrings) ';

I have tried using the REPLACE function also

myNewStrings := replace(&myStrings, '''' , '''''');

but I get an exception related to abc and def not being defined.

ISSUE:

I am getting a syntax exception because the quotes around abc and def in myString are not escaped. The value "'abc','def'" must be 'defined' rather then 'declared' so it is substituted later.

QUESTION:

Is it possible to 'define' a variable in such a way that I can use it both as table type values and also a string in the execute immediate statement?

TO REPRODUCE:

Create

create table bar (bar_id number not null, bar_val varchar2(20), 
  constraint bar_pk primary key (bar_id)
  enable 
);

Insert

insert into bar (bar_id, bar_val)
values (1, 'abc'),
       (2, 'def'),
       (3, 'ghi');

SAMPLE PROCEDURE

set verify off;
set serveroutput on; 

define myStrings = "'abc','def'"

declare
    type varcharListType is table of varchar(20);
    myList varcharListType;

begin
    myList := varcharListType(&myStrings);

    execute immediate 'create table tmp_foo '
                   || 'as select * from bar '
                   || 'where bar_val in (&myStrings) ';

    for i in myList.FIRST..myList.LAST loop
        dbms_output.put_line('VALUE: ' || myList(i));
    end loop;
end;
/

set serveroutput off;
set verify on;

possible duplicate of [PL/SQL, how to escape single quote in a string?](http://stackoverflow.com/questions/11315340/pl-sql-how-to-escape-single-quote-in-a-string) — user272735, Jul 15 '14 at 08:52
Quote the `execute immediate` string with [alternative quoting mechanism](http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm). See the duplicate for more details. — user272735, Jul 15 '14 at 08:54
Or you can just double up the single quotes, as in `define myStrings = "''abc'',''def''"`. Share and enjoy. — Bob Jarvis - Слава Україні, Jul 15 '14 at 10:35
possible duplicate of [Escaping single quote in PLSQL](http://stackoverflow.com/questions/6678478/escaping-single-quote-in-plsql) — Bob Jarvis - Слава Україні, Jul 15 '14 at 10:36
Thanks for the suggestions guy but it seems as though the `replace` function is treating myStrings as parameters. Even when I quote the reference `'&myStrings'` or `"&myStrings"` there are still syntax exceptions being thrown. Did you have a chance to run the example by any chance? — travega, Jul 15 '14 at 22:31

score 1 · Answer 1 · answered Jul 17 '14 at 05:57

The below is the approch I would take, Note the use of tablen in the loop, this is because the DBMS_UTILITY.COMMA_TO_TABLE procedure adds a null value at the end of the table.

Hope you find this helpfull

declare

  myStrings varchar2(100) := '''abc'',''def''';
  myList dbms_utility.uncl_array;
  tablen number :=0;

begin

  DBMS_UTILITY.COMMA_TO_TABLE ( replace(myStrings, '''', ''),  tablen,  myList); 

  execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (' ||myStrings||')';

  for i in myList.FIRST..tablen loop
    dbms_output.put_line('VALUE: ' || myList(i));
  end loop;
end;
/

score 0 · Accepted Answer · answered Jul 18 '14 at 01:31

Thanks to @ShaunPeterson for inspiring the solution to this issue. While it solve the issue directly it provided the correct approach so all +1s should go to him.

Where his answer fell short was that he 'declared' myStrings rather then 'defining' it.

declare
  myStrings varchar2(100) := '''abc'',''def''';

NOT

define myStrings = "'abc','def'"

Herein lay the crux of the issue. In PL/SQL variables that are 'declared' for a procedure block like myStringsVar below are not substituted like 'defined' variables are. As per the OP the requirement was that 'myStrings' was first 'defined' then later transformed for use in a procedure block.

Therefore the resulting solution looks like this:

define myStrings = "''abc'',''def''"

declare

  myStringsVar varchar2(100) := '&myStrings';
  myList dbms_utility.uncl_array;
  tablen number :=0;

begin

  DBMS_UTILITY.COMMA_TO_TABLE ( replace(myStringsVar, '''', ''),  tablen,  myList); 

  execute immediate 'create table tmp_foo '
               || 'as select * from bar '
               || 'where bar_val in (' || myStringsVar||')';

  for i in myList.FIRST..tablen loop
    dbms_output.put_line('VALUE: ' || myList(i));
  end loop;
end;
/

DEFINE vs DECLARE - escaping quotes

2 Answers2