C# OracleCommand return multiple DataTable as Output-Parameter with a single CURSOR

Question

I have an SQL file that looks like this:

DECLARE
  V_ID_CB VARCHAR2(15) := '{V_ID_CB}';
  V_X_CB VARCHAR2(15) := '{V_X_CB}';
BEGIN
    IF V_ID_CB IS NOT NULL THEN
        OPEN :result_cursor FOR
            SELECT * FROM DEMANDES WHERE ID_CB = V_ID_CB;
    ELSIF V_X_CB IS NOT NULL THEN
        OPEN :result_cursor FOR
            SELECT * FROM SITES;
    END IF;
END;

I read this file in C# and then use the Replace command to replace the value of the variable with the actual value:

      var content = System.IO.File.ReadAllText(filePath);
      var sqlSkript = content;
      foreach (var param in parameters)
      {
        sqlSkript = sqlSkript.Replace($"{{V_{param.Key}}}", param.Value);
      }

public DataTable Execute(string sqlSkript)
    {
      try
      {
        if (!(_oraController.OracleConnection.State == ConnectionState.Open))
        {
          _oraController.OracleConnection.Open();
        }
        using (var command = new OracleCommand(sqlSkript) { Connection = _oraController.OracleConnection })
        {
          command.CommandType = CommandType.Text;
          OracleParameter resultCursor = new OracleParameter
          {
            ParameterName = "result_cursor",
            OracleDbType = OracleDbType.RefCursor,
            Direction = ParameterDirection.Output
          };
          command.Parameters.Add(resultCursor);
          using (var reader = command.ExecuteReader())
          {
            DataTable dataTable = new DataTable();
            dataTable.Load(reader);
            return dataTable;
          }
        }
      }
      catch (Exception ex)
      {
        string errorMessage = ex.Message + "\n" + ex.StackTrace;
        throw new Exception(errorMessage, ex);
      }
    }

This also works so far and I get the result of the SELECT-Statement in a DataTable.

Now I would like to query several tables as output parameters. How is this possible? Is that even possible? I only want to work with a single CURSOR.

I have tried to achieve my goal by using several CURSORs. However, this SQL file will constantly grow, which would mean that I would have to define a new “result_cursor” in my code for each new SELECT. This is not a solution for me.

"I read this file in C# and then use the Replace command to replace the value of the variable with the actual value" Which makes your code vulnerable to SQL injection attacks. Don't use find-replace to build SQL queries. Instead use bind variables. (You are already using them for the returned cursor, use them for the input values as well.) — MT0
– MT0, Commented Sep 24, 2024 at 15:57

Sinus32 · Accepted Answer · 2024-09-25 07:12:18Z

0

Now I would like to query several tables as output parameters. How is this possible? Is that even possible?

Yes, it's possible.

I only want to work with a single CURSOR.

Bad luck here, each result set you return requires own REF CURSOR.

I have tried to achieve my goal by using several CURSORs. However, this SQL file will constantly grow, which would mean that I would have to define a new “result_cursor” in my code for each new SELECT.

Using several REF CURSORs is the right solution.

And one note:

[...] use the Replace command to replace the value of the variable [...]

Do not use Replace, use parameters instead. You already declared one (result_cursor), just declare more.

answered Sep 25, 2024 at 7:12

Sinus32

1,1037 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Akin Over a year ago

But how can I create a new CURSOR each time in C#? I don't know how many CURSORs there will be in the SQL file. In the SQL file, I would then use a new CURSOR for each new SELECT, e.g. :result_cursor1, :result_cursor2 ... . And in C# I would then have to say the following for each individual CURSOR: OracleParameter resultCursor = new OracleParameter { ParameterName = "result_cursor1", OracleDbType = OracleDbType.RefCursor, Direction = ParameterDirection.Output }; Same thing for result_cursor2, result_cursor3 ... .

Akin Over a year ago

Because the SQL file is unknown to me and can grow constantly, I cannot define a new CURSOR in C# in this way every time

Sinus32 Over a year ago

How do you know how many result sets are you expecting? Maybe instead of having one big file you should make many files with one results set per file?

Akin Over a year ago

Unfortunately that is also not an option for me. But is it possible to Output the result in one big string? So I don't need to use any CURSOR at all?

Akin Over a year ago

Maybe a csv string?

|

Collectives™ on Stack Overflow

C# OracleCommand return multiple DataTable as Output-Parameter with a single CURSOR

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related