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