CODEWITHSUNDEEP
oracle-databaseoracle11g
M1X
M1X

Reputation: 5354

Oracle multiple values in a single row

I have this query which inserts 4 new rows in my table. I don't want this. I want to insert a single row and separate values with ,. How can I do this?

INSERT INTO DOCUMENTATION (NAME, VALUE, QUERY, INSERTDATE) 
     SELECT 'Datafile paths', name, 'select name from V$DATAFILE', SYSDATE 
     from V$DATAFILE;

Upvotes: 1

Views: 59

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220787

In Oracle, aggregating several rows into a single comma-separated value is done with LISTAGG:

INSERT INTO DOCUMENTATION (NAME, VALUE, QUERY, INSERTDATE) 
SELECT 'Datafile paths',
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name), 
       'select name from V$DATAFILE', 
       SYSDATE 
FROM V$DATAFILE;

Beware of the 4000 character limit, as always...

Upvotes: 1

SMA
SMA

Reputation: 37023

Try to concatenate all the values using "||" like:

INSERT INTO DOCUMENTATION (NAME, VALUE, QUERY, INSERTDATE) 
 SELECT 'Datafile paths' || name || 'select name from V$DATAFILE' || SYSDATE, 'Datafile paths' || name || 'select name from V$DATAFILE' || SYSDATE, 'Datafile paths' || name || 'select name from V$DATAFILE' || SYSDATE, 'Datafile paths' || name || 'select name from V$DATAFILE' || SYSDATE
 from V$DATAFILE;

If you need to select just one row, then you would need to specify the where clause like:

INSERT INTO DOCUMENTATION (NAME, VALUE, QUERY, INSERTDATE) 
 SELECT 'Datafile paths', name, 'select name from V$DATAFILE', SYSDATE 
 FROM V$DATAFILE
 WHERE myfield = 'foobar';

Upvotes: 1

Related Questions