0

I am using Dapper and have specified the ParameterDirection and Size. But the string that comes back is not the whole string.

[INF] Return message: The person-ID 23 has been inse

var queryParameters = new DynamicParameters();
queryParameters.Add("id", item.Id);
queryParameters.Add("first_name", item.FirstName);
queryParameters.Add("last_name", item.LastName);
//queryParameters.Add("out_message", direction: ParameterDirection.Output, size: 250);
//queryParameters.Add("out_message", direction: ParameterDirection.Output, size: DbString.DefaultLength);
queryParameters.Add("out_message", dbType:DbType.String, direction: ParameterDirection.Output, size: 500);

var results = connection.Query("kwc_test_person_procedure2",
queryParameters,
commandType: CommandType.StoredProcedure);

var returnMessage = queryParameters.Get<string>("out_message");
Log.Information("Return message: {0}", returnMessage);

The Oracle Stored Procedure is as follow, and I am getting the full string of out_message when I test it directly in the database:

create or replace procedure kwc_test_person_procedure2(
id in number,
first_name in varchar2,
last_name in varchar2,
out_message out varchar2
) is

begin
  insert into kwc_test_person(id, first_name, last_name) values (id, upper(first_name), upper(last_name));
  out_message := 'The person-ID ' || id || ' has been inserted to database on ' || sysdate;
  dbms_output.put_line( 'The person-ID ' || id || ' has been inserted to database on ' || sysdate);

End kwc_test_person_procedure2;

The person-ID 84 has been inserted to database on 24-JUN-21

As you can see in the commented out lines, I have tried different things.

When I tried:

queryParameters.Add("out_message", dbType: DbType.String, direction: ParameterDirection.Output, size: int.MaxValue);

I even got exception:

[ERR] Parameter 'out_message': No size set for variable length data type: String.

Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
KWC
  • 109
  • 1
  • 8
  • did you try this out? https://stackoverflow.com/questions/54095648/using-dapper-to-get-nvarcharmax-returns-a-string-trimmed-to-4000-characters-c/54096564#54096564 – mabiyan Jun 24 '21 at 11:54
  • @ashmabi, yes. And I got exception: ORA-01459: invalid length for variable character string. – KWC Jun 24 '21 at 12:27

1 Answers1

0

I have found the solution.

So the following works, If I was using the correct client.

queryParameters.Add("out_message", direction: ParameterDirection.Output, size:DbString.DefaultLength);

I didn't realize that I was using System.Data.OracleClient in my test-project. So the solution was by using the client under ManagedDataAccess.

//using System.Data.OracleClient;
using System.Linq;
using Dapper;
using Oracle.ManagedDataAccess.Client;

2021-06-25 13:35:41.452 +02:00 [INF] Return message: The person-ID 23 has been inserted to database on 25.06.2021

KWC
  • 109
  • 1
  • 8