How can I execute 2 SQLplus statements on the same 1 line C #

Question

I have 1 procedure that can be used on sqlplus but in C# I don't know how to write execute in cmd.commandtext

this is my command sqlplus

CREATE OR REPLACE PROCEDURE get_column1 (p_MaritalStatus IN VARCHAR2,p_cursor out SYS_REFCURSOR)
as
BEGIN
   Open p_cursor for
   SELECT column_name FROM all_tab_columns WHERE table_name = p_MaritalStatus ORDER BY column_id;
   dbms_sql.return_result(p_cursor);
EXCEPTION
WHEN no_data_found THEN 
NULL;
 END;

VARIABLE cur REFCURSOR;
execute get_column1('V_$SGA',:cur);

c#

`OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "VARIABLE cur REFCURSOR;execute get_column1('V_$SGA',:cur);";
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader dr = cmd.ExecuteReader();`

Very similar to https://stackoverflow.com/q/3940587/1509264 or https://stackoverflow.com/q/33422233/1509264 (but the specific question of using two statements is different) — MT0, Mar 23 '23 at 10:25

jmcilhinney · Answer 1 · 2023-03-23T10:15:26.037

When you set CommandType to StoredProcedure, that means that the CommandText contains ONLY the name of a stored procedure. Something like this:

cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;

Your command is NOT a stored procedure. It's inline SQL. The fact that that SQL invokes a stored procedure is neither here nor there. Just get rid of the line that sets CommandType and accept the default Text.

I don't really use Oracle so I'm still not 100% sure that it will work with multiple SQL statements separated by semicolons but the would work with SQL Server, so I'm guessing it will be fine.

EDIT:

According to a comment posted below, you cannot execute multiple statements together in a single command. In that case, you would presumably have use two separate commands or the same command twice and change the CommandText in between. You'd probably want to do so within a transaction. What I said about the CommandType still stands.

"would work with SQL Server, so I'm guessing it will be fine." **NO**, Oracle forbids multiple statements in a single command to help prevent SQL injection attacks so it is definitely not fine. — MT0, Mar 23 '23 at 10:08

MT0 · Answer 2 · 2023-03-23T10:21:44.847

Oracle forbids multiple statements in a single command. This helps to prevent SQL injection attacks.

For your use case, do not try to declare the variable in SQL, specify the bind parameter in C#:

CREATE OR REPLACE PROCEDURE get_column1 (
  p_MaritalStatus IN  all_tab_columns.table_name%TYPE,
  p_cursor        OUT SYS_REFCURSOR
)
AS
BEGIN
   OPEN p_cursor FOR
     SELECT column_name
     FROM   all_tab_columns
     WHERE  table_name = p_MaritalStatus
     ORDER BY column_id;
END;
/

Then (untested as I do not have a C# connected database but it gives you the general idea):

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P_MARITALSTATUS", OracleDbType.Char).Value = "V_$SGA";
cmd.Parameters.Add("P_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
OracleDataReader dr = cmd.ExecuteReader()

How can I execute 2 SQLplus statements on the same 1 line C #

2 Answers2