Hi,

I wich buy llblgen v3.5 if the present problem is resolved :

I use visual studio 2010 with oracle 10g database with ODP.NET drivers.

I create a new PACKAGE like this :

CREATE OR REPLACE PACKAGE EVEREST.My_app_package
 AS   
TYPE RCURSOR IS REF CURSOR; 
TYPE t_int_table IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; 
END My_app_package;

And I need to generate a call for my stored procedure :

CREATE OR REPLACE PROCEDURE GetUserPerimeter
(
userId  INT
,RC1 IN OUT My_app_package.RCURSOR
)
AS
BEGIN
OPEN RC1 FOR

select * from userclient
inner join ezperimeter on ezperimeter .clientId = userclient.clientId 

END GetUserPerimeter;

->Code generation produce bad generation : i wait something like

public Dataset getUserimeter(int userId)

and it generate :

public int getUserimeter(int userId)

I saw how to fix this problem if we use Adapter template group adding this piece of code :

using(OracleDataAdapter adapter = (OracleDataAdapter)CreateNewPhysicalDataAdapter())
                        {
                             for (int i = 0; i < parameters.Length; i++)
                             {
                                   if(parameters[i].OracleDbType == OracleDbType.RefCursor)
                                   {
                                         DataTable tableToFill = dataSetToFill.Tables.Add(parameters[i].ParameterName);
                                         adapter.Fill(tableToFill, (OracleRefCursor)parameters[i].Value);
                                   }
                             }
                        }

But The problem is that i don't want to do a code generation for Adapater template group but for SelfServicing template group (with .net 4.0 taget plateform and SD.Presets.SelfServicing.General2010.

Have you got a solution ?

In the catalog explorer, does the stored procedure have a resultset? if not, could you refresh the catalog please (right click the catalog in catalog explorer -> refresh relational model data from a database) and at the last page of the wizard, make sure you check the checkbox of the stored procedure 'GetUserPerimeter' so the resultset is determined?

If you did that, please check the application output window, whether an error was reported there.

I done that you sayed me and nothing did changed , i think that maybe my explanation was not good :

The problem is : a stored procedure whoses returns a cursor must be return dataset type , not an int type.

When a i do the generation , see the result :

/// <summary>Delegate definition for stored procedure 'GETUSERPERIMETER' to be used in combination of a UnitOfWork object.</summary>
        public delegate int GetuserperimeterCallBack(System.Decimal userid,  ITransaction transactionToUse);


        /// <summary>Calls stored procedure 'GETUSERPERIMETER'.<br/><br/></summary>
        /// <param name="userid">Input parameter of stored procedure. </param>
        /// <param name="rc1">InputOutput parameter of stored procedure. </param>
        /// <returns>Number of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
        public static int Getuserperimeter(System.Decimal userid)
        {
            return Getuserperimeter(userid, null);
        }

        /// <summary>Calls stored procedure 'GETUSERPERIMETER'.<br/><br/></summary>
        /// <param name="userid">Input parameter of stored procedure. </param>
        /// <param name="rc1">InputOutput parameter of stored procedure. </param>
        /// <param name="transactionToUse">the transaction to use, or null if no transaction is available.</param>
        /// <returns>Number of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
        public static int Getuserperimeter(System.Decimal userid,  ITransaction transactionToUse)
        {
            using(StoredProcedureCall call = CreateGetuserperimeterCall(new DataAccessCoreImpl(new CommonDaoBase(), transactionToUse), userid))
            {
                int toReturn = call.Call();
                return toReturn;
            }
        }

        /// <summary>Creates the call object for the call 'Getuserperimeter' to stored procedure 'GETUSERPERIMETER'.</summary>
        /// <param name="dataAccessProvider">The data access provider.</param>
        /// <param name="userid">Input parameter</param>
        /// <returns>Ready to use StoredProcedureCall object</returns>
        private static StoredProcedureCall CreateGetuserperimeterCall(IDataAccessCore dataAccessProvider, System.Decimal userid)
        {
            object rc1 = null;
            return new StoredProcedureCall(dataAccessProvider, "\"EVEREST\".\"GETUSERPERIMETER\"", "Getuserperimeter")
                            .AddParameter("USERID", "Decimal", 0, ParameterDirection.Input, true, 38, 0, userid)
                            .AddParameter("RC1", "RefCursor", 0, ParameterDirection.InputOutput, true, 0, 0, rc1);
        }

I believe that the problem is in procedure parameters and parameters mappings are not configured correcly see these pictures of my copies screen :

parameters : https://plus.google.com/u/0/photos/111270068714936282413/albums/5854468998992848145/5854469006304107026?authkey=CIC-w7Pj0KCLngE

parameter mapping : https://plus.google.com/u/0/photos/111270068714936282413/albums/5854468998992848145/5854469002826313426?authkey=CIC-w7Pj0KCLngE

--> i think that the mapping parameters must return a dataset maybe , but in v3.5 trial , it's impossible to modify it.

Can you confirm me this ?

It sees the stored procedure as an action procedure, not as a procedure which returns a resultset. This is strange as the procedure should be picked up as such as it returns a cursor (and if you checked the checkbox in the last page of the wizard, it will retrieve the resultset).

It might be it ran into an error doing so, which should be reported in the application output window docked at the bottom (after the refresh). If it does that, it flags the proc as an action proc. Could you check that for me, please?

You can fix this manually though: right-click the stored procedure in catalog explorer and select 'set the number of resultsets' -> 1

then save and regenerate. This should add the procedure to the RetrievalProcedures class and return a datatable (as the proc has 1 cursor)

Good ! It works !

I do a rapid summury :

For each stored procedures which return a cursor , -> select your procedure stored name in Catalog Explorer and click right > Se tNumber Of Resultset > 1 to retrieve a datatable and 2 or more to retrieve a dataset.

Thanks

davidz wrote:

Good ! It works !

I do a rapid summury :

For each stored procedures which return a cursor , -> select your procedure stored name in Catalog Explorer and click right > Se tNumber Of Resultset > 1 to retrieve a datatable and 2 or more to retrieve a dataset.

Thanks

Well, if you check the checkbox on the last page of the the relational model data retrieval wizard for every proc which returns a cursor, this isn't necessary. It will retrieve the resultset format in that case.

However if an error occurs, or a field in the resultset has an unsupported type, the resultset is marked as not retrievable and the proc will have 0 resultsets. You can see that in the catalog explorer, it will have (0) as suffix after its name.

You can select multiple procs at once though. Select them all, then right-click -> set resultset to 1 or 2.

It's essential to check the application output window though as it will give clues why it couldn't retrieve the resultset for a procedure.

Hope this helps.