Oracle PLSQL Dynamic Loop

Dynamic Loop

 

 

 

 Declare  
   v_sql      VARCHAR2(32000);  
   TYPE t_refcrs IS REF CURSOR;  
   c_exttable t_refcrs;  
   v_my_field varchar2(320000 char);  
  BEGIN  
      v_sql := 'select my_field from my_table';  
      v_count := 0;  
      OPEN c_exttable FOR v_sql;  
       LOOP  
        FETCH c_exttable INTO v_v_my_field;  
        EXIT WHEN C_EXTTABLE%NOTFOUND;  
           --My operation  
       END LOOP;  
 End;  

Kill sessions in Oracle

How to kill a running session in Oracle?

 SELECT 'exec sys.kill_session(' || sesion.sid || ',' || sesion.serial# || ');', sql_text  
 from v$sqltext sqltext, v$session sesion  
 where sesion.sql_hash_value = sqltext.hash_value  
 and sesion.sql_address = sqltext.address  
 and sesion.username is not null  
 order by sqltext.piece;  

Oracle: Multiple insert conditiona​l

The goal is to insert multiple lines into several lines and track the possible errors as well...

 insert all   
 when 1=1  
 then into zzz_tab_output_1 (OF1, OF2, OFK) values (IF1, IF2, IF3)  
 LOG ERRORS INTO err$_zzz_tab_output_1 (1) REJECT LIMIT UNLIMITED  
 when exists (select 1 from ZZZ_Tab_INPUT_1 where ifk in (select ofk from zzz_tab_output_1))  
 then into zzz_tab_output_2 (OF1, OF3, OFK) values (IF1, IF3, IFK)  
 LOG ERRORS INTO err$_zzz_tab_output_2 (2) REJECT LIMIT UNLIMITED  
 Select i.IF1, j.IF2, j.IF3, IFK from ZZZ_Tab_INPUT_1 i join ZZZ_Tab_INPUT_2 j using (IFK);  

Oracle: import XLS file into Oracle stable

How to import an XLS file into an Oracle Table:

 procedure read_date_amount(p_filename in varchar2, pr_date out date, pr_amount out number) as  
  fp utl_file.file_type;  
  v_line varchar2(16384 char);  
  v_relevant_lines clob := '';  
  v_distance number := null;  
  v_filename varchar2(16384 char) := p_filename;  
  v_ret NUMBER;  
 begin  
  BEGIN  
   fp := utl_file.fopen(g_cs2_cmd_folder, v_filename, 'r', 16384);  
  exception when utl_file.invalid_operation then  
   write_log('Couldn''t open file ' || v_filename);  
   return;  
  end;  
  loop  
   begin  
    utl_file.get_line(fp, v_line);  
    if v_distance is null and v_line like '%INFOH%' then  
     v_distance := 0;  
     v_relevant_lines := substr(v_line, instr(v_line, 'INFOH'));  
    elsif v_distance is not null then  
     v_relevant_lines := v_relevant_lines || ' ' || v_line;  
     v_distance := v_distance + 1;  
     if v_distance = 5 then  
      pr_date := to_date(substr(v_relevant_lines, instr(v_relevant_lines, '<Data ss:Type="String">', 1, 7) + 23, 10), 'dd.mm.yyyy');  
      pr_amount := to_number(substr(v_relevant_lines, instr(v_relevant_lines, '<Data ss:Type="Number">', 1, 1) + 23, 10));  
      exit;  
     end if;  
    end if;      
   exception when no_data_found then  
    exit;  
   end;  
  end loop;  
  utl_file.fclose(fp);  
 end;