Ramon,
I wrote this little but useful procedure.
You need to change your table fields only.
HTH
---
procedure test as
fp utl_file.file_type;
cursor c_tabla is
select * from my_table;
v_output_buffer varchar2(200);
begin
fp := utl_file.fopen( '/pdsi/dsk1','t.out','w' );
for x in c_tabla loop
v_output_buffer := x.field1 || '|' || x.field2 || '|' || ....etc ;
UTL_FILE.PUT_LINE(FP, V_OUTPUT_BUFFER);
end loop;
dbms_output.put_line(' Ya acabe!! ');
utl_file.fclose_all();
exception
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Ruta erronea');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Modo invalido');
WHEN OTHERS THEN
dbms_output.put_line ( ' Otras excepciones y errores: ' || sqlcode || ' - ' || sqlerrm
);
utl_file.fclose_all();
end;
-----
Mario Alberto Ramos
SAlu2
>>> [EMAIL PROTECTED] 04/10/01 22:45 >>>
FYI: If you use PL/SQL Developer you can save results sets right as CSV,
really sweet. I use PL/SQL Developer for 90% of what I do.
And it is $50-$150 / license depending on how many you get, i would compare
it to toad ($750) or navigator ($5,000) any day of the week.
(Sorry quest people).
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 10/4/01 6:10 PM
Hi Jared,
Excuse me for contact you directly not throug the list.
The past week you post an answer to someone trying to write to excel,
I took the example to generate a file comma separated, but getting an
error.
CREATE OR REPLACE PACKAGE BODY PROCESAR_AGENCIAS AS
PROCEDURE GENERAR_FACTURAS
( PGRUPO IN NUMBER, PCOMPANIA IN NUMBER,
PFECHA_INICIAL IN DATE, PFECHA_FINAL IN DATE,
PAGENCIA IN NUMBER ) AS
CURSOR C_FACTURAS IS
SELECT F.GRUPO||','||
F.COMPANIA||','||
F.TIPO_FACTURA||','||
F.AGENCIA||','||
F.FACTURA||','||
F.CLIENTE||','||
F.VENDEDOR||','||
F.DOCUMENTO_COBRO||','||
F.FECHA||','||
F.FECHA_PAGO||','||
F.FECHA_VENCIMIENTO||','||
F.ESTATUS_COMISION||','||
F.COMISION_VENDEDOR||','||
F.MONTO||','||
F.MONTO_PAGADO||','||
F.IMPRESA||','
FROM FACTURAS F
WHERE
F.GRUPO = PGRUPO AND
F.COMPANIA = PCOMPANIA AND
F.AGENCIA = PAGENCIA AND
F.FECHA BETWEEN PFECHA_INICIAL AND PFECHA_FINAL;
CURSOR C_ITEM_FACTURAS IS
SELECT I.GRUPO||','||
I.COMPANIA||','||
I.AGENCIA||','||
I.TIPO_FACTURA||','||
I.FACTURA||','||
I.LOCALIDAD||','||
I.ARTICULO||','||
I.SECUENCIA||','||
I.COSTO||','||
I.PRECIO_VENTA||','||
I.CANTIDAD||','||
I.ITBIS||','||
I.DESCTO||','
FROM FACTURAS F, ITEM_FACTURAS I
WHERE
F.GRUPO = PGRUPO AND
F.COMPANIA = PCOMPANIA AND
F.AGENCIA = PAGENCIA AND
F.FECHA BETWEEN PFECHA_INICIAL AND
PFECHA_FINAL AND
I.GRUPO = F.GRUPO AND
I.COMPANIA = F.COMPANIA AND
I.TIPO_FACTURA = F.TIPO_FACTURA AND
I.FACTURA = F.FACTURA;
V_ARCHIVO UTL_FILE.FILE_TYPE;
REGISTRO FACTURAS%ROWTYPE; ************* I declare it here
OUT_REC TYPE REGISTRO; *************
BEGIN
-- Loop para el archivo de Facturas
V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','FACTURAS.TXT', 'W');
FOR FT IN C_FACTURAS
LOOP
UTL_FILE.PUT_LINE(V_ARCHIVO, FT.OUT_REC);
END LOOP;
UTL_FILE.FCLOSE(V_ARCHIVO);
-- Loop para el archivo de Item Facturas
V_ARCHIVO := UTL_FILE.FOPEN('D:\EMBARQUES','ITEM_FACTURAS.TXT', 'W');
FOR IFT IN C_ITEM_FACTURAS
LOOP
UTL_FILE.PUT_LINE(V_ARCHIVO, IFT.OUT_REC);
END LOOP;
UTL_FILE.FCLOSE(V_ARCHIVO);
END GENERAR_FACTURAS;
END PROCESAR_AGENCIAS;
/
PL/SQL: Statement ignored
PLS-00302: component 'OUT_REC' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'OUT_REC' must be declared
How should I Declare it.
I did
REGISTRO FACTURAS%ROWTYPE;
OUT_REC TYPE REGISTRO;
Is there something missing ?
Any help !!
Thanks in Advance,
Ramon E. Estevez
[EMAIL PROTECTED]
Dominican Republic
809-565-3121
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ramon Estevez
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mario Alberto Ramos Arellano
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
