| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Function Return Type Record, How to call ?
A copy of this was sent to "Sean Zhang" <sean_at_cnfei.com>
(if that email address didn't require changing)
On Fri, 18 Jun 1999 00:17:45 GMT, you wrote:
>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?
>
when calling PL/SQL from sql -- the function must return a SQL datatype (something you could create a table with for example). Since you cannot create a table with a column of type 'FEE_TYPE', SQL cannot see that datatype and hence, SQL cannot be used with that type.
To return a 'record' you would need to use Object types in Oracle8. For example:
SQL> create or replace type myRecord as object
2 ( x int, 3 y int )
SQL> create or replace function myRecordFunc return myRecord
2 as
3 begin
4 return myRecord( 1, 2 );
5 end;
6 /
Function created.
SQL> select myRecordFunc from dual
2 /
MYRECORDFUNC(X, Y)
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |