Migrating Oracle Data from Windows to Linux using DataPump

It was a dark, stormy night in Redwood Shores. Only a single light burned at Oracle Towers. The Marketing Department was still locked in conference.
Countless flip-chart sheets littered the room, the result of thought-showers, story-boarding and numerous break-out imagineering sessions.
The challenge with which they had grappled all this time ? How to re-brand the long-time staple, but not particularly exciting export/import utility.
Suddenly, one nameless alpha-male ( and it must surely have been a man) rose to his feet, propelled by a lightning strike of inspiration. In a great, booming voice, dripping with testosterone, pelvis-thrusting beneath his ample girth for added emphasis, he announced to the room, “I know, let’s call it Data Pump !”

The name may have changed, the odd bell-and-whistle added, but the purpose remains unchanged. Export/Import ( Data Pump, if you must), is a utility for transferring objects and data from one Oracle instance to another, irrespective of the Operating System on which either the source or target database is running. Continue reading

UTL_FILE in PL/SQL – I/O, I/O, it’s off to work we go

Back in the mists of time, when Broadband was a way of describing a group of fat blokes with guitars, PL/SQL blinked it’s way into the world. It’s purpose was ( and largely remains) to provide the facility to apply 3GL program structures to SQL from within the database ( hence – Procedural Language / SQL).
As an integral part of the Oracle RDBMS, most PL/SQL I/O activities are on database tables. The ability to read and write OS files didn’t arrive until much later.
Meanwhile, back in the present, things are somewhat better on the File Handling front. So, if you just have to generate that flat-file and would rather not muck about with a pre-compiler (or a Java Stored Procedure), PL/SQL will do the job. Continue reading