help needed

From: motty shuster <mottys_at_amdocs.com>
Date: 2000/07/18
Message-ID: <963931489.791883@soint7>#1/1

I open a curson from a certain table, and create a record type. After that I'm trying to fill the record fields with the data from the cursor and I get Error:
can anyone help me?

The program is with the error is:

SQL> @charge_1
SQL> set serveroutput on size 100000
SQL> set term on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE PROCEDURE charge_oracle_extract
  2  (v_from_cus IN NUMBER,
  3   v_to_cus   IN NUMBER,

  4 v_file_name IN VARCHAR2,
  5 v_status OUT CHAR)
  6 IS

  7      CURSOR iaf_accounting_crs IS
  8           SELECT custnum,
  9                  userid,
 10                  package,
 11                  service,
 12                  activity,
 13                  sum(amount),
 14                  sum(discount),
 15                  max(actdate),
 16                  sum(elapsed_time),
 17                  sum(usage)
 18           FROM iaf_accounting
 19           WHERE billed='Y'
 20  --               extract_seq_no is null
 21           GROUP BY custnum, userid, package, service, activity;
 22
 22          TYPE account_rec_type IS RECORD
 23        (record_type          CHAR(1) DEFAULT '2',
 24  --     ban                  iaf_attributes.ban%TYPE,
 25  --     product_id           iaf_attributes.product_id%TYPE,
 26         account_no           iaf_accounts.custnum%TYPE,
 27         parent_custnum       iaf_accounts.parent_custnum%TYPE,
 28         billing_parent       iaf_accounts.billing_parent%TYPE,
 29         activity             iaf_accounting.activity%TYPE,
 30         service              iaf_accounting.service%TYPE,
 31         package              iaf_accounting.package%TYPE,
 32         package_desc         iaf_packages.description%TYPE,
 33         amount               iaf_accounting.amount%TYPE,
 34         discount             iaf_accounting.discount%TYPE,
 35         bill_cycle           CHAR(2),
 36         activity_type        iaf_activities.act_type%TYPE,
 37         ftr_revenue_code     CHAR(1),
 38         activity_date        CHAR(8),
 39         cvrg_prd_strt_date   CHAR(8),
 40         cvrg_prd_end_date    CHAR(8),
 41         time_duration        iaf_accounting.elapsed_time%TYPE,
 42         units_to_rate        iaf_accounting.usage%TYPE,
 43         user_id              CHAR(11),
 44         domain               CHAR(11),
 45         pck_eff_date         CHAR(8),
 46         pck_end_date         CHAR(8),
 47         com_res              iaf_accounts.com_res%TYPE,
 48         generation_date      CHAR(8));
 49
 49      account_rec account_rec_type;

 50
 50 BEGIN
 51 dbms_output.put_line('Hello');
 52 FOR iaf_accounting_rec IN iaf_accounting_crs LOOP

 53       BEGIN
 54          account_rec.account_no    := iaf_accounting_rec.custnum;
 55          account_rec.activity      := iaf_accounting_rec.activity;
 56          account_rec.service       := iaf_accounting_rec.service;
 57          account_rec.package       := iaf_accounting_rec.package;
 58          account_rec.amount        := iaf_accounting_rec.amount;
 59          account_rec.discount      := iaf_accounting_rec.discount;
 60          account_rec.activity_date := iaf_accounting_rec.act_date;
 61          account_rec.time_duration := iaf_accounting_rec.elapsed_time;
 62          account_rec.units_to_rate := iaf_accounting_rec.usage;
 63          account_rec.generation_date := SYSDATE;
 64
 64          dbms_output.put_line('Account no' ||  account_rec.account_no );
 65
 65        END;
 66     END LOOP;

 67 END charge_oracle_extract;
 68
 68 /
Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS;
Errors for PROCEDURE CHARGE_ORACLE_EXTRACT:

LINE/COL ERROR

-------- -----------------------------------------------------------------
52/3     PLS-00402: alias required in SELECT list of cursor to avoid
         duplicate column names
52/3     PL/SQL: Statement ignored

Received on Tue Jul 18 2000 - 00:00:00 CDT