Re: Function Return Type Record, How to call ?

A function that is used in an SQL statement must return only valid Oracle RDBMS datatypes(i.e. number, varchar2, date, etc.). You could write the function to take all the fields from the record individually and call it with the columns.

create function my_fnct(input1 number, input2 number, input3 number) return number is
begin
return (input1 + input2 + input3);
end;
/

select my_fnct(col1, col2, col3)
from my_table;

This works.

Kenny Gump

Sean Zhang wrote in message ...
>Hi, Evveryone,
>
>I need a customized calculation for a query( view) , so I came up with
the
>following solution:
>
> Create Pakage FEE_PKG is
> Type FEE_TYPE is Record(duty_fee number(5,2),
> frt_fee
>number(5,2),
> agent_fee
>number(5,2),
> .............
> desn_fee
>number(6,2)
> );
> Function Fee_function ( Part# IN varchar2) return FEE_TYPE;
> end FEE_PKG;
>--------also with the pakage body to calculate all sorts of cost.
>
> The pakage and body were compiled and fine.
>
> Question:
> suppose I have a <stock> table with a column <part#> and I want to Make
>a
> view to list all kinds of Cost associated with the <part#> , I create
a
>View like :
> Create or replace view COST_VIEW as
> select s.part# , fee_pkg.fee_function.duty_fee as duty_fee,
> fee_pkg.fee_function.frt_fee as frt_fee from stock s ;
>
> Then I got error message like :
> "Invalid column name " ..
>
> I know something is wrong with the function datatype. Can anybody help
me?
>
>Any suggestions and advises are greatly appreciated.
>
>Sean Zhang
>mailto:sean_at_cnfei.com
>
>
>
>
>
Received on Fri Jun 18 1999 - 06:52:52 CDT