RE: Output to Excel

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


>>> cspence_at_FuelSpot.com 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
com.banilejas_at_codetel.net.do 
Dominican Republic
809-565-3121



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Ramon Estevez
  INET: com.banilejas_at_codetel.net.do 

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: ListGuru_at_fatcity.com (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: cspence_at_FuelSpot.com 

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: ListGuru_at_fatcity.com (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: alramos_at_capufe.gob.mx

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: ListGuru_at_fatcity.com (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).