Stored Procedure Arguments for Oracle

Aug 6, 2014 at 6:54 AM
Edited Aug 6, 2014 at 6:54 AM
Hi there,

Not sure if this is issue of this lib or perhaps issue of the connection string, but arguments for stored procedures are not filled in. As I figured out after debugging the problem in restrictions for Arguments schema. When I set the first one (owner) to the null -- then it returns all the arguments, but further parsing is slow, so here is workaround I have implemented for a while to make it work. If anyone advice how I can make it work without patching I will be appreciated. Also I can send a patch file if needed.

If it is matter I use Oracle.ManagedDataAccess.Client provider

file: .\dbschemareader\databaseschemareader\schemareader.cs
line: 634
        protected virtual DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection)
        {
            //different collections here- we could just if(IsOracle)
            string collectionName = ProcedureParametersCollectionName;
            if (!SchemaCollectionExists(connection, collectionName)) collectionName = "Arguments";
            if (ProviderType == SqlType.MySql) collectionName = "Procedure Parameters";
            else if (ProviderType == SqlType.Oracle) collectionName = "Arguments"; //Oracle, assume packages
            if (!SchemaCollectionExists(connection, collectionName)) return CreateDataTable(ProcedureParametersCollectionName);

            DataTable dt;
            string[] restrictions = SchemaRestrictions.ForRoutine(connection, collectionName, storedProcedureName);

            if (ProviderType == SqlType.Oracle)
            {
                dt = connection.GetSchema(collectionName).Select("OWNER = '" + restrictions[0] + "'").CopyToDataTable();
            }
            else
            {
                dt = connection.GetSchema(collectionName, restrictions);
            }            

            dt.TableName = ProcedureParametersCollectionName;
            return dt;
        }
Aug 6, 2014 at 11:22 AM
They work here, using the same provider. I am using Oracle 11g and I have a couple of test sps such as this:

CREATE PROCEDURE ADDITEMTOORDER(
OrderId NUMBER,
ItemId NUMBER,
Quantity NUMBER)
AS
BEGIN
select NULL from dual;
END;
/
Coordinator
Aug 6, 2014 at 12:49 PM
Yep, it's working for me too... Oracle.ManagedDataAccess, Version=4.121.1.0 plus latest versions of Devart.Data.Oracle, System.Data.OracleClient and Oracle.DataAccess.Client.

restrictions[0] is the owner, and passing that into the GetSchema should limit the results to that owner.
Your fix doesn't use the restrictions, so it returns a huge datatable (35000 rows in my XE instance, probably millions in a "real" db) and then filters down those rows.

One possible reason why does it not work within GetSchema, but it works in .net code, is that Oracle is case sensitive.
So Owner = "HR" isn't the same as Owner = "hr" or Owner = "Hr" ...

If you get the case wrong, you'll get an empty datatable.
Aug 6, 2014 at 1:18 PM
Yes. I understand that this is not the optimal solution. I have played with restrictions (Upcase etc) and for some reason schema request for "Arguments" returns no rows if at least one is applied.
Coordinator
Aug 6, 2014 at 1:30 PM
Most of the schema objects use the same restrictions mechanism, so they should work in the same way.
Perhaps you have an older version of the client with a bug in GetSchema?