Rem
Rem $Header: patching_jobs_util_body.sql 27-jun-2007.05:39:58 kavkrish Exp $
Rem
Rem patching_jobs_util_body.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      patching_jobs_util_body.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    kavkrish    06/27/07 - Merging Fannie-Mae one-off changes
Rem    kavkrish    03/22/07 - bug-5943583
Rem    kavkrish    02/13/07 - Using targets hostname in function_get_listeners 
Rem    abhalla     07/30/06 - fixing bug 4870388, changing method
Rem                           function_get_sids to return all sids of a home
Rem    mningomb    04/15/06 - Bug 5164614
Rem    shgangul    09/08/05 - Bug 4596343: Filter listeners based on
Rem                           machine name as well 
Rem    mningomb    08/30/05 - 
Rem    abhalla     05/04/05 - Make non-jobowner to add new parameters 
Rem    pdasika     04/28/05 - Changes to make Pre/post script paramters 
Rem                           optional 
Rem    mningomb    04/13/05 - mningomb_dbpatch
Rem    shgangul    04/01/05 - Added procedures to get listeners and sids
Rem    mningomb    03/29/05 - Created
Rem
CREATE OR REPLACE PACKAGE BODY MGMT_OH_PATCHING_UTIL
IS


FUNCTION  tokenize_it( i_str IN VARCHAR2, delimeter VARCHAR2)  RETURN TOKEN_LIST
  IS
   l_n        NUMBER;
   l_data     TOKEN_LIST := TOKEN_LIST();
   l_str      VARCHAR2(4096) := i_str || delimeter;

 BEGIN
  LOOP
    l_n := instr( l_str, delimeter);
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  END LOOP;


    l_n := 1;
    loop
     exit when (l_n > l_data.count);
     dbms_output.put_line(l_data(l_n));
     l_n := l_n +1;
    end loop;

 return l_data;


 END;

PROCEDURE filter_homes_creds_on_host(p_job_id RAW,
                 p_execution_id RAW,
                 index_of_node int,
                 job_owner varchar2
                ) IS
l_temp_vector_data MGMT_JOB_VECTOR_PARAMS;
l_temp_data VARCHAR2(4096);

l_current_homes MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_current_usernames MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
l_current_passwords MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

l_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_homes TOKEN_LIST;
l_usernames TOKEN_LIST;
l_passwords TOKEN_LIST;
l_count NUMBER := 0;
l_skip_count NUMBER := 0;
l_curr_homes_cnt NUMBER := 0;
l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;

MODULE_NAME VARCHAR2(20) := 'OH_PATCHING_UTIL';

BEGIN

    -- Look into table 'emdw_trace_data' for logging data


    SELECT  vector_value INTO l_temp_vector_data
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'homes';

    FOR  i IN 1..(index_of_node-1)
    LOOP
     l_temp_data := l_temp_vector_data(i);
     l_homes := tokenize_it(l_temp_data,'&');
     l_skip_count := l_skip_count + l_homes.count;
    END LOOP;

    IF emdw_log.p_is_debug_set
    THEN
        emdw_log.debug('homes size = ' || l_temp_vector_data.count || '  and index_of_node = ' || index_of_node, MODULE_NAME);
    END IF ;

    l_count:=0;
    l_temp_data := l_temp_vector_data(index_of_node);
    l_homes := tokenize_it(l_temp_data,'&');
    l_curr_homes_cnt := l_homes.count;
    LOOP
      l_count := l_count+1;
      exit when (l_count > l_homes.count);
      l_current_homes.extend(1);
      l_current_homes(l_current_homes.count) := l_homes(l_count);
    END LOOP;

    SELECT  vector_value INTO l_temp_vector_data
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'usernames';

    for i in (l_skip_count+1)..(l_skip_count+l_curr_homes_cnt)
    LOOP
     l_current_usernames.extend(1);
     l_current_usernames(l_current_usernames.count) := l_temp_vector_data(i);
    END LOOP;

    SELECT  vector_value INTO l_temp_vector_data
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'passwords';

    for i in (l_skip_count+1)..(l_skip_count+l_curr_homes_cnt)
    LOOP
     l_current_passwords.extend(1);
     l_current_passwords(l_current_passwords.count) := l_temp_vector_data(i);
    END LOOP;

    IF emdw_log.p_is_debug_set
    THEN
        emdw_log.debug('home no.' || l_count || ' is ' || l_current_homes(l_current_homes.count),MODULE_NAME) ;
    END IF ;

    l_param_list.extend(3);
    l_param_list(1) := MGMT_JOB_PARAM_RECORD('current_homes',
                                             MGMT_JOBS.PARAM_TYPE_VECTOR,
                                             null,
                                             l_current_homes);
    l_param_list(2) := MGMT_JOB_PARAM_RECORD('current_usernames',
                                             MGMT_JOBS.PARAM_TYPE_VECTOR,
                                             null,
                                             l_current_usernames);
    l_param_list(3) := MGMT_JOB_PARAM_RECORD('current_passwords',
                                             MGMT_JOBS.PARAM_TYPE_VECTOR,
                                             null,
                                             l_current_passwords);


    SETEMUSERCONTEXT(job_owner, MGMT_USER.OP_SET_IDENTIFIER);
      MGMT_JOBS.add_job_parameters(p_job_id, p_execution_id, l_param_list);
    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);


  END;
