create or replace package body pa_table_to_array is
/* *****************************************************************************
* $Id       : $
* $Author   : $
* $DateTime : $
* $Change   : $
* 
* Beschreibung:
*     Array Versus Tabelle.
*     Das Paket liest die Daten einer Tabelle aus, mit deren 
*     ein Array gefuellt werden wird.
* 
* Historie:
*     16.08.2009 FJ Erzeugung der Datei.
***************************************************************************** */

  -- Variablen globlalen innenhalb des Pakets.
  type      t_host is table of hostname_mapping_array%rowtype;
  v_host    t_host;

  Procedure p_refresh_get_host is
  
  Begin

    select *
      bulk collect into v_host
      from hostname_mapping_array
    ;

  End p_refresh_get_host;

  Procedure p_show_hosts is
  Begin

    for j in v_host.first .. v_host.last 
    loop
      dbms_output.put_line ('hostalias       : ' || v_host(j).hostalias     ) ;
      dbms_output.put_line ('enterprise_id   : ' || v_host(j).enterprise_id ) ;
      dbms_output.put_line ('protocol        : ' || v_host(j).protocol      ) ;
      dbms_output.put_line ('hostname        : ' || v_host(j).hostname      ) ;
      dbms_output.put_line ('port            : ' || v_host(j).port          ) ;
    end loop ;

  End p_show_hosts;

  ------------------------------------------------------------------------------

  Procedure p_auto_refresh is
    v_count number ;
  Begin

    v_count :=  v_host.count ;

  Exception
    when others then
      p_refresh_get_host ;
  End p_auto_refresh;

  ------------------------------------------------------------------------------

  Function  f_get_host_url( p_hostalias     in varchar2,
                            p_enterprise_id in number default 1)
    return varchar2 is
    v_hostname         varchar2(255)     ;
    v_exists           number(1)         ;
  Begin
    p_auto_refresh ;

    v_exists  := 0 ; -- not exists
    for j in v_host.first .. v_host.last 
    loop
      if v_host(j).hostalias       = upper(p_hostalias) and 
         v_host(j).enterprise_id   = p_enterprise_id then

         if trim(v_host(j).protocol) is not null then
           v_hostname := v_host(j).protocol || '://' ;
         end if ;
         
         v_hostname := v_hostname || v_host(j).hostname ;

         if trim(v_host(j).port) is not null then
           v_hostname := v_hostname || ':' || to_char(v_host(j).port) ;
         end if ;
        v_exists  := v_exists + 1 ; -- exists
      end if ;
    end loop ;

    if v_exists = 0 then
      return v_exists ;
    elsif v_exists > 1 then
      return v_exists ;
    end if;

    return v_hostname ;

  Exception
    when others then
      raise ;    
  End f_get_host_url;

End pa_table_to_array ;
/
show err ;