Devart Forums

Code: Select all

create or replace package arrays is

  TYPE rec_nums IS RECORD (
    num1 number,
    num2 number);

  TYPE aarray_type IS TABLE OF rec_nums
    INDEX BY BINARY_INTEGER;

end arrays;


procedure array_samplep(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used
                        p_param2 in varchar2, -- a parameter that might be used in the future, now is not used
                        p_array  in out aarray_type) is
  v_id       number;  
begin

  v_id := 0;
  for v_id in 1..10 loop
    p_array(v_id).num1 := v_id;
    p_array(v_id).num2 := round(dbms_random.value(1,1000));    
  end loop;
  
end array_samplep;

Code: Select all

Code: 
CREATE TYPE rec_nums AS OBJECT 
( 
    num1 number, 
    num2 number 
); 

CREATE TYPE aarray_type AS TABLE OF rec_nums;  

procedure array_samplep(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_param2 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_array  in out aarray_type) is 
  v_id       number;  
begin 

  v_id := 0; 

  p_array := aarray_type();
  for v_id in 1..10 loop
    p_array.EXTEND;
    p_array(v_id).num1 := v_id; 
    p_array(v_id).num2 := round(dbms_random.value(1,1000));    
  end loop; 
  
end array_samplep;

Code: Select all

function array_samplep3(p_param1 in varchar2, -- a parameter that might be used in the future, now is not used 
                        p_param2 in varchar2 -- a parameter that might be used in the future, now is not used 
                        ) return aarray_type
is                        
  v_id       number;  
  p_array aarray_type;
begin 
  v_id := 1; 
  p_array  :=  aarray_type();
   for v_id in 1..10 loop 
    p_array.EXTEND;
    p_array(v_id) := round(dbms_random.value(1,1000));    
 end loop;
  return p_array;
end array_samplep3;

Code: Select all

select * from Table(arrays.array_samplep3('',''))

Code: Select all

create or replace package DAP_contactOverview is
  
  
  -- types for input parameters...

  SUBTYPE dcot_name_t    IS dap_contacttype.dcot_name%TYPE ;
  SUBTYPE dcon_name_t    IS dap_contact.dcon_name%TYPE     ;
  SUBTYPE dcon_city_t    IS dap_contact.dcon_city%TYPE     ;
  SUBTYPE dcon_country_t IS dap_contact.dcon_country%TYPE  ;

  -- table type for output parameter...

  TYPE dcot_name_tt        IS TABLE OF dap_contacttype.dcot_name%TYPE       INDEX BY BINARY_INTEGER ;


procedure DAP_getContacts (i_dcot_Name        in   dcot_name_t,
                           i_dcon_Name        in   dcon_name_t,
                           i_dcon_City        in   dcon_city_t,
                           i_dcon_Country     in   dcon_country_t,
                           o_dcot_Name        out  dcot_name_tt
)  ;

end DAP_contactOverview;


create or replace package body DAP_contactOverview is


cursor csGetContacts (i_dcot_Name    in   dcot_name_t,
                      i_dcon_Name    in   dcon_name_t,
                      i_dcon_City    in   dcon_city_t,
                      i_dcon_Country in   dcon_country_t
)
is
  select dcot.dcot_name
    from dap_contact dcon,
         dap_contacttype dcot
   where dcon.dcot_ref = dcot.dcot_ref
     and dcot.dcot_name like i_dcot_Name
     and dcon.dcon_name like i_dcon_Name
     and dcon.dcon_city like i_dcon_City
     and dcon.dcon_country like i_dcon_Country
  order by dcot.dcot_name, dcon.dcon_name, dcon.dcon_country, dcon.dcon_city, dcon.dcon_street ;


procedure DAP_getContacts (i_dcot_Name        in   dcot_name_t,
                           i_dcon_Name        in   dcon_name_t,
                           i_dcon_City        in   dcon_city_t,
                           i_dcon_Country     in   dcon_country_t,
                           o_dcot_Name        out  dcot_name_tt
)  
is

i integer := 0 ;
cs_rt csGetContacts%rowtype ;
begin

  if not csGetContacts%ISOPEN then
    OPEN csGetContacts (i_dcot_Name, i_dcon_Name, i_dcon_City, i_dcon_Country) ;
  end if;
  
  loop
    FETCH csGetContacts
     INTO cs_rt ; 

    if csGetContacts%ROWCOUNT <= 0 or csGetContacts%NOTFOUND = TRUE then
      exit ;
    end if;

    i := i+1 ;
    
    o_dcot_Name(i) := cs_rt.dcot_name ; 
     
  end loop ; 
  
  if csGetContacts%ISOPEN then
    CLOSE csGetContacts ;
  end if;
    
end;

end DAP_contactOverview;

Code: Select all

procedure TfrmDAP_contactOverview.FillGrdContact();
var
o_cotName : string ;
v: TOraArray;
begin

  with dmDAP_dataModule.OraStoredProc1 do begin

    StoredProcName := 'DAP_CONTACTOVERVIEW.DAP_GETCONTACTS' ;

    PrepareSQL() ;

    Params.ParamByName('i_dcot_Name').value := '%' ;
    Params.ParamByName('i_dcon_Name').value := '%' ;
    Params.ParamByName('i_dcon_City').value := '%' ;
    Params.ParamByName('i_dcon_Country').value := '%' ;

    Params.ParamByName('o_dcot_Name').Length := 5 ;

    Execute ;

    //this line caused a compile error...
    v := Params.ParamByName('o_dcot_Name').value ;
  end;

end;