Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
Working with LONGs |
Too often, in the
Oracle Technology Database Forums, I see questions regarding how to
write data from one or more tables into a comma delimited file for
use in Excel or some other product.
I personally think moving data out of the database a bad practice.
And that moving anything into Excel is essentially a statement that
"audit trails and the quality of our data are unimportant." But
given the large number of requests I have put together these demos
showing how to capture string, numeric, and date column values.
So step one lets create a table and insert some data we can export. |
CREATE TABLE test (
fname VARCHAR2(20),
lname VARCHAR2(20),
num_col NUMBER(5,2),
dat_col DATE);
INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Dan',
'Morgan', 1, SYSDATE-1);
INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Tom', 'Kyte',
100.42, SYSDATE);
INSERT INTO test (fname, lname, num_col, dat_col) VALUES ('Richard',
'Foote', 0.64, SYSDATE+1);
COMMIT;
SELECT * FROM test; |
| The first thing we will practice is
extracting strings. We will produce the output first by
concatenating the string columns: Then we will do so with
delimiters. |
SELECT fname, lname
FROM test;
SELECT fname || lname
FROM test;
-- space delimited
SELECT fname || ' ' || lname
FROM test;
-- comma delimited
SELECT fname || ',' || lname
FROM test;
-- vertical bar delimited
SELECT fname || '|' || lname
FROM test; |
|
Now lets add in the numeric column. This
works but forces Oracle to perform an implicit conversion. We will
modify it to cast num_col as a string too. |
SELECT fname
|| ',' || lname || ',' || num_col
FROM test;
SELECT fname || ',' || lname || ',' || TO_CHAR(num_col)
FROM test;
SELECT fname || ',' || lname || ',' || TO_CHAR(num_col, '999.99')
FROM test; |
|
Now lets add the date column.
Remembering again that it is best not to depend on explicit
conversion. |
SELECT fname || ',' || lname || ',' || num_col
|| ',' || dat_col
FROM test;
SELECT fname || ',' || lname || ',' || num_col || ',' ||
TO_CHAR(dat_col)
FROM test;
SELECT fname || ',' || lname || ',' || num_col || ',' ||
TO_CHAR(dat_col, 'MM/DD/YYYY')
FROM test;
SELECT fname || ',' || lname || ',' || num_col || ',' ||
TO_CHAR(dat_col, 'MM/DD/YYYY HH24:MI:SS')
FROM test; |
|
We can add quotes to the strings too to
make it easier to understand the data types of the values |
SELECT '''' || fname || '''' || ',' || '''' ||
lname || '''' || ',' || num_col
FROM test; |
|
And finally output the entire string as
an Oracle insert statement that could be run in a different schema. |
SELECT 'INSERT INTO test (fname, lname, num_col,
dat_col) VALUES (' || '''' ||
fname || '''' || ',' || '''' || lname || '''' || ',' || num_col ||
',' || 'SYSDATE);'
FROM test;
-- copy one of the statements and run it.
SELECT * FROM test; |
| |
|
