Re: SQL*Loader for loading, but how to unload data?

On Mon, 10 Sep 2001 21:05:14 -0700, Marky Mark <DontSpamMe_at_Nospam.org> wrote:

>I'm looking for a way to unload data from a table, similar to what I
>can do in Informix with an "Unload to ..." statement.
>
>Is there some way to unload table data to a file from Oracle? I have
>several versions: 7.3.2 8.0.5 and 8.1.6.
>
>Thanks,
>Mark
>

spool $HOME/oradata.txt
  select geo_area_cd || ',' || appointment_dte || ',' || job_group   from ar_
  where rownum <= 10;
spool off

The above works.

Also, you can RYOC via utl_file

Something like.
DECLARE

	  fileHandler UTL_FILE.FILE_TYPE;
	BEGIN
	  fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
	  UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a
file!!!\n');
	  UTL_FILE.FCLOSE(fileHandler);
	EXCEPTION
	  WHEN utl_file.invalid_path THEN
	     raise_application_error(-20000, 'ERROR: Invalid path for
file or path not in INIT.ORA.');
	END;
	/

Also, if you have TOAD or other vendor tools you can do it via a GUI which I sure invokes UTL_FILE under the covers.

In think in 9i you can have external tables which is not a real unload to a flat file but is close. Received on Tue Sep 11 2001 - 06:29:57 CDT