Select list as column in Oracle SQL

I would like to append a few rows to an already existing table (15 rows to be precise). What is the quickest way to do this?

This works, but it seems redundant.

select person_id from people_table
union all
select '0010' as person_id from dual
union all
select '0019' as person_id from dual
union all
select '0085' as person_id from dual

I was wondering if there’s a solution along the lines of:

select person_id from people_table
union all
select ('0010','0019','0085') as person_id from dual

Please note that I want to preserve the leading zeros for each element in my list. This post is almost what I’m looking for, but it converts each element to integers and drops the leading zeros.

Answer

You can use a system-provided collection type; you select from it using the TABLE operator (even that is no longer necessary since Oracle 12, as I demonstrate below). Note that the column name is COLUMN_NAME – that is the name Oracle chose when they created the system-provided type.

Let’s create a small table for testing:

create table people_table (person_id varchar2(10), person_name varchar2(10));

insert into people_table (person_id, person_name) values ('2003', 'Maria');
insert into people_table (person_id, person_name) values ('2005', 'Peter');

Then, here is how you can do what you wanted:

select person_id from people_table
union all
select column_value from sys.odcivarchar2list('1000', '1001', '1002')
;

PERSON_ID
---------
2005
2003
1000
1001
1002

Do a Google search for SYS.ODCIVARCHAR2LIST (and similar SYS.ODCINUMBERLIST) if you are not familiar with it; it’s quite useful.