locked
Ora -01036 RRS feed

  • Question

  • User-541003552 posted

    Am using this code to call a store procedure from an oracle database server but having the below error.

    ORA-01036: illegal variable name/number


       at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
       at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
       at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
       at System.Data.OracleClient.OracleCommand.ExecuteScalarInternal(Boolean needCLStype, Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
       at System.Data.OracleClient.OracleCommand.ExecuteScalar()
       at SaveImage.Form1.BOACheck() in e:\ProjectsTestLab\SaveImageProject\SaveImage\Form1.cs:line 243

                try
                    {
                OracleConnection conn = new OracleConnection(strOraBOA);
                OracleCommand cmd = new OracleCommand("pk_gh.GET_CLIENT_IDP", conn);
                cmd.CommandType = CommandType.StoredProcedure;
    			OracleParameter  param1 = new OracleParameter("VCOMPTE", OracleType.NVarChar); 
                param1.Direction = ParameterDirection.Input;
                param1.Value = 02001105480;    //AccNo.Text;
    
                    cmd.Parameters.Add(param1);
                     cmd.Parameters.Add(new OracleParameter("CLIENT_NO", OracleType.NVarChar, 6)).Direction = ParameterDirection.Output;
                     cmd.Parameters.Add(new OracleParameter("IDP ", OracleType.NVarChar, 7)).Direction = ParameterDirection.Output;
                      conn.Open();
                     cmd.ExecuteScalar();
                     Message.Text = "SUCCESS";
    
                    }
                catch (Exception ex)
                    {
                    Message.Text = ex.Message + "\n\r" + ex.StackTrace;
    
                    }

    Any help please

    Tuesday, September 1, 2015 8:02 AM

Answers

  • User269602965 posted

    Your IN and OUT parameters in the PL/SQL procedure side must match the ORDER and NUMBER of parameters on the .NET side.

    The data types of the IN and OUT parameters must match as well, so replace oracletype Nvarchar with VARCHAR2

    REFCURSOR are typically passed to a Oracle DataAdapter and read into dataset to use in an application table.

    example:

    /* VB.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    Try
    	Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
    	Using conn As New OracleConnection(connstr)
    		Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
    			cmd.CommandType = CommandType.StoredProcedure
    			cmd.Parameters.Clear()
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    End Try
    
    /* CS.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    try 
      {
    	string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
    	using (OracleConnection conn = new OracleConnection(connstr)) 
    	  {
    		using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) 
    		  {
    			cmd.CommandType = CommandType.StoredProcedure;
    			cmd.Parameters.Clear();
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input);
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input);
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input);
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output);
    			conn.Open();
    			using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) 
    			  {
    				DataSet ds = new DataSet();
    				oda.Fill(ds);
    				this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
    		  	}
    		  }
    	  }
      } 
    catch (Exception ex) 
    {}
    
    
    /* Oracle PL/SQL code */
    
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR);
    END PKG_BIRDS;
    /
    
    CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR)
    BEGIN
    	OPEN ret_REFCUR FOR
    		SELECT
    			AOU_CODE,
    			BIRD_CLASS,
    			BIRD_NAME,
    			DATE_BAND,
    			WING_LENGTH,
    			TAIL_LENGTH,
    			EYE_COLOR,
    			PLUMAGE_CODE
    		FROM
    			{SCHEMANAME}BANDED_BIRDS
    		WHERE
    			DATE_BAND  > p_DATE_BAND
    			AND
    			AOU_CODE   = p_AOU_CODE
    			AND
    			BIRD_CLASS = p_BIRD_CLASS
    END;
    
    END {SCHEMANAME}.{PACKAGENAME};
    /
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 1, 2015 11:29 PM

All replies

  • User269602965 posted

    Check the datatype of the IN parameter on the stored proc side.. is it not NVARCHAR2 or VARCHAR2?

    OracleType must match the datatype of the IN parameter.

    If your IN parameter is a STRING VARCHAR2 or NVARCHAR2

    you are trying to pass a NUMBER value and that will fail.

    convert number to string to be compatible with NVARCHAR2 datatype

    Tuesday, September 1, 2015 9:24 AM
  • User-541003552 posted

    Hi  Lannie,

    below is the store procedure

    PROCEDURE GET_CLIENT_IDP
    Argument Name                  Type                              In/Out         Default?
    ------------------------------ -----------------------                      ------ -           -------
    VCOMPTE                      VARCHAR2                           IN
    VRESULT                       REF CURSOR                         OUT
    CLIENT_NO                  VARCHAR2(6)                        OUT
    IDP                               VARCHAR2(7)                        OUT
    

     any help

    Tuesday, September 1, 2015 9:51 AM
  • User269602965 posted

    Your IN and OUT parameters in the PL/SQL procedure side must match the ORDER and NUMBER of parameters on the .NET side.

    The data types of the IN and OUT parameters must match as well, so replace oracletype Nvarchar with VARCHAR2

    REFCURSOR are typically passed to a Oracle DataAdapter and read into dataset to use in an application table.

    example:

    /* VB.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    Try
    	Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
    	Using conn As New OracleConnection(connstr)
    		Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
    			cmd.CommandType = CommandType.StoredProcedure
    			cmd.Parameters.Clear()
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    End Try
    
    /* CS.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    try 
      {
    	string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
    	using (OracleConnection conn = new OracleConnection(connstr)) 
    	  {
    		using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) 
    		  {
    			cmd.CommandType = CommandType.StoredProcedure;
    			cmd.Parameters.Clear();
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input);
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input);
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input);
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output);
    			conn.Open();
    			using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) 
    			  {
    				DataSet ds = new DataSet();
    				oda.Fill(ds);
    				this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
    		  	}
    		  }
    	  }
      } 
    catch (Exception ex) 
    {}
    
    
    /* Oracle PL/SQL code */
    
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR);
    END PKG_BIRDS;
    /
    
    CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR)
    BEGIN
    	OPEN ret_REFCUR FOR
    		SELECT
    			AOU_CODE,
    			BIRD_CLASS,
    			BIRD_NAME,
    			DATE_BAND,
    			WING_LENGTH,
    			TAIL_LENGTH,
    			EYE_COLOR,
    			PLUMAGE_CODE
    		FROM
    			{SCHEMANAME}BANDED_BIRDS
    		WHERE
    			DATE_BAND  > p_DATE_BAND
    			AND
    			AOU_CODE   = p_AOU_CODE
    			AND
    			BIRD_CLASS = p_BIRD_CLASS
    END;
    
    END {SCHEMANAME}.{PACKAGENAME};
    /
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 1, 2015 11:29 PM