I have built the database using Oracle 10g XE.
Below are the code that I have applied in Oracle:
create or replace PACKAGE "GET_EMPLOYEES" AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR);
END GET_EMPLOYEES;
create or replace PACKAGE BODY "GET_EMPLOYEES" AS
PROCEDURE GetEmployees (cur_Employees OUT T_CURSOR)
IS
BEGIN
OPEN cur_Employees FOR
SELECT * FROM EMPLOYEES;
END GetEmployees;
END GET_EMPLOYEES;
Below are the code that I have implemented:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Threading;
namespace HR_Mobile_Conn_CS
{
public partial class Form1 : Form
{
private CoreLab.Oracle.OracleConnection connection = new CoreLab.Oracle.OracleConnection();
// private CoreLab.Oracle.OracleCommand command = new CoreLab.Oracle.OracleCommand();
public Form1()
{
InitializeComponent();
lblResult.Text = "";
}
private void btnConnect_Click(object sender, EventArgs e)
{
connection.ConnectionString = "";
// user id=hr;password=hr;data source=hr-server:1521/XE
connection.UserId = txtUserID.Text;
connection.Password = txtPassword.Text;
connection.Port = Convert.ToInt32(txtPort.Text);
connection.Server = txtHost.Text;
connection.Sid = txtDatabase.Text;
try
{
// Stored Procedure for Select
CoreLab.Oracle.OracleCommand command = new CoreLab.Oracle.OracleCommand("GET_EMPLOYEES.GetEmployees", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("cur_Employees", CoreLab.Oracle.OracleDbType.Cursor);
connection.Open();
command.ExecuteNonQuery();
CoreLab.Oracle.OracleCursor cur = (CoreLab.Oracle.OracleCursor)command.Parameters["cur_Employees"].Value;
CoreLab.Oracle.OracleDataReader dr = cur.GetDataReader();
int i = 0;
while ((dr.Read()) && (i < 5))
{
lblResult.Text = dr["EMPLOYEE_ID"] + "; " + dr["FIRST_NAME"] + "; " + dr["LAST_NAME"];
Thread.Sleep(300);
}
connection.Close();
lblResult.Text = "Completed";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnExit_Click(object sender, EventArgs e)
{
connection.Close();
this.Close();
}
}
}
I have got the error of "Null pointer exeception" while run the step "CoreLab.Oracle.OracleDataReader dr = cur.GetDataReader();", are there any comment for me?
Problem to use Stored Procedure on OraDirect .Net Mobile
-
- Posts: 729
- Joined: Thu 13 Dec 2007 10:24
Try to add this line before opening a connection:
If the parameter direction isn't set to "Output", and execution of the associated command does return a value, the DbParameterBase will contain a null value.
Code: Select all
command.Parameters["cur_Employees"].Direction = ParameterDirection.Output;