set serveroutput on
set verify off

declare
 v_exist number;
 v_orig_ol varchar2(100);
 v_orig_cat varchar2(100);
 v_orig_sqltext long;
 
 v_des_ol varchar2(100);
 v_des_cat varchar2(100);

 v_prod_ol varchar2(100);
 v_prod_cat varchar2(100);
 
 v_continuar varchar2(1) := 'S';
 
 --ol name y categoria de la de ol original
 v_param1 varchar2(100) := '&1';
 v_param2 varchar2(100) := '&2';

 --ol name y categoria de la de ol tuneada
 v_param3 varchar2(100) := '&3';
 v_param4 varchar2(100) := '&4';

 --ol name y categoria de la nueva ol producto de la integracion de la ol original y el plan de la ol tuneada
 v_param5 varchar2(100) := '&5';
 v_param6 varchar2(100) := '&6';

 err_num          number;
 err_msg          varchar2 (255);
begin    
    begin
        if (v_param1 is null)or(v_param2 is null)or(v_param3 is null)or(v_param4 is null)or(v_param5 is null)or(v_param6 is null) then
           dbms_output.put_line('parametros insuficientes');
        end if;        
    
        begin
    		    select ol_name, category, sql_text
    		      into v_orig_ol, v_orig_cat, v_orig_sqltext
    		      from outln.ol$
    		     where ol_name = v_param1
    		       and category = v_param2;
        exception
          when no_data_found then
               dbms_output.put_line('error: la ol origen '||v_param1||' no existe');
               raise_application_error(-20001, 'error: la ol origen '||v_param1||' no existe');
               v_continuar := 'N';
        end;
        
        if v_continuar = 'S' then   
    		    begin
    				    select ol_name, category
    				      into v_des_ol, v_des_cat
    				      from outln.ol$
      		     where ol_name = v_param3
      		       and category = v_param4;
    		    exception
    		      when no_data_found then
                 raise_application_error(-20001, 'error: la ol desarrollo '||v_param3||' no existe');
    		           v_continuar := 'N';
    		    end;
    		    
    
          if v_continuar = 'S' then
              begin
      						    select ol_name, category
      						      into v_prod_ol, v_prod_cat
      						      from outln.ol$
          		     where ol_name = v_param5
          		       and category = v_param6;
    
       		        v_continuar := 'N';
      						  exception
          	      when no_data_found then
          	        null;
        		    end;
    						    
              if v_continuar = 'S' then    						      
    						      begin
      						      --duplicamos la ol origen
                  insert into outln.ol$ 
                  (ol_name, sql_text, textlen, signature, hash_value, hash_value2, category, version, creator, timestamp, flags, hintcount, spare1, spare2)
                  select v_param5, '', textlen, signature, hash_value, hash_value2, v_param6, version, creator, timestamp, flags, hintcount, spare1, spare2
                    from outln.ol$ 
            		     where ol_name = v_param1
            		       and category = v_param2;
            		    
            		    update outln.ol$ 
            		       set sql_text = v_orig_sqltext
            		     where ol_name = v_param5
            		       and category = v_param6;
            		    
                  insert into outln.ol$hints
                  (ol_name, hint#, category, hint_type, hint_text, stage#, node#, table_name, table_tin, table_pos, ref_id, user_table_name, cost, cardinality, bytes, hint_textoff, hint_textlen, join_pred, spare1, spare2, hint_string)
                  select v_param5, hint#, v_param6, hint_type, hint_text, stage#, node#, table_name, table_tin, table_pos, ref_id, user_table_name, cost, cardinality, bytes, hint_textoff, hint_textlen, join_pred, spare1, spare2, hint_string
                    from outln.ol$hints 
            		     where ol_name = v_param1
            		       and category = v_param2;
      
                  insert into outln.ol$nodes
                  (ol_name, category, node_id, parent_id, node_type, node_textlen, node_textoff, node_name)
                  select v_param5, v_param6, node_id, parent_id, node_type, node_textlen, node_textoff, node_name 
                    from outln.ol$nodes 
            		     where ol_name = v_param1
            		       and category = v_param2;
            		       
            		    --integracion de la ol original y la ol tuneada en la nueva ol   
            		    delete outln.ol$hints 
                  where ol_name = v_param5
                    and category = v_param6;
      
                  insert into outln.ol$hints
                  (ol_name, hint#, category, hint_type, hint_text, stage#, node#, table_name, table_tin, table_pos, ref_id, user_table_name, cost, cardinality, bytes, hint_textoff, hint_textlen, join_pred, spare1, spare2, hint_string)
                  select v_param5, hint#, v_param6, hint_type, hint_text, stage#, node#, table_name, table_tin, table_pos, ref_id, user_table_name, cost, cardinality, bytes, hint_textoff, hint_textlen, join_pred, spare1, spare2, hint_string
                    from outln.ol$hints 
            		     where ol_name = v_param3
            		       and category = v_param4;
                  
                  /*
                  delete outln.ol$nodes
                   where ol_name = v_param5
                     and category = v_param6;
      
                  insert into outln.ol$nodes
                  (ol_name, category, node_id, parent_id, node_type, node_textlen, node_textoff, node_name)
                  select v_param5, v_param6, node_id, parent_id, node_type, node_textlen, node_textoff, node_name 
                    from outln.ol$nodes 
            		     where ol_name = v_param3
            		       and category = v_param4;
            		    */
            		    
                  update outln.ol$
                     set hintcount = (select count(1) from outln.ol$hints  where ol_name = v_param5 and category = v_param6)
                   where ol_name = v_param5
                     and category = v_param6;
                   
                  commit;

 				             dbms_output.put_line('ol '||v_param5||' creada sin errores en la categoria: '||v_param6);
    						      exception
          	      when others then
          	           rollback;
                      err_num := sqlcode;
                      err_msg := sqlerrm;

                      raise_application_error(-20001, 'error: la ol '||v_param5||' no ha podido ser integrada'||chr(13)||err_num||'-'||err_msg);
    						      end;
    						    else
                raise_application_error(-20001, 'error: la ol integrada '||v_param5||' ya existe en la categoria: '||v_param6);
    				        v_continuar := 'N';
    				      end if;
    				  end if;
        end if;
    exception
     when others then
          err_num := sqlcode;
          err_msg := sqlerrm;

          rollback;
          raise_application_error(-20001, 'error general. '||err_num||'-'||err_msg);
    end;
end;
/

undefine 1
undefine 2
undefine 3
undefine 4
undefine 5
undefine 6