Pessoal, precisando gerar um arquivo, aquelas coisas que precisamos sempre recorrer ao passado, mas que é muito funcional. Deixo aqui o que fiz e alguns links para maiores informações, principalmente na LEITURA de arquivos.
Para usar a gravação de arquivos em disco via package e/ou procedure, precisamos primeiramente verificar o parametro UTL_FILE_DIR que mostra em quais diretórios temos permissão de gravar arquivos.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzaG93IHBhcmFtZXRlcnMgVVRMX0ZJTEVfRElSCiBOQU1FIFRZUEUgVkFMVUUKIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIHV0bF9maWxlX2RpciBzdHJpbmc=” hover_enabled=”0″]U1FMPiBzaG93IHBhcmFtZXRlcnMgVVRMX0ZJTEVfRElSCiBOQU1FIFRZUEUgVkFMVUUKIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAtLS0tLS0tLS0tLSAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIHV0bF9maWxlX2RpciBzdHJpbmc=[/et_pb_dmb_code_snippet]

Após verificar que em nenhum precisamos realizar a alteração, para isso podemos fazer da forma abaixo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzaHV0ZG93biBpbW1lZGlhdGU7CiBCYW5jbyBkZSBkYWRvcyBmZWNoYWRvLgogQmFuY28gZGUgZGFkb3MgZGVzbW9udGFkby4KIEluc3RhbmNpYSBPUkFDTEUgZGVzYXRpdmFkYS4KIAogU1FMPiBzdGFydHVwIG1vdW50OwogSW5zdGFuY2lhIE9SQUNMRSBpbmljaWFkYS4KIFRvdGFsIFN5c3RlbSBHbG9iYWwgQXJlYSAxNjEwNjEyNzM2IGJ5dGVzCiBGaXhlZCBTaXplIDIwODQyOTYgYnl0ZXMKIFZhcmlhYmxlIFNpemUgMTAyMzQxMDc0NCBieXRlcwogRGF0YWJhc2UgQnVmZmVycyA1NzA0MjUzNDQgYnl0ZXMKIFJlZG8gQnVmZmVycyAxNDY5MjM1MiBieXRlcwogQmFuY28gZGUgZGFkb3MgbW9udGFkby4KIAogU1FMPiBhbHRlciBzeXN0ZW0gc2V0IHV0bF9maWxlX2RpciA9ICcqJyBzY29wZT1zcGZpbGU7CiBTaXN0ZW1hIGFsdGVyYWRvLgogCiBTUUw+IHNodXRkb3duCiBPUkEtMDExMDk6IGJhbmNvIGRlIGRhZG9zIG7Do28gYWJlcnRvCiBCYW5jbyBkZSBkYWRvcyBkZXNtb250YWRvLgogSW5zdGFuY2lhIE9SQUNMRSBkZXNhdGl2YWRhLgogCiBTUUw+IHN0YXJ0dXAKIEluc3RhbmNpYSBPUkFDTEUgaW5pY2lhZGEuCiBUb3RhbCBTeXN0ZW0gR2xvYmFsIEFyZWEgMTYxMDYxMjczNiBieXRlcwogRml4ZWQgU2l6ZSAyMDg0Mjk2IGJ5dGVzCiBWYXJpYWJsZSBTaXplIDEwMjM0MTA3NDQgYnl0ZXMKIERhdGFiYXNlIEJ1ZmZlcnMgNTcwNDI1MzQ0IGJ5dGVzCiBSZWRvIEJ1ZmZlcnMgMTQ2OTIzNTIgYnl0ZXMKIEJhbmNvIGRlIGRhZG9zIG1vbnRhZG8uCiBCYW5jbyBkZSBkYWRvcyBhYmVydG8u” hover_enabled=”0″]U1FMPiBzaHV0ZG93biBpbW1lZGlhdGU7CiBCYW5jbyBkZSBkYWRvcyBmZWNoYWRvLgogQmFuY28gZGUgZGFkb3MgZGVzbW9udGFkby4KIEluc3RhbmNpYSBPUkFDTEUgZGVzYXRpdmFkYS4KIAogU1FMPiBzdGFydHVwIG1vdW50OwogSW5zdGFuY2lhIE9SQUNMRSBpbmljaWFkYS4KIFRvdGFsIFN5c3RlbSBHbG9iYWwgQXJlYSAxNjEwNjEyNzM2IGJ5dGVzCiBGaXhlZCBTaXplIDIwODQyOTYgYnl0ZXMKIFZhcmlhYmxlIFNpemUgMTAyMzQxMDc0NCBieXRlcwogRGF0YWJhc2UgQnVmZmVycyA1NzA0MjUzNDQgYnl0ZXMKIFJlZG8gQnVmZmVycyAxNDY5MjM1MiBieXRlcwogQmFuY28gZGUgZGFkb3MgbW9udGFkby4KIAogU1FMPiBhbHRlciBzeXN0ZW0gc2V0IHV0bF9maWxlX2RpciA9ICcqJyBzY29wZT1zcGZpbGU7CiBTaXN0ZW1hIGFsdGVyYWRvLgogCiBTUUw+IHNodXRkb3duCiBPUkEtMDExMDk6IGJhbmNvIGRlIGRhZG9zIG7Do28gYWJlcnRvCiBCYW5jbyBkZSBkYWRvcyBkZXNtb250YWRvLgogSW5zdGFuY2lhIE9SQUNMRSBkZXNhdGl2YWRhLgogCiBTUUw+IHN0YXJ0dXAKIEluc3RhbmNpYSBPUkFDTEUgaW5pY2lhZGEuCiBUb3RhbCBTeXN0ZW0gR2xvYmFsIEFyZWEgMTYxMDYxMjczNiBieXRlcwogRml4ZWQgU2l6ZSAyMDg0Mjk2IGJ5dGVzCiBWYXJpYWJsZSBTaXplIDEwMjM0MTA3NDQgYnl0ZXMKIERhdGFiYXNlIEJ1ZmZlcnMgNTcwNDI1MzQ0IGJ5dGVzCiBSZWRvIEJ1ZmZlcnMgMTQ2OTIzNTIgYnl0ZXMKIEJhbmNvIGRlIGRhZG9zIG1vbnRhZG8uCiBCYW5jbyBkZSBkYWRvcyBhYmVydG8u[/et_pb_dmb_code_snippet]