PROCEDURE filter_home_targets(p_job_id RAW,
                 p_execution_id RAW,
                 index_of_node int,
                 index_of_home int,
                 job_owner varchar2
                ) IS
l_temp_vector_data MGMT_JOB_VECTOR_PARAMS;
l_temp_data VARCHAR2(4096);
l_machine_name VARCHAR2(4096);

l_current_targets MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

l_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_homes_and_targets TOKEN_LIST;
l_targets TOKEN_LIST;
l_count NUMBER := 0;
l_param_cnt NUMBER := 0;

l_scalar_targets VARCHAR2(4096);
l_oracle_home VARCHAR2(4096);
l_sids VARCHAR2(4096);
l_listeners VARCHAR2(4096);

l_temp_scalar_data VARCHAR2(4096);
final_prescript VARCHAR2(4096);
final_postscript VARCHAR2(4096);
indx int;
l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;

MODULE_NAME VARCHAR2(100) := 'MGMT_OH_PATCHING_UTIL';
DEBUG BOOLEAN := false;


BEGIN


    SELECT  vector_value INTO l_temp_vector_data
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'targets';

    l_temp_data := l_temp_vector_data(index_of_node);
    l_homes_and_targets := tokenize_it(l_temp_data,'*');
    l_temp_data := l_homes_and_targets(index_of_home);
    l_targets := tokenize_it(l_temp_data,'&');

    SELECT  vector_value INTO l_temp_vector_data
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'hosts';

    l_machine_name := l_temp_vector_data(index_of_node);

    l_count:=0;
    LOOP
      l_count := l_count+1;
      exit when (l_count > l_targets.count);
      l_current_targets.extend(1);
      l_current_targets(l_current_targets.count) := l_targets(l_count);
      if l_count = 1 then
       l_scalar_targets := l_targets(l_count) || ',';
      end if;
      l_scalar_targets := l_scalar_targets || l_targets(l_count) || ',';
    END LOOP;

    l_scalar_targets := substr(l_scalar_targets,1,length(l_scalar_targets)-1);


    SELECT  scalar_value INTO l_oracle_home
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=p_execution_id
    AND     parameter_name = 'oraclehome';


    l_sids := function_get_sids(l_oracle_home,l_machine_name);
    l_listeners := function_get_listeners(l_oracle_home, l_machine_name);

	begin 

     SELECT  scalar_value INTO l_temp_scalar_data
     FROM    MGMT_JOB_PARAMETER
     WHERE   job_id=p_job_id
     AND     execution_id=p_execution_id
     AND     parameter_name = 'patch_prescript_new';


     indx := instr(l_temp_scalar_data,'`dbsids`');
     if (indx > 0) then
      final_prescript := substr(l_temp_scalar_data,1,indx-1) || l_sids || substr(l_temp_scalar_data,indx+8);
     else
      final_prescript := l_temp_scalar_data;
     end if;	    
	 
     indx := instr(final_prescript,'`listeners`');
     if (indx > 0) then
      final_prescript := substr(final_prescript,1,indx-1) || l_listeners || substr(final_prescript,indx+11);
     end if;

	 EXCEPTION
     WHEN NO_DATA_FOUND THEN
       final_prescript := '';	
	 end;

	begin 
     SELECT  scalar_value INTO l_temp_scalar_data
     FROM    MGMT_JOB_PARAMETER
     WHERE   job_id=p_job_id
     AND     execution_id=p_execution_id
     AND     parameter_name = 'patch_postscript_new';

     indx := instr(l_temp_scalar_data,'`dbsids`');
     if (indx > 0) then
      final_postscript := substr(l_temp_scalar_data,1,indx-1) || l_sids || substr(l_temp_scalar_data,indx+8);
     else
      final_postscript := l_temp_scalar_data;
	 end if;

     indx := instr(final_postscript,'`listeners`');
     if (indx > 0) then
      final_postscript := substr(final_postscript,1,indx-1) || l_listeners || substr(final_postscript,indx+11);
     end if;

	 EXCEPTION
     WHEN NO_DATA_FOUND THEN
       final_postscript := '';	
	 end;
	 

	l_param_cnt := l_param_cnt + 1;
    l_param_list.extend(1);
    l_param_list(l_param_cnt) := MGMT_JOB_PARAM_RECORD('targets',
                                             MGMT_JOBS.PARAM_TYPE_VECTOR,
                                             null,
                                             l_current_targets);
									
	if (final_prescript is not null) then
   	 l_param_cnt := l_param_cnt + 1;
 	 l_param_list.extend(1);
     l_param_list(l_param_cnt) := MGMT_JOB_PARAM_RECORD('final_patch_prescript',
                                             MGMT_JOBS.PARAM_TYPE_SCALAR,
                                             final_prescript,
                                             null);
	end if;

	if (final_postscript is not null) then
   	 l_param_cnt := l_param_cnt + 1;
 	 l_param_list.extend(1);
     l_param_list(l_param_cnt)  := MGMT_JOB_PARAM_RECORD('final_patch_postscript',
                                             MGMT_JOBS.PARAM_TYPE_SCALAR,
                                             final_postscript,
                                             null);
	end if;
	
     l_param_cnt := l_param_cnt + 1;
 	 l_param_list.extend(1);
     l_param_list(l_param_cnt)  := MGMT_JOB_PARAM_RECORD('dbsids',
                                             MGMT_JOBS.PARAM_TYPE_SCALAR,
                                             l_sids,
                                             null);
   	 l_param_cnt := l_param_cnt + 1;
 	 l_param_list.extend(1);
     l_param_list(l_param_cnt)  := MGMT_JOB_PARAM_RECORD('listeners',
                                             MGMT_JOBS.PARAM_TYPE_SCALAR,
                                             l_listeners,
                                             null);
    SETEMUSERCONTEXT(job_owner, MGMT_USER.OP_SET_IDENTIFIER);
      MGMT_JOBS.add_job_parameters(p_job_id, p_execution_id, l_param_list);
    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);


  END;

