Spreadsheets, reporting tools, and data loaders often want CSV, not JSON. ORDS has a CSV source type that turns any query into comma-separated text. Testing it showed two things to know: the output has no header row, and it is not paged. This guide uses the CSV source type, and then a small PL/SQL handler that adds a header row and a download file name.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
The handlers join the network module from How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE.
Syntax
ords.define_handler(..., p_method => 'GET',
p_source_type => ords.source_type_csv_query,
p_source => 'select ...');The CSV Source Type
routes.csv returns the routes, optionally from one airport given in the query string.
Example:
begin
ords.define_template(p_module_name => 'network', p_pattern => 'routes.csv');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes.csv',
p_method => 'GET',
p_source_type => ords.source_type_csv_query,
p_source => 'select origin, destination, distance_km, block_minutes
from routes
where origin = nvl(upper(:origin), origin)
order by origin, destination');
commit;
end;
/Example:
curl -k "https://localhost:8443/ords/nimbus/network/routes.csv?origin=SIN"
Output:
SIN,DXB,5845,423 SIN,NRT,5358,391 SIN,SYD,6294,453
The response has the type text/csv and one line per row, but no header row with the column names. Without origin, all 50 routes come back in one response: the CSV type ignores the module's page size.
CSV with a Header Row
A PL/SQL handler writes the header line itself, then one line per row, and sets Content-Disposition so browsers save the response as routes.csv.
Example:
begin
ords.define_template(p_module_name => 'network', p_pattern => 'routes-export.csv');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes-export.csv',
p_method => 'GET',
p_source_type => ords.source_type_plsql,
p_source => q'[
begin
owa_util.mime_header('text/csv', false);
htp.p('Content-Disposition: attachment; filename="routes.csv"');
owa_util.http_header_close;
htp.p('origin,destination,distance_km,block_minutes'); -- the header row
for r in (select origin, destination, distance_km, block_minutes
from routes
where origin = nvl(upper(:origin), origin)
order by origin, destination) loop
htp.p(r.origin || ',' || r.destination || ',' || r.distance_km || ',' || r.block_minutes);
end loop;
end;]');
commit;
end;
/Example:
curl -k -D - "https://localhost:8443/ords/nimbus/network/routes-export.csv?origin=SIN"
Output (headers without the Date header, then the body):
HTTP/1.1 200 OK Content-Type: text/csv; charset=UTF-8 Content-Disposition: attachment; filename="routes.csv"; filename*=UTF-8''routes.csv ETag: "/vNeixLqRn+m1CZK2349GTPdyWKBYfgFpLCTwihDHPg6y05yzrNAL1LJHx9VTjAnuYn2rKchD46gPFyYZ3cuJQ==" Transfer-Encoding: chunked origin,destination,distance_km,block_minutes SIN,DXB,5845,423 SIN,NRT,5358,391 SIN,SYD,6294,453
Things to Know
- Text values that can contain commas or quotes must be quoted in CSV; wrap them in double quotes and double any quotes inside, or use only values that cannot contain them.
- Large exports go out in one response; filter them with query parameters, or schedule them as files instead.
- A dot in a template name, as in routes.csv, is an ordinary character, so the URL ends like a file name.
Related Guides
- How to Download Files from ORDS (media resources)
- How to Format Query Output in SQLcl (SET SQLFORMAT)
Conclusion
ORDS returns CSV from any query with the CSV source type, without a header row and without paging. When consumers need column names or a download file name, write the CSV in a PL/SQL handler with HTP and a Content-Disposition header.