Também podemos fazer a alteração do parametro e reiniciar a instância:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBhbHRlciBzeXN0ZW0gc2V0IHV0bF9maWxlX2RpciA9ICcqJyBzY29wZT1zcGZpbGU7CiBTaXN0ZW1hIGFsdGVyYWRvLgoKU1FMPiBzaHV0ZG93biBpbW1lZGlhdGU7CiBCYW5jbyBkZSBkYWRvcyBmZWNoYWRvLgogQmFuY28gZGUgZGFkb3MgZGVzbW9udGFkby4KIEluc3RhbmNpYSBPUkFDTEUgZGVzYXRpdmFkYS4KCiBTUUw+IHN0YXJ0dXAKIEluc3RhbmNpYSBPUkFDTEUgaW5pY2lhZGEuCiBUb3RhbCBTeXN0ZW0gR2xvYmFsIEFyZWEgMTYxMDYxMjczNiBieXRlcwogRml4ZWQgU2l6ZSAyMDg0Mjk2IGJ5dGVzCiBWYXJpYWJsZSBTaXplIDEwMjM0MTA3NDQgYnl0ZXMKIERhdGFiYXNlIEJ1ZmZlcnMgNTcwNDI1MzQ0IGJ5dGVzCiBSZWRvIEJ1ZmZlcnMgMTQ2OTIzNTIgYnl0ZXMKIEJhbmNvIGRlIGRhZG9zIG1vbnRhZG8uCiBCYW5jbyBkZSBkYWRvcyBhYmVydG8u” hover_enabled=”0″]U1FMPiBhbHRlciBzeXN0ZW0gc2V0IHV0bF9maWxlX2RpciA9ICcqJyBzY29wZT1zcGZpbGU7CiBTaXN0ZW1hIGFsdGVyYWRvLgoKU1FMPiBzaHV0ZG93biBpbW1lZGlhdGU7CiBCYW5jbyBkZSBkYWRvcyBmZWNoYWRvLgogQmFuY28gZGUgZGFkb3MgZGVzbW9udGFkby4KIEluc3RhbmNpYSBPUkFDTEUgZGVzYXRpdmFkYS4KCiBTUUw+IHN0YXJ0dXAKIEluc3RhbmNpYSBPUkFDTEUgaW5pY2lhZGEuCiBUb3RhbCBTeXN0ZW0gR2xvYmFsIEFyZWEgMTYxMDYxMjczNiBieXRlcwogRml4ZWQgU2l6ZSAyMDg0Mjk2IGJ5dGVzCiBWYXJpYWJsZSBTaXplIDEwMjM0MTA3NDQgYnl0ZXMKIERhdGFiYXNlIEJ1ZmZlcnMgNTcwNDI1MzQ0IGJ5dGVzCiBSZWRvIEJ1ZmZlcnMgMTQ2OTIzNTIgYnl0ZXMKIEJhbmNvIGRlIGRhZG9zIG1vbnRhZG8uCiBCYW5jbyBkZSBkYWRvcyBhYmVydG8u[/et_pb_dmb_code_snippet]