FUNCTION function_get_listeners
                (p_dest_home MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE,
                 p_machine_name MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE)
RETURN VARCHAR2
IS

l_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_listeners VARCHAR2(4096);
l_listener MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE;
l_index int;
lp_listeners VARCHAR2(4096);

CURSOR l_mgmt_target_properties_cur IS
    SELECT DISTINCT p.property_value
      FROM mgmt_targets t,
           mgmt_target_properties h,
           mgmt_target_properties p
     WHERE t.target_guid=p.target_guid
       AND t.target_guid=h.target_guid
       AND t.target_type='oracle_listener'
       AND t.host_name=p_machine_name
       AND h.property_name='OracleHome'
       AND h.property_value=p_dest_home
       AND p.property_name='LsnrName';

BEGIN

    /* Initialize */
    l_listener := '';
    l_listeners := '';
    l_index := 1;

    OPEN l_mgmt_target_properties_cur;

    LOOP

        /* Retrieve one listener name */
        FETCH l_mgmt_target_properties_cur INTO l_listener;

        /* Exit if there is no more rows */
        EXIT WHEN l_mgmt_target_properties_cur%NOTFOUND;

        /* Add the listener to the listener list */
        IF l_index = 1
        THEN
            l_listeners := l_listener;
        ELSE
            l_listeners := l_listeners || '&' || l_listener;
        END IF;

        /* Increment the index */
        l_index := l_index + 1;

    END LOOP;

    CLOSE l_mgmt_target_properties_cur;

    return l_listeners;

END;

FUNCTION function_get_sids(
   p_dest_home MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE,
   p_host_name MGMT_TARGETS.HOST_NAME%TYPE)
RETURN VARCHAR2
IS
l_db_sids VARCHAR2(4096) := '';
cursor C1 is 
        SELECT p.property_value
          as l_sid
        FROM mgmt_targets t, mgmt_target_properties p, 
             mgmt_target_properties h
        WHERE t.target_guid=p.target_guid
         AND t.host_name = p_host_name
         AND t.target_type='oracle_database'            
         AND p.property_name='SID'
  		   AND t.target_guid = h.target_guid
  		   AND h.property_name = 'OracleHome'
  		   AND h.property_value = p_dest_home
  		   AND h.target_guid = p.target_guid;

c1Rec C1%ROWTYPE;
BEGIN

    l_db_sids := '';
    
      FOR c1Rec in C1
      LOOP
       
      IF c1Rec.l_sid IS NOT NULL THEN       
      IF l_db_sids IS NULL THEN                      
      l_db_sids := c1Rec.l_sid;             
      ELSIF (instr(l_db_sids,c1Rec.l_sid) <= 0) THEN
      l_db_sids := l_db_sids || '&' || c1Rec.l_sid;             
      END IF;
      END IF;
       
      END LOOP;
      RETURN l_db_sids;
      EXCEPTION 
      WHEN NO_DATA_FOUND THEN
      l_db_sids := '';

  END;
END;
/

show errors