locked
Oracle Stored Procedure Execution Error RRS feed

  • Question

  • User-229812728 posted

    Hi ,

      I am trying a simple code to execute a oracle stored procedure. I encountered the below error when executing the web page :-

               ERRORERROR [42000] [Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

    My stored procedure can be successfully executed from  SQLPLUS command.  I have enlisted my codes below. Really appreciate your help as I have no idea what is the problem. I have also looked for any similar problems in the Forum but can't get a solution.

    Thanks !.

    Regards, Thana.

    ---------------------------------------------------------------------------------------------

    My Stored Procedure Code in Oracle :-

    CREATE OR REPLACE PROCEDURE       extract_list(dptno IN NUMBER,   d_out OUT dept.dname%TYPE) AS
    d_nm dept.dname%TYPE;
    BEGIN
      SELECT dname
      INTO d_nm
      FROM dept
      WHERE deptno = dptno;
      d_out := d_nm;
    END;

    Below is my ASP.NET code in C# :-

    using System;

    using System.Data;

    using System.Configuration;

    using System.Collections;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

    using System.Data.Odbc;

    using System.Web.Configuration;

    public partial class Default2 : System.Web.UI.Page

    {

    protected void Page_Load(object sender, EventArgs e)

    {

    String dptname;

    String con = WebConfigurationManager.ConnectionStrings["con"].ConnectionString;

    OdbcConnection sqlnew = new OdbcConnection(con);

    try

    {

    sqlnew.Open();

    // OdbcCommand cmd = new OdbcCommand("Extract_List", sqlnew);

    OdbcCommand cmd = new OdbcCommand();

    cmd.Connection = sqlnew;

    cmd.CommandText = "EXTRACT_LIST";

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new OdbcParameter("@dptno", OdbcType.Int));

    cmd.Parameters["@dptno"].Value = 20;

    cmd.Parameters.Add(new OdbcParameter("@d_out", OdbcType.NVarChar, 25));

    cmd.Parameters["@d_out"].Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();

    Response.Write("Return Value:" + cmd.Parameters["@d_out"].Value);

    }

    catch (Exception oEx)

    {

    Response.Write("ERROR" + oEx.Message);

    }

    finally

    {

    sqlnew.Close();

    }

    }

    }

    Wednesday, July 12, 2006 9:49 PM

All replies

  • User1589233239 posted

    Hi there,

    I tried your code but modified names and stuff. Itried odbc but the connection string was giving me problems and so I tried OracleClient. I am not sure if this will be useful to you or not but here goes.

    -------------------------------------

    create table test(id number(2),name varchar2(20));

    insert into test values (10,'hello1');

    insert into test values (20,'hello2');

    insert into test values (30,'hello3');

    -------------------------------------

    CREATE OR REPLACE PROCEDURE ptest(p_id IN NUMBER, p_name OUT test.name%TYPE)
    AS 
    v_result varchar2(100);
    BEGIN

      SELECT name
      INTO v_result
      FROM test
      WHERE id = p_id;

      p_name :=  v_result;

    END;

    -------------------------------------

    using System;

    using System.Data;

    using System.Configuration;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

    using System.Web.Configuration;

    using System.Data.OracleClient ;

    public partial class _Default : System.Web.UI.Page

    {

    protected void Page_Load(object sender, EventArgs e)

    {

    OracleConnection sqlnew = new OracleConnection("myconnectionstring");

    try

    {

    sqlnew.Open();

    OracleCommand cmd = new OracleCommand();

    cmd.Connection = sqlnew;

    cmd.CommandText = "ptest";

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("p_id", OracleType.Int32);

    cmd .Parameters["p_id"].Value = 20;

    cmd.Parameters["p_id"].Direction = ParameterDirection.Input;

    cmd.Parameters.Add("p_name", OracleType.VarChar, 100);

    cmd.Parameters["p_name"].Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();

    Response.Write("Return Value:" + cmd.Parameters["p_name"].Value);

    }

    catch (Exception oEx)

    {

    Response.Write("ERROR" + oEx.Message);

    }

    finally

    {

    sqlnew.Close();

    }

    }

    }

    "Return Value:hello2 " prints out fine. I hope this helps.

     

     

     

    Friday, July 14, 2006 7:12 PM
  • User-229812728 posted

    Hi ,

    Thanks for the help. It works !.

     

    Regards,

    Thana

    Wednesday, July 19, 2006 7:26 AM