Search This Blog

Tuesday, September 17, 2013

PROCEDURE VS FUNCTION ORACLE

Differences between Stored Procedures and Functions

  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input/output parameters for it whereas functions can have only input parameters.
  • Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in afunction.
  • We can go for transaction management in procedure whereas we can't go in function.
  • Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

In depth

Stored Procedure 

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.
User defined functions have three main categories:
  1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
  2. Inline function - can contain a single SELECT statement.
  3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to re

Monday, September 16, 2013

CREATING CAB FILES IN .NET

Imports System
Imports System.IO
Imports System.Text
Imports Microsoft.Deployment.Compression.Cab
Public Class Test

    Public Shared Sub Main()
        Dim path As String = "c:\Newfolder\MyTest.txt"

        ' Delete the file if it exists.
        If File.Exists(path) Then
            File.Delete(path)
        End If

        'Create the file.
        Dim fs As FileStream = File.Create(path)

        AddText(fs, "This is some text")
        AddText(fs, "This is some more text,")
        AddText(fs, Environment.NewLine & "and this is on a new line")
        AddText(fs, Environment.NewLine & Environment.NewLine)
        AddText(fs, "The following is a subset of characters:" & Environment.NewLine)

        Dim i As Integer

        For i = 1 To 120
            AddText(fs, Convert.ToChar(i).ToString())

        Next

        fs.Close()

        Dim __cab As CabInfo
        Dim list As New List(Of String)
        list.Add("c:\Newfolder\MyTest.txt")
        __cab = New CabInfo("c:\Newfolder\cab1.cab")
        __cab.PackFiles(Nothing, list, Nothing)
        ''Open the stream and read it back.
        'fs = File.OpenRead(path)
        'Dim b(1024) As Byte
        'Dim temp As UTF8Encoding = New UTF8Encoding(True)

        'Do While fs.Read(b, 0, b.Length) > 0
        '    Console.WriteLine(temp.GetString(b))
        'Loop

        'fs.Close()
    End Sub

    Private Shared Sub AddText(ByVal fs As FileStream, ByVal value As String)
        Dim info As Byte() = New UTF8Encoding(True).GetBytes(value)
        fs.Write(info, 0, info.Length)
    End Sub
End Class