Latest Posts

Sunday, March 30, 2014

Oracle Tip: Recover Deleted Rows from a Table


Problem : Recover deleted rows from a table

Solution : Using timestamp you can recover the deleted data form a table. You can select the data and store it in another table for finding the missing data.



Select * from table1 as of timestamp(to_timestamp('2014-03-29 11:00:00','yyyy-mm-dd hh:mi:ss'))

read more...

Thursday, August 1, 2013

ASP.NET Tip : Reading and Writing CLOB Datatype column in Oracle

Problem : Read & Write CLOB Dataype Column in Oracle

Solution : Using Oracle Data Provider (ODP.NET)


Steps :

  • Install ODP.NET and use the below code


    Imports Oracle.DataAccess.Client
    Imports Oracle.DataAccess.Types

    ' Reading CloB Data

    Public Sub ReadLOBData()


    Dim con As New OracleConnection(connectionstring)

    con.Open()

    Dim sql As String = "Select clb_field from Table1 where Id= 1"


    Dim cmd As OracleCommand = New OracleCommand(sql, con)
    Dim dr As OracleDataReader = cmd.ExecuteReader()
    dr.Read()

    Dim blob As OracleClob = dr.GetOracleClob(0)
    TextBox1.Text = blob.Value

    blob.Close()
    dr.Close()
    con.Close()


    End Sub


    ' Writing CLOB Data

    Public Sub WriteLOBData()


    Dim connection As New OracleConnection(connectionstring)

    connection.Open()

    Dim strSQL As String = "INSERT INTO Table1 (ID,clb_field) VALUES (1 , :TEXT_DATA) "


    Dim paramData As New OracleParameter

    paramData.Direction = ParameterDirection.Input
    paramData.OracleDbType = OracleDbType.Clob
    paramData.ParameterName = "TEXT_DATA"

    paramData.Value = TextBox1.Text

    Dim cmd As New OracleCommand
    cmd.Connection = connection
    cmd.Parameters.Add(paramData)
    cmd.CommandText = strSQL
    cmd.ExecuteNonQuery()

    paramData = Nothing
    cmd = Nothing
    connection.Close()


    End Sub




  1.                 
read more...

Wednesday, July 31, 2013

Oracle Tip: Find Missing Sequence No from a Table

Problem : Finding Missing Sequence No. from a Table

Solution : Using Analytics is the best solution. For this problem I have used LAG()


Steps :

  • Run the statement given below
    • TABLE1 - Table for which you need to find the missing seqno.
    • seqno- Sequence no field


    SELECT last_expected_seq as MissingSeq
    FROM (
      SELECT seqno,
             seqno- 1 as last_expected_seq,
             lag(seqno) over (order by seqno) as last_sequence
      FROM TABLE1
    ) t
    WHERE last_expected_seq <> last_sequence;


  1.                 
read more...