Re: help needed

hi,

corrections embedded. it is better/clear if you use an alias like sum_amount,
you need to change it in in the body i.e. iaf_accounting_rec.sum_amount

Hakan

Note: please avoid cross-posting.

motty shuster wrote:
>
> 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> _at_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) amount,

• ^^^^^^^^^^
  > 14 sum(discount) discount,
  
  • ^^^^^^^^^
    > 15 max(actdate) act_date,
    
    • ^^^^^^^^
      > 16 sum(elapsed_time) elapsed_time,
      
      • ^^^^^^^^^^^^
        > 17 sum(usage) 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