Re: create type with index

On Mar 15, 2:00 am, NOVA <nova1..._at_gmail.com> wrote:
> Dear all,
>
> I face a problem with this package
>
> what is the target?

Who can say? You're wasting effort trying to use this type when it's completely unnecessary.

> 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.
>

Why???

> 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

The type and the variable are unnecessary. I would stop trying to use them.

>     type xch_type is table of
>         number(15,7)
>         index by varchar2(4);
>     xch xch_type;
> procedure proc_t;
> end package_name;
> /

The package specification compiles successfully; your body does not.

> create or replace package body package_name as  -- line 25
>     procedure proc_t as
>     begin

xch is NOT a function, it's a type, and it isn't part of the XCH_T table, so why are you trying to select it?

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

This is not how to load such a table.

> 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.

Your body, if you REALLY want to use this type, needs to be written this way:

create or replace package body package_name as -- line 25

    procedure proc_t as

	cursor get_xch_data is
	select cucd1, cvra from xch_t;

    begin
	for xrec in get_xch_data loop
                                -- Populate the table
		xch(xrec.cucd1):= xrec.cvra;
                                -- Use the table to populate another
table (busy work, in my opinion)
		insert into aa
		values (xrec.cucd1, xrec.cvra, xch(xrec.cucd1));
	end loop;

    end proc_t;

end package_name;
/

The above code, in its entirety, can be replaced with:

create or replace package package_name as

        procedure proc_t;

end package_name;
/

create or replace package body package_name as -- line 25

    procedure proc_t as

	cursor get_xch_data is
	select cucd1, cvra from xch_t;

    begin
	for xrec in get_xch_data loop

		insert into aa
		values (xrec.cucd1, xrec.cvra, xrec.cvra);

	end loop;

    end proc_t;

end package_name;
/

Your array, as I see it, is useless here and it's only causing you problems.

David Fitzjarrell Received on Sun Mar 15 2009 - 14:04:39 CDT