Function ORACLEHOMES(strComputer As String)
    Const HKEY_LOCAL_MACHINE = &H80000002
    ORACLEHOMES = ""
    Dim strKeyPath
    Dim arrSubKeys
    Dim oReg
    Dim strValueName
    Dim strValue
    'strComputer = "."
    Set oReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
    strKeyPath = "SOFTWARE\ORACLE"
    oReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys
    For Each subkey In arrSubKeys
        If Left(subkey, 4) = "KEY_" Then
            strValueName = "ORACLE_HOME"
            oReg.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strValueName, strValue
            If ORACLEHOMES = "" Then
                ORACLEHOMES = strValue
            Else
                ORACLEHOMES = ORACLEHOMES & ";" & strValue
            End If
        End If
    Next
End Function

Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)
  Dim strConOracle, oConOracle, oRsOracle
  Dim StrResult As String
  
  StrResult = ""
  
  strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
         "CONNECTSTRING=(DESCRIPTION=" & _
         "(ADDRESS=(PROTOCOL=TCP)" & _
         "(HOST=" & strHost & ")(PORT=1521))" & _
         "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
  Set oConOracle = CreateObject("ADODB.Connection")
  Set oRsOracle = CreateObject("ADODB.Recordset")
  oConOracle.Open strConOracle
  Set oRsOracle = oConOracle.Execute(strSQL)
  Do While Not oRsOracle.EOF
      If StrResult <> "" Then
        StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value
      Else
        StrResult = oRsOracle.Fields(0).Value
      End If
    oRsOracle.MoveNext
  Loop
  oConOracle.Close
  Set oRsOracle = Nothing
  Set oConOracle = Nothing
  ORAQUERY = StrResult
End Function

create or replace function "ORACLE_FOO"
(foo_arg1 in NUMBER)
return NUMBER
is
rt NUMBER := 0;
begin
  IF foo_arg1 > 1 THEN
    SELECT 1 INTO rt;
  ELSE
    SELECT 2 INTO rt;
  END IF;
  RETURN (rt);
end;

SELECT ORACLE_FOO(1) FROM dual;

create table TMP_LOG (HORA date, ENTRADA varchar(4000));

INSERT INTO TMP_LOG (HORA, ENTRADA) VALUES(SYSDATE, 
  'V_NOM_DESTINATARIO1: '||V_NOM_DESTINATARIO1||', V_DOM_DESTINATARIO1: '||V_DOM_DESTINATARIO1||
  ', P_NOM_DESTINATARIO_2: '||P_NOM_DESTINATARIO_2||', P_DOM_DESTINATARIO_2: '||P_DOM_DESTINATARIO_2||
  ', B_DESTINATARIO: '||B_DESTINATARIO||
  ', V_NOM_DESTINATARIO2: '||V_NOM_DESTINATARIO2||', V_DOM_DESTINATARIO2: '||V_DOM_DESTINATARIO2);

select * from tmp_log order by hora desc;

drop table TMP_LOG;

c1 varchar2(200);
c2 varchar2(200);
c3 varchar2(200);

fetch v_Return into c1, c2, c3;
loop
  EXIT WHEN v_Return%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(c1||', '||c2||', '||c3);
  fetch v_Return into c1, c2, c3;
end loop;
close v_Return;

DECLARE
  PVCOD_DESPACHO NUMBER;
  PVCOD_IDIOMA VARCHAR2(200);
  v_Return PG_SGIE_Types.cursor_type;
  c1 varchar2(200);
  c2 varchar2(200);
  c3 varchar2(200);
BEGIN
  PVCOD_DESPACHO := 2792;
  PVCOD_IDIOMA := '1';

  v_Return := FU_LISTAENVIOSDESPACHO(
    PVCOD_DESPACHO => PVCOD_DESPACHO,
    PVCOD_IDIOMA => PVCOD_IDIOMA
  );
  -- Modify the code to output the variable
  --DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  
  fetch v_Return into c1, c2, c3;
  loop
    EXIT WHEN v_Return%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(c1||', '||c2||', '||c3);
    fetch v_Return into c1, c2, c3;
  end loop;
  close v_Return;
END;

CREATE TABLE schema.table AS SELECT * FROM schema.table

alter system set PROCESSES=100 scope=SPFILE;
alter system set TRANSACTIONS=126 scope=SPFILE;
alter system set SESSIONS=115 scope=SPFILE;

select name, value from v$parameter where name like LOWER('%license%') ;

select name, value from v$parameter where name like LOWER('%session%') ;

namespace :db do
  task :fix_clobs => [:environment] do |t|
    @outfile = File.expand_path(File.join(RAILS_ROOT,"db","fix_clobs.sql"))
  
    File.open(@outfile, "w") do |file|
      ActiveRecord::Base.connection.tables.each do |table_name|
        begin
          model = eval(table_name.classify)
          model.columns.each do |column|
            if column.sql_type == "CLOB"
              file.write("ALTER TABLE #{table_name} ADD #{column.name}_temp VARCHAR2(4000);\n")
              file.write("UPDATE #{table_name} SET #{column.name}_temp = #{column.name};\n")
              file.write("COMMIT;\n")
              file.write("ALTER TABLE #{table_name} DROP COLUMN #{column.name};\n")
              file.write("ALTER TABLE #{table_name} RENAME COLUMN #{column.name}_temp TO #{column.name};\n")
              file.write("\n")
            end
          end
         rescue => ex
           puts "Failed for #{table_name} with #{ex.class}"
         end
      end
    end
  end
end

namespace :db do
  task :adjust_sequences => :environment do
    ActiveRecord::Base.connection.tables.each do |table|
      begin
        count = ActiveRecord::Base.count_by_sql("SELECT MAX(id) FROM #{table}")
        seq = "#{table}_seq"
        ActiveRecord::Base.connection.execute("DROP SEQUENCE #{seq}")
        ActiveRecord::Base.connection.execute("CREATE SEQUENCE #{seq} START WITH #{count+1}")
      rescue => ex
        puts "Failed for #{table} with #{ex.class}"
      end
    end
  end
end