Stored Procedures support and Oracle

3 posts, 0 answers
  1. CarlosLima
    CarlosLima avatar
    47 posts
    Member since:
    May 2010

    Posted 29 Sep 2010 Link to this post

    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:

                                                oCONTEXT_TAG_ID IN NVARCHAR2 DEFAULT NULL,
                                                oDISPLAY_ORDER  IN NUMBER DEFAULT NULL) IS
      RowsAffected Number;
      RowsAffected := SQL%ROWCOUNT;

    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'?)
  2. Thomas
    Thomas avatar
    588 posts

    Posted 07 Oct 2010 Link to this post

    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 )
    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 ;

    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 ; 
    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,

    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. CarlosLima
    CarlosLima avatar
    47 posts
    Member since:
    May 2010

    Posted 07 Oct 2010 Link to this post

    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)
Back to Top