GetSchema data does not provide function return type

GetSchema data does not provide function return type

Postby silverfox » Fri 17 Feb 2012 11:09

Hello,

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'


This query will never return the result type of my function. (because of ARGUMENT_NAME LIKE ARGUMENT_NAME)

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(";");
            }
        }


Thanks in advance for your help.
silverfox
 
Posts: 10
Joined: Wed 18 Jan 2012 10:58
Location: Belgium

Postby Pinturiccio » Mon 20 Feb 2012 12:29

We have reproduced the issue. We will investigate it and notify you about results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44

Postby Pinturiccio » Mon 27 Feb 2012 13:09

We have fixed the bug with Arguments metadata table, that does not contain a row for return value of a function.
The new build of dotConnect for Oracle 6.70.311 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23469
Pinturiccio
Devart Team
 
Posts: 1953
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle