英文:
How can I execute 2 SQLplus statements on the same 1 line C #
问题
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 in SQL:
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);
And in C#:
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "VARIABLE cur REFCURSOR;execute get_column1('V_$SGA',:cur);";
cmd.CommandType = CommandType.StoredProcedure;
OracleDataReader dr = cmd.ExecuteReader();
英文:
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();`
答案1
得分: 1
当你将CommandType
设置为StoredProcedure
时,这意味着CommandText
仅包含存储过程的名称。就像这样:
cmd.CommandText = "get_column1";
cmd.CommandType = CommandType.StoredProcedure;
您的命令不是存储过程,它是内联SQL。这个SQL调用存储过程的事实与此无关。只需删除设置CommandType
的那一行,并接受默认的Text
。
我不常使用Oracle,所以我仍然不能100%确定它是否能够处理使用分号分隔的多个SQL语句,但对于SQL Server,这应该没有问题,所以我猜应该可以。
编辑:
根据下面发布的评论,您不能在单个命令中执行多个语句。在这种情况下,您可能需要使用两个单独的命令或在它们之间更改CommandText
,最好在一个事务内执行。关于CommandType
的说法仍然适用。
英文:
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.
答案2
得分: 1
Oracle禁止在单个命令中使用多个语句,以防止SQL注入攻击。
对于您的用例,请不要尝试在SQL中声明变量,而是在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;
/
然后(未经测试,因为我没有与C#连接的数据库,但它给出了一般的想法):
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()
英文:
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()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论