Stored Procedures support and Oracle

[CarlosLima]

I've been doing a lot of tests to ORM using Oracle (11) and the class mappings work fine, the linq queries obviously also but the stored procedures have a lot of problems (or I haven't yet figured out if I'm doing something wrong)

First, when I use the tool to create the CUD Mappings and I set to use stored procedures, it doesn't list the existing "imported" stored procedures. Ok maybe it has to create it's own, so I set it to create a new stored procedure for the insert for example. The result seams fine but the query it uses to create the SP on the DB is wrong for Oracle (ex: RowsAffected := SQL%ROWCOUNT ; without creating the variable first)
So i tried to create my own insert SP and use it on the application using:

dbContext.ExecuteStoredProcedure<object>("SP_APP_MENU_INS", sqlParameters, "aaa", 10, "bb");   

and it returns a null exception (the fields are fine)

System.NullReferenceException: Object reference not set to an instance of an object.
   at OpenAccessRuntime.Relational.RelationalQueryResult.set_QResult(QueryResultContainer value)
   at OpenAccessRuntime.Relational.RelationalQueryResult.prepare(QueryResultContainer qContainer)
   at OpenAccessRuntime.Relational.RelationalQueryResult.nextBatch(ApplicationContext context, Int32 skipAmount, QueryResultContainer qContainer)
   at OpenAccessRuntime.Relational.RelationalStorageManager.fetchNextQueryResult(ApplicationContext context, RunningQuery runningQuery, Int32 skipAmount)

even using:  dbContext.SPAPPMENUINS("aaa", 12, "bb"); same  error

and the SP is:

CREATE OR REPLACE PROCEDURE SP_APP_MENU_INS(oMENU_ID        IN NVARCHAR2,
                                            oCONTEXT_TAG_ID IN NVARCHAR2 DEFAULT NULL,
                                            oDISPLAY_ORDER  IN NUMBER DEFAULT NULL) IS
  RowsAffected Number;
BEGIN
  INSERT INTO APP_MENU
    (MENU_ID, CONTEXT_TAG_ID, DISPLAY_ORDER)
  VALUES
    (oMENU_ID, oCONTEXT_TAG_ID, oDISPLAY_ORDER);
  RowsAffected := SQL%ROWCOUNT;
END;

So I'm not sure if it supports everything or I am doing something wrong.

Another thing, I have a SP that returns a cursor  (it's a select from various tables), does ORM support this also? Please see my previous post (Where is 'Enable project to use ORM'?)

[Thomas]

Hi Carlos,

I've just verified that the SQL for an insert SP is generated correctly. Take an example from out test suite:

create or replace PROCEDURE "sp__ns_s_p_class_with_auto_inc"
( another_id IN number DEFAULT NULL, s_val IN varchar2 DEFAULT NULL, voa_version IN number, RowsAffected OUT number, AutoIncValue OUT number )
IS BEGIN
INSERT INTO "s_p_class_with_auto_inc" ( "another_id" , "s_val" , "voa_version" ) VALUES ( another_id , s_val , voa_version )
       RETURNING "id" INTO AutoIncValue; RowsAffected := SQL%ROWCOUNT ;
END;

IS BEGIN 

INSERT INTO "s_p_class_with_auto_inc" ( "another_id" , "s_val" , "voa_version" ) VALUES ( another_id , s_val , voa_version ) 

       RETURNING "id" INTO AutoIncValue; RowsAffected := SQL%ROWCOUNT ; 

E
N

As you can see, the RowsAffected and the AutoIncValue are out parameters, and we do not expect return values for them. The other SP (update/delete) follow similiar conventions.
Can you please check/retry your SP with such 'calling conventions' as well? OpenAccess just needs the declaration of the SP in order to call that SP instead of generating the INSERT statement dynamically.

Generally, creating the SP from the mapping should work out of the box, but wiring the OpenAccess runtime up to accept predefined SP is a bit more work. In any case, SP that should be called for CUD operations will need to follow certain rules (all fields present, out parameters used) as the runtime will be the invoker and hence must restrict the signature. On the other hand, using SP for read operations must be done explicitly by the application through the ExecuteStoredProcedure method.

All the best,

Thomas
the Telerik team

[CarlosLima]

I have uninstalled Telerik ORM and decided to create my own. Anyway, altough I don't have it here anymore, I'm pretty sure that the SP that your ORM created didn't had the RowsAffected as an out parameter only as part of the sql code (probably ommited for some reason)