Agora vamos ver como está o parâmetro UTL_FILE_DIR alterado:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBzaG93IHBhcmFtZXRlcnMgVVRMX0ZJTEVfRElSCgpOQU1FIFRZUEUgVkFMVUUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQp1dGxfZmlsZV9kaXIgc3RyaW5nICo=” hover_enabled=”0″]U1FMPiBzaG93IHBhcmFtZXRlcnMgVVRMX0ZJTEVfRElSCgpOQU1FIFRZUEUgVkFMVUUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tIC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQp1dGxfZmlsZV9kaXIgc3RyaW5nICo=[/et_pb_dmb_code_snippet]

O * mostra que podemos gravar em qualquer lugar, aonde o usuário Oracle tenha permissão de SO.

Agora vamos dar a permissão de execute para o PACOTE UTL_FILE:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBncmFudCBleGVjdXRlIG9uIFVUTF9GSUxFIHRvIFVTVUFSSU87CiBDb25jZXNzw6NvIGJlbS1zdWNlZGlkYS4=” hover_enabled=”0″]U1FMPiBncmFudCBleGVjdXRlIG9uIFVUTF9GSUxFIHRvIFVTVUFSSU87CiBDb25jZXNzw6NvIGJlbS1zdWNlZGlkYS4=[/et_pb_dmb_code_snippet]

Agora vejam o exemplo da procedure que vai gerar a saida do meu SQL no SO:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”UHJvY2VkdXJlIFNwX0dlcmFfRW1haWxfVHh0IEFzCkxpc3RhX0VtYWlsIFV0bF9GaWxlLkZpbGVfVHlwZTsKQmVnaW4KTGlzdGFfRW1haWwgOj0gVXRsX0ZpbGUuRm9wZW4oJy90bXAnLCAnZW1haWwudHh0JywgJ1cnKTsKIC0tIEVzc2UgRk9SIGFxdWkgw6kgZG8gU1FMCiBGb3IgUGxpbmhhIEluIChTZWxlY3QgRW1haWwKIEZyb20gVXN1YXJpb3MKIFdoZXJlIEVtYWlsIElzIE5vdCBOdWxsKQogTG9vcAogVXRsX0ZpbGUuUHV0X0xpbmUoTGlzdGFfRW1haWwsIFBsaW5oYS5FbWFpbCk7CiBFbmQgTG9vcDsKVXRsX0ZpbGUuRmNsb3NlKExpc3RhX0VtYWlsKTsKRW5kIFNwX0dlcmFfRW1haWxfVHh0Ow==” hover_enabled=”0″]UHJvY2VkdXJlIFNwX0dlcmFfRW1haWxfVHh0IEFzCkxpc3RhX0VtYWlsIFV0bF9GaWxlLkZpbGVfVHlwZTsKQmVnaW4KTGlzdGFfRW1haWwgOj0gVXRsX0ZpbGUuRm9wZW4oJy90bXAnLCAnZW1haWwudHh0JywgJ1cnKTsKIC0tIEVzc2UgRk9SIGFxdWkgw6kgZG8gU1FMCiBGb3IgUGxpbmhhIEluIChTZWxlY3QgRW1haWwKIEZyb20gVXN1YXJpb3MKIFdoZXJlIEVtYWlsIElzIE5vdCBOdWxsKQogTG9vcAogVXRsX0ZpbGUuUHV0X0xpbmUoTGlzdGFfRW1haWwsIFBsaW5oYS5FbWFpbCk7CiBFbmQgTG9vcDsKVXRsX0ZpbGUuRmNsb3NlKExpc3RhX0VtYWlsKTsKRW5kIFNwX0dlcmFfRW1haWxfVHh0Ow==[/et_pb_dmb_code_snippet]

Também podemos trabalhar a leitura de arquivos, nesses links abaixo você encontra mais exemplos sobre arquivos com UTL_FILE.

Mais info:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm
http://eduardolegatti.blogspot.com.br/2009/12/um-pouco-do-pacote-utlfile-dbmslob-e.html
http://www.oraclehome.com.br/2012/02/13/como-ler-e-gravar-arquivos-textos-pelo-oracle-10g-ou-superior/