Stored procedure with lob out parametr

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
asmmail
Posts: 5
Joined: Wed 27 Mar 2019 08:43

Stored procedure with lob out parametr

Post by asmmail » Wed 27 Mar 2019 09:07

I need to use a stored procedure parameter with a lob data type to eliminate the overhead of copying a temporary lob.
I was used examples from documentation, but doesn't success.
Oracle 11g, dotConnect for Oracle latest version (9.6.696)

Code: Select all

CREATE TABLE ASM_CLOB (
  ID NUMBER(12),
  NAME VARCHAR2(20),
  DATA CLOB
)

CREATE OR REPLACE PROCEDURE ASM_ADD_CLOB(P_ID NUMBER, P_NAME VARCHAR2, P_DATA OUT CLOB)
IS
BEGIN
  INSERT INTO ASM_CLOB VALUES(P_ID, P_NAME, EMPTY_CLOB())
  RETURNING
    DATA
  INTO
    P_DATA;
END;

Code: Select all

string connectionString = ...;
using (OracleConnection connection = new OracleConnection(connectionString))
{
	connection.Open();

	OracleCommand oraCommand = new OracleCommand("asm_add_clob", connection);
	oraCommand.CommandType = CommandType.StoredProcedure;

	OracleParameter op1 = new OracleParameter("p_id", OracleDbType.Number, ParameterDirection.Input);
	op1.Value = 123;
	OracleParameter op2 = new OracleParameter("p_name", OracleDbType.VarChar, ParameterDirection.Input);
	op2.Value = "123";

	OracleParameter op3 = new OracleParameter("p_data", OracleDbType.Clob, ParameterDirection.Input);
	// Input according https://www.devart.com/dotconnect/oracle/articles/lob.html
	op3.Value = "test";

	oraCommand.Parameters.Add(op1);
	oraCommand.Parameters.Add(op2);
	oraCommand.Parameters.Add(op3);
  
	oraCommand.ExecuteNonQuery();
}
Result:
in Direct=true connection mode ASM_CLOB.DATA is empty after success stored procedure execution
in Direct=false connection mode Devart.Data.Oracle.OracleException: 'ORA-22275: invalid LOB locator specified'

Any ideas?

asmmail
Posts: 5
Joined: Wed 27 Mar 2019 08:43

Re: Stored procedure with lob out parametr

Post by asmmail » Mon 01 Apr 2019 13:42

Hello!

The problem persists in version 9.6.725 also.
It's "show-stopper" issue, please help

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Stored procedure with lob out parametr

Post by Pinturiccio » Mon 01 Apr 2019 14:01

Thank you for the provided information. The provided article is outdated and we will update it. You need to change your code in the following way:
1. Replace ParameterDirection.Input with ParameterDirection.Output in op3 creation.
2. Remove the following line from your code: op3.Value = "test";

Now your code should work.

asmmail
Posts: 5
Joined: Wed 27 Mar 2019 08:43

Re: Stored procedure with lob out parametr

Post by asmmail » Mon 01 Apr 2019 14:23

Yes, I've done something like this before:

Code: Select all

		OracleParameter op3 = new OracleParameter("p_data", OracleDbType.Clob, ParameterDirection.Output);

		oraCommand.Parameters.Add(op1);
		oraCommand.Parameters.Add(op2);
		oraCommand.Parameters.Add(op3);

		var reader = oraCommand.ExecuteNonQuery();


		var lob = (OracleLob) (op3.OracleValue);
                lob.Write("asdas");

                transaction.Commit();
It doesnt work.
Also, as a symptom: lob.IsTemporary == true, but should be false in case off direct lob access

asmmail
Posts: 5
Joined: Wed 27 Mar 2019 08:43

Re: Stored procedure with lob out parametr

Post by asmmail » Thu 04 Apr 2019 10:41

Is there any information on this issue? Сan you reproduce this issue?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Stored procedure with lob out parametr

Post by Pinturiccio » Thu 04 Apr 2019 14:40

asmmail wrote:It doesnt work.
We could not reproduce the issue. In our case, all works correctly with you code. Please specify the following:
1. What exactly doesn't work? Do you get an error, or you get incorrect behaviour?
2. Is the issue reproduced with dotConnect for Oracle 9.6.725 in your environment?
3. Oracle server version.
4. In which mode can the issue be reproduced: Direct, OCI, or both?
5. If the issue can be reproduced in the OCI mode, specify your Oracle Client version.
6. If possible, create and send us a small complete test project, which reproduces the issue.
asmmail wrote:Also, as a symptom: lob.IsTemporary == true, but should be false in case off direct lob access
We have reproduced the issue, that IsTemporary is true in this case. We will investigate it and post here about the results as soon as possible.

asmmail
Posts: 5
Joined: Wed 27 Mar 2019 08:43

Re: Stored procedure with lob out parametr

Post by asmmail » Tue 03 Sep 2019 13:32

Is there any information on this issue?
Ticket ID: 279777

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Stored procedure with lob out parametr

Post by Shalex » Thu 05 Sep 2019 11:43

The investigation is in progress. We will notify you about the result.

Post Reply