RE: Output to Excel

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 04 Oct 2001 19:34:42 -0700
Message-ID: <F001.003A2D43.20011004194521@fatcity.com>

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
--------------------------------------------------------------------