oracle ORA-00922：当尝试从 C# 执行 PL/SQL 时缺少或无效的选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8072244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00922: missing or invalid option When trying to execute PL/SQL from C#
提问by Geekender
I am almost positive this is user error.
我几乎肯定这是用户错误。
I am new to connecting C# to an Oracle database and have modeled my code after that in the Oracle online documentation
我是将 C# 连接到 Oracle 数据库的新手，并在Oracle 在线文档中对我的代码进行了建模
My PL/SQL works from within Oracle SQL Developer. The error I am getting in my test.aspx page is:
我的 PL/SQL 在 Oracle SQL Developer 中工作。我在 test.aspx 页面中遇到的错误是：
ORA-00922: missing or invalid option
ORA-00922: 缺少或无效的选项
The code behind in the test.aspx.cs page is:
test.aspx.cs 页面后面的代码是：
string currentTerm = getCurrentTerm();
string passwd = "<get password input>";
string salt = "<get salted input>";
conn.ConnectionString = ConfigurationManager.ConnectionStrings["CONNECTIONSTRINGNAME"].ConnectionString;
conn.Open();
// pl/sql block
string pl_sql = " set serveroutput on " +
"DECLARE"+
" output tablename.function%TYPE;" +
"BEGIN"+
" tablename.myfunction(:1,:2,:3);" +
" dbms_output.put_line('output= '||output"+
"END;";
//Oracle Parameters necessary for the myfunction function
OracleParameter p_1 = new OracleParameter(passwd, OracleDbType.Varchar2, 50, ParameterDirection.Input);
OracleParameter p_2 = new OracleParameter(salt, OracleDbType.Varchar2, 50, ParameterDirection.Input);
OracleParameter p_3 = new OracleParameter("3", OracleDbType.Varchar2, 50, ParameterDirection.Output);
// create the command object
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = pl_sql;
// add the parameters
cmd.Parameters.Add(p_1);
cmd.Parameters.Add(p_2);
cmd.Parameters.Add(p_3);
// execute the pl/sql block
cmd.ExecuteNonQuery();
// get a data reader from the ref cursor
// note: this is on p_3, the output value
OracleDataReader dr = ((OracleRefCursor)p_3.Value).GetDataReader();
while (dr.Read())
{
Response.Write("Salt licked hash: "+ dr[0].ToString());
//Output the line retrieved from dbms_output.put line
Response.Write("<br />");
Response.Write("DBMS_OUTPUT STATUS: "+ p_3.Value.ToString());
}
回答by Justin Cave
1) SET SERVEROUTPUT ONis a SQLPlus function-- it is not part of the SQL or PL/SQL language (though lots of GUIs like SQL Developer and Toad have at least some support for SQLPlus functions like this).
1)SET SERVEROUTPUT ON是一个 SQL Plus 函数——它不是 SQL 或 PL/SQL 语言的一部分（尽管很多像 SQL Developer 和 Toad 这样的 GUI 至少对这样的 SQLPlus 函数有一些支持）。
2) You almost certainly do not want to use DBMS_OUTPUTfor anything having to do with your application-- that's not the right way to get data from a PL/SQL block. Technically, you could call DBMS_OUTPUT.ENABLE(<<buffer size>>)in your PL/SQL block, write data to the buffer using DBMS_OUTPUT.PUT_LINEand then have your application make calls to DBMS_OUTPUT.GET_LINEto read the data from the buffer once the procedure completed. That's what SQL*Plus and SQL Developer are doing under the covers. But that's not the appropriate way to structure an application.
2) 您几乎肯定不想DBMS_OUTPUT用于与您的应用程序有关的任何事情——这不是从 PL/SQL 块获取数据的正确方法。从技术上讲，您可以调用DBMS_OUTPUT.ENABLE(<<buffer size>>)PL/SQL 块，使用将数据写入缓冲区DBMS_OUTPUT.PUT_LINE，然后DBMS_OUTPUT.GET_LINE在过程完成后让您的应用程序调用以从缓冲区读取数据。这就是 SQL*Plus 和 SQL Developer 在幕后所做的事情。但这不是构建应用程序的适当方式。
3) I'm not sure what type tablename.myfunctionis returning. If it is returning a simple scalar or a REF CURSOR, life is much easier-- you can just call the function without the PL/SQL anonymous block as Harrison demonstrates in this SO thread on calling a function that returns a REF CURSOR in C#. If it is returning a PL/SQL record type, Mark Williams has an example of an anonymous PL/SQL block that returns a PL/SQL record in C#on the OTN forums.
3）我不确定tablename.myfunction返回的是什么类型。如果它返回一个简单的标量或一个 REF CURSOR，生活就容易多了——你可以只调用没有 PL/SQL 匿名块的函数，正如 Harrison 在这个 SO 线程中演示的那样，调用一个在 C# 中返回一个 REF CURSOR 的函数。如果它返回 PL/SQL 记录类型，Mark Williams 有一个匿名 PL/SQL 块示例，该块在 OTN 论坛上以 C# 形式返回 PL/SQL 记录。
回答by Daryl
I don't see you closing this statement dbms_output.put_line('output= '||output"+
我没有看到你关闭这个声明 dbms_output.put_line('output= '||output"+
