DZone Snippets: oracle code

Excel VBA : read registry key values on a remote computer using WMI

bouffon69 (Sylvain Le Courtois) — Fri, 19 Oct 2007 12:03:49 GMT

// VBA code to paste in an module



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

Excel : Make a query on a Oracle database and return the result (useful for sheet formulas)

bouffon69 (Sylvain Le Courtois) — Mon, 10 Sep 2007 15:26:22 GMT

// This should be pasted in a module of the workbook



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 and call an oracle function

davetrane (David Davis) — Wed, 06 Jun 2007 21:22:16 GMT



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;

Debug logs in the database

koke24 (Koke) — Wed, 30 May 2007 16:59:19 GMT

How to log things in the database

Create the table:



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

Write the things I need to the table:



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);

Consult the things written:



select * from tmp_log order by hora desc;

Dont forget to drop it at the end:



drop table TMP_LOG;

Mostar los resultados de un cursor

koke24 (Koke) — Fri, 04 May 2007 10:04:24 GMT

Esto muestra los resultados de un cursor en la ventana de pruebas del SQL Developer

Declaración de las variables



c1 varchar2(200);

c2 varchar2(200);

c3 varchar2(200);

Código en sí



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;

Ejemplo del código completo:



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;

[Oracle] Very simple way to recreate a table with contents between schemas

dirtyaffairs (Dirty Affairs) — Thu, 19 Apr 2007 11:20:55 GMT

User must have both schema permissions to execute this query



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

Change sessions, transaction and processes parameters in Oracle 10g

dirtyaffairs (Dirty Affairs) — Wed, 10 Jan 2007 16:23:22 GMT



alter system set PROCESSES=100 scope=SPFILE;

alter system set TRANSACTIONS=126 scope=SPFILE;

alter system set SESSIONS=115 scope=SPFILE;

Session and License information in Oracle

dirtyaffairs (Dirty Affairs) — Tue, 09 Jan 2007 15:55:23 GMT



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



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

Switch Oracle CLOB to VARCHAR2

dcmanges (Dan Manges) — Mon, 20 Nov 2006 08:23:21 GMT

This rake task creates a database script to change all Oracle CLOB columns to VARCHAR2 columns.

Changing Oracle CLOBs to VARCHAR2s can result in a huge performance increase, especially if the database and app servers are not close together on the network.



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

Adjust Database Sequences

dcmanges (Dan Manges) — Mon, 20 Nov 2006 07:22:13 GMT

If loading data through fixtures or other means with hard-coded IDs, database sequences may need to be adjusted. This is written for Oracle.



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