Creating StoredProcedure Model

[arv]

It seems, if raise_application_error method used in a stored procedure,
devart linqConnectModel cannot create correct function of storedprocedure in model class.

Can you please offer a solution ?

Thanks.

Here is SP example and method in linq model :

Code: Select all

CREATE OR REPLACE testProc
(
  v_Node IN VARCHAR2 DEFAULT NULL ,
  v_Sensor IN NUMBER DEFAULT NULL ,
  v_Duration IN NUMBER DEFAULT NULL ,
  v_Username IN VARCHAR2 DEFAULT NULL ,
  v_ProgramState IN NUMBER DEFAULT NULL ,
  cv_1 OUT SYS_REFCURSOR
)
AS
   iv_Duration NUMBER(10,0) := v_Duration;
   v_ErrorStr VARCHAR2(300 CHAR);
   v_ProgramTemperaturePeriodic NUMBER(1,0);
   v_temp NUMBER(1,0) := 0;

BEGIN

   IF v_Username IS NULL
     OR v_Username = N''
     OR v_Node IS NULL
     OR v_Node = N''
     OR v_Sensor IS NULL
     OR v_ProgramState IS NULL
     OR ( v_ProgramState <> 3
     AND v_ProgramState <> 4 ) THEN
   BEGIN
      raise_application_error( -20002, N'Invalid parameter' );
      RETURN;
   END;
   END IF;

Code: Select all

/// <summary>
/// There are no comments for TESTPROC in the schema.
/// </summary>

[Function(Name=@"testProc")]
public void TESTPROC([Parameter(Name="V_NODE", DbType="VARCHAR2(32766 CHAR)")] string V_NODE,
[Parameter(Name="V_SENSOR", DbType="NUMBER")] System.Nullable<decimal> V_SENSOR,
[Parameter(Name="V_DURATION", DbType="NUMBER")] System.Nullable<decimal> V_DURATION,
[Parameter(Name="V_USERNAME", DbType="VARCHAR2(32766 CHAR)")] string V_USERNAME,
[Parameter(Name="V_PROGRAMSTATE", DbType="NUMBER")] System.Nullable<decimal> V_PROGRAMSTATE)
{
    IExecuteResult _TESTPROCResult = this.ExecuteMethodCall(this, ((MethodInfo)              (MethodInfo.GetCurrentMethod())), V_NODE, V_SENSOR, V_DURATION, V_USERNAME, V_PROGRAMSTATE);
}

[MariiaI]

Yes, the issue is related to raise_application_error method in your SP. When adding procedures to the model, Entity Developer tries to execute a stored procedure to determine the return type of the method which corresponds to this stored procedure. So, you see the warning about obtaining metadata of procedure result set. In case, you choose OK the stored procedure will be executed and the return type will be determined. Whether any data or database structures may be damaged, depends solely on what the procedure does. If you are sure this procedure is safe, you can allow Entity Developer to run it and get the metadata. Otherwise, you can configure the procedure result set manually.
In your case, the execution of the SP fails and the metadata of the result set cannot be obtained by Entity Developer.

As a solution you can try performing these steps:
- re-create your SP so that there is no raise_application_error method in it (temporarily);
- add this SP to your model, obtain metadata of procedure result set and generate the code for method;
- re-create your SP to the initial form and try calling it from the application.

Also, you can try manually changing the code for generated method.

For more information please refer here.

If you have any further questions, feel free to contact us.

[arv]

Thanks.