Return an Array from Function

tvr · Post by **tvr** » Fri 22 Sep 2006 22:15

My company is currently looking for a third party software to connect from our Handheld Devices to our Oracle Database. We used to go through the Web Service, but that is no longer available to us.

So, we are trying to call an Oracle Database Function from our Handheld Device in order to imitate what we had and avoid recoding. The function we are calling returns an array. We have run your code and been able to connect to the database and get a return value from our function, but we are unable to return an array. We are working with Visual Studio .NET 2003, Compact Framework in C#, and we downloaded a demo version of the OraDirect .NET Mobile last week. Here is the code we have so far. The last line is where the failure occurs.

OracleConnection myConn = new OracleConnection("User Id=user;Password=password;Port=1521;Server=999.999.999.99;Sid=test");
myConn.Open();
OracleCommand getFunc = new OracleCommand("K_ADCI_WS.F_VALIDATE_USER",myConn);

getFunc.CommandType = System.Data.CommandType.StoredProcedure;
//Prepare parameters - input parameters
OracleParameter pUser = getFunc.Parameters.Add("pvar_oracle_id", "XXXXXXX");
sHashPassword = GetHash("XXXXXXX", "temppass");
OracleParameter pHashPass = getFunc.Parameters.Add("pvar_hashed_password_id", Convert.FromBase64String(sHashPassword));
OracleParameter pVersion = getFunc.Parameters.Add("pvar_hh_version", sVersion);
//output parameter
OracleParameter pUserTable = new OracleParameter();
pUserTable.ParameterName = ":t";
pUserTable.OracleDbType = OracleDbType.Array;
pUserTable.ObjectTypeName = "ttbl_users";
pUserTable.Direction = System.Data.ParameterDirection.Output;
getFunc.Parameters.Add(pUserTable);

getFunc.ExecuteNonQuery();

Thanks for any help!

Alexey · Post by **Alexey** » Mon 25 Sep 2006 05:59

Please provide us with you database objects definitions.

tvr · Post by **tvr** » Mon 25 Sep 2006 16:41

Below is what's defined in the function:

FUNCTION f_get_users (
pvar_oracle_id VARCHAR2,
pvar_hashed_password_id RAW,
pvar_hh_version VARCHAR2
)
RETURN ttbl_users

Within the code it defines ltbl_users as
ltbl_users := ttbl_users ();

then it returns ltbl_users at the end.

Thanks!

Alexey · Post by **Alexey** » Tue 26 Sep 2006 10:51

Judging from your code, ttbl_users is VARRAY. Mobile version does not support object data types. For more information see direct mode restrictions in OraDirect .NET help documentation.

Alexey · Post by **Alexey** » Tue 26 Sep 2006 11:19

We can suggest you using REFCURSOR parameter and work via OracleDataReader.