ODP.NET

Greetings!

I have a problem regarding calling stored procedure from Oracle database. The procedure takes one parameter: phone_number (varchar2) and returns two values: out_1(varchar2) and out_2(varchar2). The database is located on remote host and is managed by administrators I don't know, so I can't see any logs of the database. So when I launch my application - connectivity sets up well, but calling remote procedure always returns "null". I use Oracle ODT with ODAC(32bit) in my .Net application.

I've written a simple application in python that uses the stored procedure and it retrieves data correctly, so there id definitely no problem with database or stored procedure. Here is a C# code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{

string OracleConnectionString = "Data Source=(DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP)(HOST=<MyHostIP>)(PORT=1530)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<MyDatabaseName>)));User Id=MyLogin;Password=MyPassword";

OracleConnection OrCon = new OracleConnection(OracleConnectionString);

OracleCommand cmd = new OracleCommand("OREN_PHONE_TO_PORT", OrCon);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter phone_number = new OracleParameter();
phone_number.OracleDbType = OracleDbType.Varchar2;
phone_number.Direction = ParameterDirection.Input;
phone_number.Value = textBox1.Text;
cmd.Parameters.Add(phone_number);

OracleParameter out_1 = new OracleParameter();
out_1.OracleDbType = OracleDbType.Varchar2;
out_1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(out_1);

OracleParameter out_2 = new OracleParameter();
out_2.OracleDbType = OracleDbType.Varchar2;
out_2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(out_2);

try
{
OrCon.Open();
cmd.ExecuteNonQuery();
textBox2.Text = ((Oracle.DataAccess.Types.OracleString)cmd.Parameters[1].Value).ToString();
}
catch (OracleException ex)
{
Console.Write(ex.Message);
}

OrCon.Close();

// phone number example - 353xxxxxxx

}
}
}

In the end text "null" appears in textBox2.Text field. The same phone number in python application works fine and stored procedure returns correct data about it. Any help will be highly appreciated.

Edited by: 969927 on 06.11.2012 19:57