Re: Forms6.0, PL/SQL can't see Table with user-defined-type columns
Date: Mon, 07 Jun 1999 16:56:17 GMT
Message-ID: <7jgtjb$sbn$1_at_nnrp1.deja.com>
In oracle 7, we used Stored Functions as "Column Functions" then created views, or select statements against these table objects and column functions. Perhaps this approach would also work just fine in Oracle 8. Example:
- return a Y or N depending on where non-conformance exists
Create Function NonConformanceYesNo(viOrderNum in number)
Return varchar2 as
Cursor cNonCon is
select 'x' form NonCon_Table
where ordernum = viOrderNum;
MyTemp varchar2(1);
Begin
Open cNonCon;
Fetch cNonCon into MyTemp;
If cNonCon%NOTFOUND then
MyTemp := 'N';
End If;
Close cNonCon;
Return MyTemp;
End;
- Now use this --- Select OrderNum, Description,..., NonConformanceYesNo(OrderNum) from Order_Table;
- or make a view --- Create view OrderWithNonCon as Select OrderNum, Description,..., NonConformanceYesNo(OrderNum) as NonConFlag from Order_Table;
and use this...
Select OrderNum, Description,...., NonConFlag
from OrderWithNonCon;
Same rules apply to function as table: If you are explicit with table (select....from owner.table) then you will also need to be explicit with function (...., owner.func(x),...). If you are using SYNONYMs, then you will need a SYNONYM for the function as well.
Robert Proffitt
Beckman Coulter
RTProffitt_at_beckman.com
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Jun 07 1999 - 18:56:17 CEST