Home » SQL & PL/SQL » SQL & PL/SQL » How do I make use of the type that I had created in if then code?
How do I make use of the type that I had created in if then code? [message #428850] Fri, 30 October 2009 05:49 Go to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
I created pCust as the user type that I had created.

declare
  lMyCust my_cust_list := my_cust_list('HAT','ELS','ELX');
  
  CURSOR WBReceive 
  IS
  SELECT 'D' record_id, 'CAS' org_code, 'DIEBANK' dept, 'RECEIVE' tran_type,
         customercode, originalcustomerlotnumber intersil_lot_no, device, appid, PACKAGE,
         nvl (waferdiesquantity, startquantity) die_qty, waferquantity, lottype,
         processspeed, shipfrom, lotobject, lotorderid,
         substr (txntime, 3, 6) tran_date, substr (txntime, 10, 6) tran_time
    FROM edi_rpt_temp
   WHERE customercode in (select * from table(lMyCust))
     AND transtype = 'WBReceive';
begin
  <your code here>
end;


Incorporated into function pcust my_cust_type

FUNCTION edi_rpt_getdept (pdevice varchar2, pcust my_cust_type) return varchar2 
IS
  pdept varchar2(20) default ' ';
 
  Cursor getdept 
  is
  select source_part_type 
    from fwsp.pp_intrepid_xref@smesp
   where source_part_name = pdevice;
BEGIN
  for rec in getdept 
  loop
    If Rec.Source_Part_Type = 'WA' Then
      pdept := 'WAFBANK';
    elsif Rec.Source_Part_Type = 'GD' Then
      pdept := 'DIEBANK';
    else
      pdept := 'DIEBANK';
    End If;
  end loop;
  
  return pdept;
END


edi_rpt_getdept (<device value>, my_cust_type('a','b','c'))


but now I dont know how to make use of the user type in the below sub procedure.

I wrote my code this way pcust = HAT or pcust = HUS which is incorect.

Can someone help me please?

procedure
EDI_RPT_INSERTGLOBAL (sdate in varchar2, edate IN VARCHAR2, pcust varchar2) IS

-- Declare program variables as shown above
rec1           varchar2(5000);
str1           varchar2(5000);
sqlstr         varchar2(5000);

Cursor get_transtype is
select * from edi_transtype_tab
where customercode = pcust;

BEGIN

for rec in get_transtype loop

if pcust = 'HAT' or pcust = 'HUS' or pcust = 'ELS' or pcust = 'ELX' or pcust = 'ISL' Then
        if rec.transtype = 'WBReceive' then
            rec1 := edirpt.edi_rpt_WBReceive(sdate,edate,rec.groupby,pcust);

end if;
end if;
commit;
end loop;
Re: How do I make use of the type that I had created in if then code? [message #428855 is a reply to message #428850] Fri, 30 October 2009 06:07 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You haven't shown Oracle generated error.
You haven't shown the code of edirpt.edi_rpt_WBReceive.

I don't find any relation between the codes you have shown.

regards,
Delna
Previous Topic: challenging one (merged)
Next Topic: PLS-00363: expression 'P_HANDLE_I' cannot be used as an assignment target
Goto Forum:
  


Current Time: Sat Oct 01 17:51:39 CDT 2016

Total time taken to generate the page: 0.08285 seconds