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...

Saturday, July 27, 2013

Oracle Tip : Removing all the duplicates from a table

Problem : Problem of Duplicates in a table

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


Steps :

  • Create a backup of the table for which you are removing the duplicates 
  • Identify the no. of duplicates
  • Run the statement given below
    • TABLE1 - replace with your table
    • FIELD1,FIELD2 - Group By fields from your table 


    DELETE FROM TABLE1
  1.       WHERE ROWID IN (
  2.                SELECT row_id
  3.                  FROM (SELECT ROWID row_id,
  4.                               ROW_NUMBER () OVER (PARTITION BY FIELD1,FIELD2 ORDER BY ROWID) ROWNO
  5.                          FROM TABLE1)
  6.                 WHERE ROWNO<> 1);
  7.                 
read more...

Monday, July 22, 2013

Excel Tip : Extract URL's from Hyperlink list

Problem : Extracting URLs from a long list of hyperlinks.

Solution : Using VBA module

read more...