Re: create type with index

From: NOVA <nova1427_at_gmail.com>
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

Original text of this message