C# Oracle ODP: Is it possible to return multiple query results in a single trip to the server without calling a stored procedure?

Question

I expected to be able to include multiple SELECT statements, each separated by a semicolon, in my query, and get a dataset returned with as the same number of datatables as individual SELECT statements.

I am starting to think that the only way that this can be done is to create a stored procedure with multiple refcursor output parameters.

string sql = @"SELECT
                            R.DERVN_RULE_NUM
                            ,P.DERVN_PARAM_INPT_IND
                            ,R.DERVN_PARAM_NM
                            ,R.DERVN_PARAM_VAL_DESC
                            ,P.DERVN_PARAM_SPOT_NUM
                            ,R.DERVN_PARAM_VAL_TXT
                        FROM
                            FDS_BASE.DERVN_RULE R
                                INNER JOIN FDS_BASE.DERVN_PARAM P
                                        ON R.DERVN_TY_CD = P.DERVN_TY_CD
                                            AND R.DERVN_PARAM_NM = P.DERVN_PARAM_NM
                        WHERE
                            R.DERVN_TY_CD = :DERVN_TY_CD
                        ORDER BY
                             R.DERVN_RULE_NUM
                            ,P.DERVN_PARAM_INPT_IND DESC
                            , P.DERVN_PARAM_SPOT_NUM";

        var dataSet = new DataSet();

        using (OracleConnection oracleConnection = new OracleConnection(connectionString))
        {
            oracleConnection.Open();

            var oracleCommand = new OracleCommand(sql, oracleConnection)
            {
                CommandType = CommandType.Text
            };

            oracleCommand.Parameters.Add(":DERVN_TY_CD", derivationType);
            var oracleDataAdapter = new OracleDataAdapter(oracleCommand);
            oracleDataAdapter.Fill(dataSet);
        }
           

I tried to apply what I read here:

https://www.intertech.com/Blog/executing-sql-scripts-with-oracle-odp/

including changing my SQL to enclose it in a BEGIN END BLOCK in this form:

string sql = @"BEGIN 
                 SELECT 1 FROM DUAL;
                 SELECT 2 FROM DUAL;
               END";

and replacing my end of line character

sql = sql.Replace("\r\n", "\n");

but nothing works.

Is this even possible w/o using a stored procedure using ODP or must I make a seperate trip to the server for each query?

Answer 1

The simplest way to return multiple query results from a single statement is with the CURSOR SQL function. For example:

select
    cursor(select * from all_tables) tables,
    cursor(select * from all_objects) objects
from dual;

(However, I am not a C# programmer, so I don't know if this solution will work for you. Let me know if the code doesn't work - there's probably another solution using anonymous blocks and OUT parameters.)

Answer 2

must I make a seperate trip to the server for each query?

The way this is asked makes it seem like there's a considerable effort or waste of resources going on somewhere that can be saved or reduced, like making a database query is the equivalent of walking to the shops to get milk, coming back, then walking to the shops again to get bread and coming back

There isn't any appreciable saving to be had; if this was going to the shops, db querying is like being able to clone yourself X times, the X of you all going to the shops, and coming back at different times - some of you found your small things instantly and sprint back with them, some of you found the massive things instantly and stagger back with them, some of you took ages to find your things etc. (These are metaphors for the speed of query execution and the time required to download large vs small result sets).

If you have two queries that take ten seconds each to run, you can set them going in parallel and have your results ready and retrieved to the client in 10+x seconds (x being the time required to drag the data over the network), or you could execute them in series and have it be 20+x

If you think about it, putting two queries in one statement is only the same thing as submitting two statements for execution over different connections. The set of steps the db must take, and the set of steps the client must do to read, are the same. Writing a sproc to handle it is more effort, more complexity to maintain and more places code lives in. Even writing a block to do it is more. None of it saves anything. Even the bytes in the header of the tcp packets, minutiae as they are, are offset by more complex multi line blocks. If one query takes considerably longer than the other you might even be hamstrung into having to wait for them all to finish before you can get the results

Write your "query statement x with y parameters and return resultset Z" as async, start two of them and Task.WhenAll to wait for them to finish; if you can handle it, don't do a WhenAll but instead read and use the results as they finish - that's a saving, if the process can logically proceed before all queries deliver

I get that you're thinking "surely I should just walk to the shops and carry milk and bread back with me - that's more efficient than going twice" but it's a faulty perspective when you consider that the shop is nanoseconds away because you run at the speed of light, you have multiple private unobstructed paths to it and the bigger spend of time is finding the items you want and loading them into sufficient chained-together carts/dragging them all home. With a cloning approach, if the milk is right there, one of you can take it home and spend 10 minutes making the béchamel with it while the other of you is still waiting 10 minutes for the shop to bake the bread that you'll eat directly when you get home - you can still eat in 10 minutes if you maintain the parallelism, and launching separate operations is not only simpler but it keeps you in easy control of that