How to create a Clob output parameter

[ning]

I would like avoid using OracleParameter those concreated class in my codes.

I want to use codes like below:

IDbCommand cmd=...;
var param=cmd.CreateParameter();
param.ParameterName="MyParam";
param.DbType=DbType.String;
param.Direcition= ParameterDirection.Output;
this works for most types, however i cannot do the same thing for clob, there is no corresponding DbType for Clob.
I looked into the codes, i found that when you assign a string whose length is more than 4000, OracleDbType return Clob, however in this case my parameter is an output parameter. By setting the param.Value="more than 4000 string here", it doesn't work properly.
Is there any walk around?

[StanislavK]

Changing the parameter type to CLOB is an expected behaviour, as VARCHAR2 maximum size is limited. As I can understand, you are encountering some problems when working with CLOB output parameters. Could you please describe these problems in more details? E.g., are you getting an exception? If yes, please specify its message and stack trace.

To change this behaviour, you can, e.g., create an OracleParameter descendant and override the Values property in it, so that OracleDbType is not changed when passing a large string.

[ning]

I don;t like to write the codes using OracleParameter directly.

I wish not using the devart namespace in my codes.

For example, i can write codes like this:

IDbCommand cmd=...//get the cmd from some where, like conn.CreateCommand();
var param= cmd.CreateParameter();
param.ParameterName="foo";
param.DbType=DbType.int32;
param.Value=12;


By looking into the devart source code.
The param 's OracleDbType is set to OracleDbType.Number.
However there is no corresponding DbType that will be converted to OracleDbType.Clob.
By looking into the devart source code. I found that follow codes can create a Clob prameter.

var param= cmd.CreateParameter();
param.ParameterName="foo";
param.Value=create4000LengthString(); // a string contains more than 4000 charcters.

this can cause the param.OracleDbType to be clob. But this doesn't work if param is a output param.

[StanislavK]

Thank you for clarification. Unfortunately, the IDbParameter interface has no property that can be used for setting provider-specific types.

As a workaround for this, you can, e.g., do the following:

• create an OracleParameter descendant;
• override the Size property in the following way:

  Code: Select all

  public override int Size {
    get { return base.Size; }
    set {
      base.Size = value;
      if (DbType == DbType.String && Size > 4000)
        this.OracleDbType = OracleDbType.Clob;
    }
  }

• when you need to use CLOBs, set the size to something larger than 4000.

Or, you can try setting the value to a large string before using the parameter.

Please tell us if this helps.

[ning]

---
Or, you can try setting the value to a large string before using the parameter.
---

Are you sure this will work for a output parameter? i haven't done a full test, but in my codes it seems not work for a output parameter.

var param= cmd.CreateParameter();
param.ParameterName="foo";
param.Value=create4000LengthString(); // a string contains more than 4000 charcters.
param.Direcition= ParameterDirection.Output;

The param seems cannot get the value back.

[StanislavK]

We have reproduced the problem with setting the output parameter value (in our environment, no new value is assigned to it after executing the command). We will analyze this issue and inform you about the results.

At the moment, you can use the workaround with creating an OracleParameter descendant.