Problem to use Stored Procedure on OraDirect .Net Mobile

Problem to use Stored Procedure on OraDirect .Net Mobile

Postby kk » Wed 19 Dec 2007 11:53

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?
kk
 
Posts: 5
Joined: Tue 20 Nov 2007 10:37

Postby Alexey.mdr » Thu 20 Dec 2007 10:06

Try to add this line before opening a connection:

Code: Select all
command.Parameters["cur_Employees"].Direction = ParameterDirection.Output;


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.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for Oracle