I'm trying to retrieve some metadata about an oracle function in a package and I do not know how to retrieve the function return type.
In the oracle All_Arguments view, function result type does not have a argument_name. The devart generated query is :
Code: Select all
SELECT OWNER AS "Schema", PACKAGE_NAME AS "Package", OBJECT_NAME AS "Procedure", OVERLOAD AS "Overload", ARGUMENT_NAME AS "Name", POSITION AS "Position", SEQUENCE AS "Sequence", DATA_TYPE AS "DataType", TYPE_OWNER AS "TypeSchema", TYPE_NAME AS "TypeName", TYPE_SUBNAME AS "SubType", IN_OUT AS "Direction", DATA_LENGTH AS "Length", DATA_PRECISION AS "Precision", DATA_SCALE AS "Scale", CHARACTER_SET_NAME AS "Charset", DEFAULT_VALUE AS "DefaultValue", DEFAULT_LENGTH AS "DefaultLength" FROM SYS.ALL_ARGUMENTS WHERE OWNER= 'SODEXO' AND OBJECT_NAME = 'FUNCTIONWITHRECORD' AND ARGUMENT_NAME LIKE ARGUMENT_NAME AND DATA_LEVEL = 0AND PACKAGE_NAME = 'PCK_PACKAGE'
Can you please tell me how to retrieve the function return type through the Connection.GetSchema function ? The OracleCommand.ParameterCheck = true + OracleCommand.Prepare() is able to define the return type (Parameter RESULT), I suppose I did not use the correct way to find my function return type.
Here is how you can reproduce my problem:
Oracle DDL
Code: Select all
Create or Replace Package pck_package As
Type Typ_ReimbursableVchrsList Is Record
(
Id_Order Number,
Id_Product Number,
Id_IssuePeriod Number,
CustomerEndValidity Date
);
Function functionWithRecord
(
pi_IdProduct In Number,
pi_IdAuthorisation In Number,
pi_IdUser In Varchar2,
Pi_IsIntranetUser In Boolean,
pi_VoucherList In Typ_ReimbursableVchrsList
)
Return Number;
End pck_package;
/
Create or Replace Package Body pck_package As
Function functionWithRecord
(
pi_IdProduct In Number,
pi_IdAuthorisation In Number,
pi_IdUser In Varchar2,
Pi_IsIntranetUser In Boolean,
pi_VoucherList In Typ_ReimbursableVchrsList
)
Return Number
Is
Begin
Return 1;
End functionWithRecord;
End pck_package;
/
And here is my .Net test code
Code: Select all
[TestMethod]
public void GetArgOfAFunctionInPckTest()
{
OracleMonitor mon = new OracleMonitor();
mon.UseApp = true;
mon.IsActive = true;
OracleConnection Connection = DirectConnection;
string currentUserName = Connection.UserId.ToUpper();
var table = Connection.GetSchema("Arguments", new string[] { currentUserName, "PCK_PACKAGE", "FUNCTIONWITHRECORD", "" });
//header
foreach (DataColumn col in table.Columns)
{
Debug.Write(col.ColumnName);
Debug.Write("(" + col.DataType.FullName + ")");
Debug.Write(" - ");
}
Debug.WriteLine(";");
//data
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Debug.Write(row[col]);
Debug.Write(" - ");
}
Debug.WriteLine(";");
}
var argstable = Connection.GetSchema("Arguments", new string[] { currentUserName, "PCK_PACKAGE", "FUNCTIONWITHRECORD" });
//header
foreach (DataColumn col in argstable.Columns)
{
Debug.Write(col.ColumnName);
Debug.Write("(" + col.DataType.FullName + ")");
Debug.Write(" - ");
}
Debug.WriteLine(";");
//data
foreach (DataRow row in argstable.Rows)
{
foreach (DataColumn col in argstable.Columns)
{
Debug.Write(row[col]);
Debug.Write(" - ");
}
Debug.WriteLine(";");
}
}