Dear all,

I face a problem with this package

what is the target?
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.


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
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 -- line 25
procedure proc_t as
begin
insert into aa
select cucd1, CVRA, xch(cucd1) from xch_t; -- line 29
end proc_t;

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.

Comments embedded.

On Mar 15, 2:00*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
Who can say? You're wasting effort trying to use this type when it's
completely unnecessary.

Why???

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

The package specification compiles successfully; your body does not.

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?

This is not how to load such a table.

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

On Mar 15, 10:04*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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.

On Mar 16, 2:36*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
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

On Mar 16, 3:42*pm, ddf <orat... (AT) msn (DOT) com> wrote:
you right,

I add function as below

function xch_f(cucd1 char, amont number) return number as
begin
return xch(cucd1);
end xch_f;

and change lin 6 to
insert into aa select cucd1, CVRA, xch_f(cucd1) from xch_t;

It work successfully.


by this way I increase the performance to load into a table.

let me explain:

I have big query inserted to table and there is function read from
xch_t (this table is very large because it is history data, that means
there is column for date and I always take sysdate) by pass currency
code to return number.
but there are a lot of exhange rate so it need to use function many of
times. on other hand one query inside the function executed many
times.

so I find solution by use with statment that is query like function
query except no filter on currency code. and inside big query I make
select statment from "WITH" by add filter for current code.

what is the different:
- By function: it take 28:10 min becuase xch_t executed many
times, same query
- By With statment: 4:30 min becuase xch_t executed one time,
and filterd inside the big query
- By array (after your help): 7:20 min becuase xch_t executed one
time, and pass the currency code to function then to array.


What do you think?

I still try to find more performance at least like (by with statment).

Using with statment has one disadvantage that it make the big query
more bigger and difficult to read when I want to make change.



I waiting for your advice.

thx

On Mar 16, 10:06*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
If it were me doing this, I'd take the subquery factoring version
(using WITH) of the three choices offered and be done with it. You've
taken a process which consumed 28+ minutes of time and reduced it to
4+ minutes with subquery factoring, then you tried to improve on that
and almost doubled the improved time and created (in my opinion) an
unnecessary type and table.

I fail to see your 'logic' in this.


David Fitzjarrell.