create type with index

From: NOVA <nova1427_at_gmail.com>
Date: Sun, 15 Mar 2009 00:00:27 -0700 (PDT)
Message-ID: <043aa569-e7a5-4374-a5c3-fe265eac9061_at_40g2000yqe.googlegroups.com>

Dear all,

I face a problem with this package

what is the target?
I have table "xch_t" (exchange rate table) to convert the currency to 'SAR'. for example:
1 USD = 3.75 SAR
1 EUR = 4.84859 SAR so the table xch shown below:

CUCD1         CVRA
USD             3.75
JPY              0.0381255
EUR             4.84859

I have a lot of procedures use the exchange, so I want to make it an array by create type "xch_type" but the index will by CUCD1.

let us start togather. execute all lines below. note: this is an example to be easier to understand.

CREATE TABLE XCH_T -- line 1
(

  CUCD1 CHAR(4 BYTE) NOT NULL,
  CVRA NUMBER(15,7) NOT NULL
);

insert into XCH_T values ('USD ', 3.75);
insert into XCH_T values ('JPY ', 0.0381255);
insert into XCH_T values ('EUR ',4.84859);

CREATE TABLE aa
(

  CUCD1 CHAR(4 BYTE) NOT NULL,
  CVRA NUMBER(15,7) NOT NULL,
  conv_result NUMBER(15,7) NOT NULL
);

create or replace package package_name as

    type xch_type is table of

        number(15,7)
        index by varchar2(4);
    xch xch_type;

procedure proc_t;
end package_name;
/
create or replace package body package_name as -- line 25

    procedure proc_t as
    begin

        insert into aa
        select cucd1, CVRA, xch(cucd1) from xch_t;   -- line 29

    end proc_t;

begin

    select cvra into xch(cucd1) from xch_t; -- line 33 end package_name;
/

it show my error in line29 and 33.

Can you help me. Received on Sun Mar 15 2009 - 02:00:27 CDT