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> @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
Received on Tue Jul 18 2000 - 00:00:00 CDT