Query who generates insert statements : invalid number

Question

I'm trying to execute a query who generates inserts statements, like that :

select 
  'insert into MYTABLE (
    ID, 
    COLUMN_A, 
    COLUMN_B)
  values (' + 
    ID + ',' +
    COLUMN_A + ',' +
    COLUMN_B  
as INSERTGENERATOR from MYTABLE;

But I have this error :

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

Why ? I tried with || instead of +, and with to_char, but it but it does not change.

maybe u miss the end ')' and ';' ? or just they are not shown on the example ? — Melon, Sep 17 '14 at 08:30
this could help ? : http://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number — Melon, Sep 17 '14 at 08:32
Just use the simple string literal technique, q'[]', see my demonstration in the answer. — Lalit Kumar B, Sep 17 '14 at 08:43

score 2 · Accepted Answer · answered Sep 17 '14 at 08:31

2

Don't put end of lines within quotes. And || is used to concatenate.

select 
  'insert into MYTABLE (ID, COLUMN_A, COLUMN_B) values (' ||
    ID || ',' ||
    COLUMN_A || ',' ||
    COLUMN_B || ');'
as INSERTGENERATOR from MYTABLE;

answered Sep 17 '14 at 08:31

wvdz

16,251
4
53
90

score 2 · Answer 2 · answered Sep 17 '14 at 08:33

2

This works:

SELECT
  'INSERT INTO MYTABLE 
  (ID, COLUMN_A, COLUMN_B)
   VALUES (' || 
    ID || ',' ||
    COLUMN_A || ','||
    COLUMN_B || ');'
AS INSERTGENERATOR 
FROM MYTABLE;

Sample SQL Fiddle

answered Sep 17 '14 at 08:33

jpw

44,361
6
66
86

score 1 · Answer 3 · answered Sep 17 '14 at 08:42

1

Get rid of all the pain while concatenating string literals using pipes "||" etc. Just use the latest string literal feature q'[]'.

SQL> SELECT q'[insert into MYTABLE (
  2  ID,
  3  COLUMN_A,
  4  COLUMN_B)
  5  values ('ID', 'COLUMN_A','COLUMN_B')]'
  6  AS INSERTGENERATOR
  7  FROM DUAL
  8  /

INSERTGENERATOR
-------------------------------------------
insert into MYTABLE (
ID,
COLUMN_A,
COLUMN_B)
values ('ID', 'COLUMN_A','COLUMN_B')

SQL>

Neat, isn't it?

answered Sep 17 '14 at 08:42

Lalit Kumar B

47,486
13
97
124

Surely `ID`, `COLUMN_A` and `COLUMN_B` should be the values from `MYTABLE`, not those literals? In which case they'd have to be concatenated? (Though I like the quoted syntax in general; just not sure it quite works here...) – Alex Poole Sep 17 '14 at 08:54
I agree. Just realized those are column names and not literals. In that case, column names has to be concatenated. Perhaps, OP would find the new string literal technique quite handy at times when he would like to build complex dynamic strings :-) By the way, thanks for pointing it out. – Lalit Kumar B Sep 17 '14 at 09:04

Query who generates insert statements : invalid number

3 Answers3