Re: create type with index
From: ddf <oratune_at_msn.com>
Date: Mon, 16 Mar 2009 05:42:30 -0700 (PDT)
Message-ID: <e4a4ad10-4b6b-4c1e-a327-7f5993f93639_at_v19g2000yqn.googlegroups.com>
On Mar 16, 2:36 am, NOVA <nova1..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 16 Mar 2009 05:42:30 -0700 (PDT)
Message-ID: <e4a4ad10-4b6b-4c1e-a327-7f5993f93639_at_v19g2000yqn.googlegroups.com>
On Mar 16, 2:36 am, NOVA <nova1..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
You cannot get 'line 6' to work as you have it written. Please understand this. You would need to write a function that uses that array and passes it a value from a select statement to get something like line 6 to work. I'll leave it to you to write the function.
David Fitzjarrell Received on Mon Mar 16 2009 - 07:42:30 CDT