Re: create type with index
Date: Mon, 16 Mar 2009 00:36:32 -0700 (PDT)
Message-ID: <619a4071-cae5-4e44-9ed7-f3567ff56eb7_at_q9g2000yqc.googlegroups.com>
On Mar 15, 10:04 pm, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
>
> 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- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
I understand what do you mean.
I try to use array as a function.
look at last change after your advice
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
cursor get_xch_data is
select cucd1, cvra from xch_t;
procedure proc_t as
begin
--insert into aa select cucd1, CVRA, xch(cucd1) from xch_t; -- line 6
insert into aa select cucd1, CVRA, xch('USD') from xch_t; -- line 7
end proc_t;
begin
for xrec in get_xch_data loop
xch(xrec.cucd1):= xrec.cvra;
end loop;
end package_name;
/
I still want to use an array. if you look at line 6 there is error
here "xch(cucd1)"
and in line 7 is successfull but the different is line 6 is dynamic
and line 7 is static.
How can I make line t successfully.
thx alot. Received on Mon Mar 16 2009 - 02:36:32 CDT