Oracle equivalent to SQLite's quote() function

Question

Hello!

Sometimes I want to generate INSERT statements from the contents of a database table.

With SQLite, I can do:

SELECT 'INSERT INTO foo (col1, col2) VALUES (' || quote(col1) || ',' || quote(col2) || ');'
  FROM bar;

With Oracle, I have to do :

SELECT 'INSERT INTO foo (col1, col2) VALUES (''' || replace(col1, '''', '''''') || ''',''' || replace(col2, '''', '''''') || ''');'
  FROM bar;

And furthermore, it won't work with NULL values.

Is there a better way?

Answer 1

you should use bind parameters:

http://use-the-index-luke.com/where-clause/bind-parameters

Answer 2

I don't see any problem with nulls in your code. Where there are nulls the value inserted will be '', which is the same as null in Oracle (I know, I know...) e.g.

INSERT INTO foo (col1, col2) VALUES ('XXX','');

and that works.

You could create the QUOTE function in Oracle like this:

create function quote (p_text varchar2) return varchar2 is
begin
   return '''' || replace (p_text, '''', '''''') || '''';
end;

and then your SQLLite SQL will work in Oracle too.

Beware of dates and the default format mask: you will lose any time information in the source table unless you set the default format mask to include it e.g.

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

(This needs to be set to the same value when the script is run, too.)

I note Marcus's point about using bind variables, but this seems to me to be one of those one-time scripts that are run and then thrown away, not a bit of production code that will be run over and over, so I don't have a problem with literals. Tools like Toad and SQL Developer have facilities to generate inserts with literals just like this in fact.

Answer 3

Also you can use Q-quote operator introduced in Oracle 10g:

SELECT 'INSERT INTO foo (col1, col2) VALUES (q''#' || col1 || '#'', q''#' || col2 || '#'');'
  FROM bar;

result:

INSERT INTO foo (col1, col2) VALUES (q'#col1's value#', q'#col2's value#');

Answer 4

If you're on 11g, I'd use DBMS_ASSERT.ENQUOTE_LITERAL instead of rolling your own.