Sep 22, 2023
Flat Files- Files
Flat files have been around since the dawn of electronic data processing. We see them literally every day. The text alert log described previously is a flat file. I found the following definition for “flat file” on the Internet and feel it pretty much wraps things up:
An electronic record that is stripped of all specific application (program) formats. This allows the data elements to be migrated into other applica-tions for manipulation. This mode of stripping electronic data prevents data loss due to hardware and proprietary software obsolescence.
A flat file is simply a file whereby each “line” is a “record,” and each line has some text delimited, typically by a comma or pipe (vertical bar). Flat files are easily read by Oracle using either the legacy data loading tool SQLLDR or external tables. In fact, I will cover this in detail in Chapter 15.
Occasionally, a user will request data in a flat file format such as one of the following:
•\ Character (or comma) separated values (CSV): These are easily imported into tools such as spreadsheets.
•\ HyperText Markup Language (HTML): Used to display pages displayed in web browsers.
•\ JavaScript Object Notation (JSON): Standard text-based format for representing structured data and used for transmitting data in web browsers.
I’ll briefly demonstrate generating each of the file types in the following sections.
Generating a CSV File
You can easily generate CSV flat files from SQLPlus. You can either use the -m ‘csv on’ switch from the operating system prompt or use SET MARKUP CSV after starting a SQLPlus session, for example:
$ sqlplus scott/tiger@PDB1
SQL> set markup csv on delimiter , quote off SQL> SELECT * FROM dept;DEPTNO,DNAME,LOC
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
Here is an example of using the -m ‘csv on’ switch at the command line:
$ sqlplus -markup ‘csv on quote off’ scott/tiger@PDB1
SQL> select * from emp where rownum < 3;
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 7369,SMITH,CLERK,7902,17-DEC-80,800,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
Tip When you use the -markup ‘csv on’ switch, SQL*Plus sets variables such as ROWPREFETCH, STATEMENTCACHE, and PAGESIZE to optimize I/O performance.
Generating HTML
Similar to generating a CSV file, you can generate an HTML file from SQL*Plus by setting
SET MARKUP HTML ON before running your query:
SQL> set markup html on
SQL> select * from dept;
| DEPTNO | DNAME… You can also specify the -markup ‘html on’ switch at the command line to generate HTML output: $ sqlplus -markup ‘html on’ scott/tiger@PDB1 In this manner, you can easily produce HTML output based on the contents of a table. |
|---|