How to use cursor in oracle stored procedure

How to use cursor in oracle stored procedure

create or replace PROCEDURE sp_myprocedure(p_1 IN t1.c1%Type,
p_2 IN t1.c2%Type,
p_3 IN t3.c3%Type)

AS

BEGIN
DECLARE

v_myvariable char(7);

CURSOR crs_my_cursor IS
select * from mytable1;
BEGIN

IF (NOT crs_my_cursor%ISOPEN) THEN -- IF 1
OPEN crs_my_cursor;
END IF;

FETCH crs_my_cursor into v_my_variable;

WHILE (crs_my_cursor%FOUND) LOOP
BEGIN -- begin 2
null;
----START WORKING YOUR CODE HERE..
END;

FETCH crs_my_cursor into v_my_variable;

END LOOP;

CLOSE crs_my_cursor;
EXCEPTION
when others then

rollback;
END;

END sp_myprocedure;

[Posted with iBlogger from my iPhone]

How to add jQuery Intellisense in VS 2008

create filder Scripts in your project download both files from jquery.com

add this in your masterpage head section:

<script src="/Script/jquery-1.3.2.js" type="text/javascript"></script>
<script src="Script/jquery-1.3.2-vsdoc.js" type="text/javascript"></script>

<script language ="javascript" type="text/javascript">
$.getJSON(…this should work now..
</script>

Note that i have /Script/jquery-1.3.2.js in the first one and
second vsdoc.js has only Script/jquery-1.3.2-vsdoc.js

Note: don’t forget to add ///
in your /Script/jquery-1.3.2.js

Hope this helps!

CSS Quick Tip – If something doesn’t look same in IE and Firefox then try this

 

Type in your CSS where you have that div or css style just type this:

clear: both;

and refresh your page. It should fix your overlap and in consistency in your css look.

Add new value in .net dropdownlist control using jQuery

 

Here is your jQuery code:

// first lets un-select any items that have been selected 
$("select.ddlMyDropDown option:selected").removeAttr("selected");
var addvalue = ‘MyNewValue’;

$("select.ddlMyDropDown").prepend('<option selected="selected" value="' + addvalue + '">' + addvalue + '</option>');

Here is your html code:

<asp:DropDownList runat="server" ID="ddlMyDropDown" CssClass="ddlMyDropDown" ></asp:DropDownList> 
<br />
<input type="text" id="addToDropDown" class="addToDropDown" visible ="false" size="1" /> 

Nice!

How to remove Team Foundation Server old source control setting from your solution

I had to change my team foundation server and the new one had different name and url.

All of my old project was not able to load back into my new TFS. So I come across this setting in :

C:\Users\loggedinusername\AppData\Local\Microsoft\Team Foundation\2.0\Cache .. make sure that loggedinusername is your user name..

look for VersionControl.config file

open in Visual Studio and make your change here under : <VersionControlServer>

-Adnan

How to use Oracle CHANGE NOTIFICATION Callback in .net application

How to use CHANGE NOTIFICATION Callback in .net application

1. You must give Grant permission on to your tables as shown below

grant change notification to database name

2. You have to registered your select query into database using your .net application as show below

below is code to register query

Public Sub ChangeNotification()
Try
Dim sql As String = "select firstname, lastname from person”

Dim constr As String = My.Settings.sConnectionString
Dim con As New OracleConnection(constr)

con.Open()

Dim cmd As New OracleCommand(sql, con)
Dim dep As New OracleDependency(cmd)
AddHandler dep.OnChange, AddressOf OnDatabaseNotification

cmd.ExecuteNonQuery()

While notificationReceived = False

Console.WriteLine("Waiting for notification...")
System.Threading.Thread.Sleep(2000)
End While

cmd.Dispose()
con.Dispose()

Console.WriteLine("Press ENTER to continue...")
Console.ReadLine()

Catch ex As Exception
Throw ex
End Try
End Sub

below is code to when data is updated or changed oracle database calls this event in your application

Public Sub OnDatabaseNotification(ByVal src As Object, ByVal args As OracleNotificationEventArgs)
Try
Console.WriteLine("Database Change Notification received!")
Dim changeDetails As DataTable = args.Details
Console.WriteLine("Resource {0} has changed.", changeDetails.Rows(0)("ResourceName"))

notificationReceived = True

Catch ex As Exception

Throw ex
End Try
End Sub

3. run this application and change/update your table information see this event called from oracle database

Enjoy !

Simple Update in Oracle Table Using Transaction from ASP.NET

 

First Create Simple Update Procedure in your oracle databae

------------------------------------------------------------------

create or replace

PROCEDURE SP_UPD_MYPROC(myid Nvarchar2, mytime TimeStamp) AS BEGIN

/* UPDATE */

Update mytable s set my_datetimefield =mytime where s.id = id; commit; END SP_UPD_MYPROC;

This is how you will call from your asp.net application using transaction

-------------------------------------------------------------------------------------------------------

Make sure you imports these libraries

Imports Oracle.DataAccess.Client

Imports Oracle.DataAccess.Types

Public Function UpdateMyTable(ByVal myId as string)

Dim txn As OracleTransaction = Nothing

Try

Using conn As OracleConnection = New

OracleConnection(My.Settings.sConnectionString)

conn.Open()

txn = conn.BeginTransaction()

Dim cmdTyProcessTbl As OracleCommand = New OracleCommand("", conn)

cmdTyProcessTbl.CommandText = "SP_UPD_MYPROC"

cmdTyProcessTbl.CommandType = Data.CommandType.StoredProcedure

Dim prm As OracleParameter = New OracleParameter("a", OracleDbType.NVarchar2)

prm.Direction = Data.ParameterDirection.Input

prm.Value = myId

cmdTyProcessTbl.Parameters.Add(prm)

prm = New OracleParameter("b", OracleDbType.TimeStamp)

prm.Direction = Data.ParameterDirection.Input

prm.Value = Now()

cmdTyProcessTbl.Parameters.Add(prm)

cmdTyProcessTbl.ExecuteNonQuery()

cmdTyProcessTbl.Dispose()

txn.Commit()

conn.Close()

conn.Dispose()

'file.Dispose()

txn.Dispose()

GC.Collect()

End Using

Catch ex As Exception

Throw ex.Message.ToString

Finally

GC.Collect()

End Try

End Function

That’s All !