help needed
Date: 2000/07/18
Message-ID: <963931489.791883_at_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:
The program is with the error is:
can anyone help me?
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),
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 ignoredReceived on Tue Jul 18 2000 - 00:00:00 CEST