Instead of creating a stored procedure & using util package in Oracle to write each record from a table to a flat file in desired column delimited way (i.e. '#|'), can i use expdp to perform the same thing ??
This will help in extracting data in a much faster way.
Please suggest.
As I see the docs: Oracle® Database Utilities 11g Release 2, expdp is unable to do formatting.
But you can use external tables for database unloads. First you should create a directory:
CREATE DIRECTORY mydir AS 'C:\MyDir'
Then grant access to it:
GRANT READ, WRITE ON DIRECTORY mydir TO myuser;
Finally in one step create an external table and export the result of a query into it:
create table mytable (
col1 varchar2(100), col2 varchar2(100)
) organization external (
type oracle_loader
default directory mydir
access parameters (
records delimited by newline
fields terminated by "#|"
)
location('myfile.txt')
) as select col1, col2 from anothertable;
Here is a good link to read more details about external tables: Oracle® Database Administrator's Guide 11g Release 1
expdpis not intended to write flat files.