ORA-00922: missing or invalid option When trying to execute PL/SQL from C#

Question

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

My PL/SQL works from within Oracle SQL Developer. The error I am getting in my test.aspx page is:

ORA-00922: missing or invalid option

The code behind in the test.aspx.cs page is:

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());

        }

Answer 1

1) SET SERVEROUTPUT ON is 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).

2) You almost certainly do not want to use DBMS_OUTPUT for 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_LINE and then have your application make calls to DBMS_OUTPUT.GET_LINE to 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.

3) I'm not sure what type tablename.myfunction is 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.

Answer 2

I don't see you closing this statement dbms_output.put_line('output= '||output"+