BLToolkit Oracle SP support

Question

Does BLT support Oracle stored procedures? I've tried numerous methods, described below to get it to work but no luck. The stored procedure updates a table with several values. This is the stored procedure, a small test procedure.

DROP PROCEDURE BETA_AUTO_UPDATE;
/

 CREATE OR REPLACE PROCEDURE BETA_AUTO_UPDATE
   (
      AutoId IN NUMBER,
      Rule  IN NVARCHAR2,  
      Nam  IN NVARCHAR2,
      Loc IN NVARCHAR2
   )
  IS
  BEGIN 

UPDATE Beta_Auto
SET RuleGuid = Rule,        
    Name = Nam,
    Location = Loc
WHERE Id=AutoId;

  EXCEPTION 
     WHEN OTHERS THEN 
         RAISE_APPLICATION_ERROR(-20001, 'ERROR OCCURED DURING UPDATE');


   END BETA_AUTO_UPDATE;
   /

Tried the following

        DbManager.AddDataProvider(new OdpDataProvider());
        DbManager OracleDb = new DbManager("BetaOracleDBConn");

        Beta_Auto Betar = new Beta_Auto();
        Betar.ID = 1;
        Betar.Name = "Jim";
        Betar.RuleGuid = "jlDKDKDKDKDKDKp";
        Betar.Location = "LocDLDLDLDLDtor";

        OracleDb.SetSpCommand("Beta_Auto_UPDATE",       
        OracleDb.CreateParameters(Betar)).ExecuteNonQuery();

That didn't work.

Tried this

        [ActionName("UPDATE")]
        public abstract void Update(Beta_Auto Auto);

That didn't work.

Tried this:

        [SprocName("Beta_Auto_Update")]
        public abstract void UpdateByParam(
        [Direction.InputOutput("ID", "RuleGuid", "Name", "Location")] Beta_Auto Auto);

That didn't work.

          [SprocName("Beta_Auto_Update")]
          public abstract void UpdateByParam(int Id, string RuleGuid, string Name,  string Location);

Also tried this:

[ActionName("Update")]
public abstract void UpdateByParam(int Id, string RuleGuid, string Name, string Location);

That didn't work.

Set the trace level on odp.net to 7. Saw that the call was being made, but couldn't see any parameters. Swapped out XE (thought it might have been a licensing problem as db was bigger that 5GB) for enterprise Oracle. Didn't work.

Create a new user, datafile, tablespace, and assigned all roles and privs, including Execute Any Procedure to the user. Didn't work.

I ran a standard ado.net (very long winded) to call the stored procedure via OracleCommand and it called perfectly and did the update.

I am stumped. All of the above work for SQL Server.

Thanks. scope_creep

score 4 · Accepted Answer · answered Aug 01 '11 at 09:31

4

I'm doing it like

        var parameters = OracleDb.GetSpParameters("BETA_AUTO_UPDATE", true, true);

        parameters.SetParamValue("pParam1", param1);
        parameters.SetParamValue("pParam2", param2);
        ...

        OracleDb.SetSpCommand("BETA_AUTO_UPDATE", parameters).ExecuteNonQuery();

It's an extra round-trip, but since I'm only using stored procedures for a couple of big batch updates it doesn't really matter, (normal/simple updates are done with Linq DML operations)

answered Aug 01 '11 at 09:31

David DV

674
4
9

Don't fully understand your question GetSpParameters -> call to DB to get the parameters collection of the stored procedure SetSpCommand(...).ExecuteNonQuery() -> call to DB which sets the parameters collection and executes the stored procedure on the site -> [link](http://www.bltoolkit.net/Doc.SetSpCommand.ashx?HL=setspcommand) You can also use it to check if the paremeters collection you are creating is the correct one – David DV Aug 01 '11 at 16:25
I used the OracleDb.SetSpCommand("Beta_Auto_UPDATE", OracleDb.CreateParameters(Betar)).ExecuteNonQuery(); – scope_creep Aug 02 '11 at 14:28
I was wondering, did you ever use the functionality found here, http://stackoverflow.com/questions/4880884/bltoolkit-multiple-resultsets with Oracle. It couldn't get it to work with Oracle but again worked flawlessley with Sql Server. Any ideas. – scope_creep Aug 02 '11 at 14:53
what I'm asking, did you manage to get sp's returning cursors? Bob. – scope_creep Aug 03 '11 at 13:48

BLToolkit Oracle SP support

1 Answers1