Problem when inserting data into an Oracle database using a stored procedure called from asp.net

I'm trying to insert data into a database using a Oracle stored procedure called from asp.net.  I've set the stored procedure up in my insert query, but when I call it I get the following error:

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I'm thinking this might have something to do with the way asp.net converts Oracle parameters.  Any help would be greatly appreciated.



Here's my stored procedure:
 
create or replace procedure INSERT_UPDATE_CUSTOMER(IN_COMPANY_ID        IN NUMBER,
                                                   IN_CUSTOMER_ID       IN NUMBER,
                                                   IN_LAST_NAME_COMPANY IN CHAR,
                                                   IN_FIRST_NAME        IN CHAR,
                                                   IN_PAYMENT_TERMS     IN CHAR,
                                                   IN_REQUIRE_PO        IN NUMBER) is
  MY_CUSTOMER_ID NUMBER;
  COUNTER        NUMBER;
begin
  SELECT COUNT(1)
    INTO COUNTER
    FROM CUSTOMER
   WHERE COMPANY_ID = IN_COMPANY_ID AND CUSTOMER_ID = IN_CUSTOMER_ID;
  IF COUNTER = 0 THEN
    SELECT MAX(CUSTOMER_ID)
      INTO MY_CUSTOMER_ID
      FROM CUSTOMER
     WHERE COMPANY_ID = IN_COMPANY_ID;
    SELECT NVL(MY_CUSTOMER_ID, 0) + 1 INTO MY_CUSTOMER_ID FROM DUAL;
    INSERT INTO CUSTOMER
    VALUES
      (IN_COMPANY_ID,
       MY_CUSTOMER_ID,
       IN_LAST_NAME_COMPANY,
       IN_FIRST_NAME,
       IN_PAYMENT_TERMS,
       IN_REQUIRE_PO);
  
  ELSE
    UPDATE CUSTOMER
       SET LAST_NAME_COMPANY = IN_LAST_NAME_COMPANY,
           FIRST_NAME        = IN_FIRST_NAME,
           PAYMENT_TERMS     = IN_PAYMENT_TERMS,
           REQUIRE_PO        = IN_REQUIRE_PO
     WHERE COMPANY_ID = IN_COMPANY_ID AND CUSTOMER_ID = IN_CUSTOMER_ID;
  END IF;
end INSERT_UPDATE_CUSTOMER;
 
 
Here's the asp code:
 
        <asp:SqlDataSource ID="SqlDataSourceCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:ACLink360 %>"
            ProviderName="<%$ ConnectionStrings:ACLink360.ProviderName %>" SelectCommand='SELECT * FROM CUSTOMER&#13;&#10;  WHERE COMPANY_ID = :COMPANY_ID AND CUSTOMER_ID = :CUSTOMER_ID' InsertCommand="INSERT_UPDATE_CUSTOMER(:IN_COMPANY_ID, :IN_CUSTOMER_ID, :IN_LAST_NAME_COMPANY, :IN_FIRST_NAME, :IN_PAYMENT_TERMS, :IN_REQUIRE_PO)" InsertCommandType="StoredProcedure">
            <SelectParameters>
                <asp:SessionParameter Name="COMPANY_ID" SessionField="COMPANY_ID" Type="Decimal" />
                <asp:SessionParameter DefaultValue="CUSTOMER_ID" Name="CUSTOMER_ID" SessionField="CUSTOMER_ID"
                    Type="Decimal" />
            </SelectParameters>
            <InsertParameters>
                <asp:SessionParameter Name="IN_COMPANY_ID" SessionField="COMPANY_ID" Type="Decimal" />
                <asp:SessionParameter Name="IN_CUSTOMER_ID" SessionField="CUSTOMER_ID" Type="Decimal" />
                <asp:ControlParameter ControlID="TextBoxLastNameCompany" Name="IN_LAST_NAME_COMPANY"
                    PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="TextBoxFirstName" Name="IN_FIRST_NAME" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="DropDownListPaymentTerms" Name="IN_PAYMENT_TERMS"
                    PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="CheckBoxRequirePO" Name="IN_REQUIRE_PO" PropertyName="Checked"
                    Type="Decimal" />
            </InsertParameters>
        </asp:SqlDataSource>

Open in new window

Randy RichPresidentAsked:
Who is Participating?
 
amritgillConnect With a Mentor Commented:
try calling it like this..change the values according to your proc


Using System;

using System.Collections.Generic;

using System.Text;

using System.Data.OracleClient;

using System.Data;

 

namespace CallingOracleStoredProc

{

    class Program

    {

        static void Main(string[] args)

        {

            using (OracleConnection objConn = new OracleConnection("Data Source=ORCL; User ID=scott; Password=tiger"))

            {

                OracleCommand objCmd = new OracleCommand();

                objCmd.Connection = objConn;

                objCmd.CommandText = "count_emp_by_dept";

                objCmd.CommandType = CommandType.StoredProcedure;

                objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;

                objCmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;

 

                try

                {

                    objConn.Open();

                    objCmd.ExecuteNonQuery();

                    System.Console.WriteLine("Number of employees in department 20 is {0}", objCmd.Parameters["pout_count"].Value);

                }

                catch (Exception ex)

                {

                    System.Console.WriteLine("Exception: {0}",ex.ToString());

                }

 

                objConn.Close();

            }

        }

    }

}

0
 
amritgillCommented:
change the is to AS in
create or replace procedure INSERT_UPDATE_CUSTOMER(IN_COMPANY_ID        IN NUMBER,
                                                   IN_CUSTOMER_ID       IN NUMBER,
                                                   IN_LAST_NAME_COMPANY IN CHAR,
                                                   IN_FIRST_NAME        IN CHAR,
                                                   IN_PAYMENT_TERMS     IN CHAR,
                                                   IN_REQUIRE_PO        IN NUMBER) AS
0
 
Randy RichPresidentAuthor Commented:
Didn't work but thanks.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
amritgillCommented:
the error is coz of the syntax error in your proc...the correct syntax is AS not IS ..
0
 
Randy RichPresidentAuthor Commented:
Here is the syntax of the new procedure, but I'm still getting the same error:


create or replace procedure INSERT_UPDATE_CUSTOMER(IN_COMPANY_ID        IN NUMBER,
                                                   IN_CUSTOMER_ID       IN NUMBER,
                                                   IN_LAST_NAME_COMPANY IN CHAR,
                                                   IN_FIRST_NAME        IN CHAR,
                                                   IN_PAYMENT_TERMS     IN CHAR,
                                                   IN_REQUIRE_PO        IN NUMBER) AS
  MY_CUSTOMER_ID NUMBER;
  COUNTER        NUMBER;
begin
  SELECT COUNT(1)
    INTO COUNTER
    FROM CUSTOMER
   WHERE COMPANY_ID = IN_COMPANY_ID AND CUSTOMER_ID = IN_CUSTOMER_ID;
  IF COUNTER = 0 THEN
    SELECT MAX(CUSTOMER_ID)
      INTO MY_CUSTOMER_ID
      FROM CUSTOMER
     WHERE COMPANY_ID = IN_COMPANY_ID;
    SELECT NVL(MY_CUSTOMER_ID, 0) + 1 INTO MY_CUSTOMER_ID FROM DUAL;
    INSERT INTO CUSTOMER
    VALUES
      (IN_COMPANY_ID,
       MY_CUSTOMER_ID,
       IN_LAST_NAME_COMPANY,
       IN_FIRST_NAME,
       IN_PAYMENT_TERMS,
       IN_REQUIRE_PO);
 
  ELSE
    UPDATE CUSTOMER
       SET LAST_NAME_COMPANY = IN_LAST_NAME_COMPANY,
           FIRST_NAME        = IN_FIRST_NAME,
           PAYMENT_TERMS     = IN_PAYMENT_TERMS,
           REQUIRE_PO        = IN_REQUIRE_PO
     WHERE COMPANY_ID = IN_COMPANY_ID AND CUSTOMER_ID = IN_CUSTOMER_ID;
  END IF;
end INSERT_UPDATE_CUSTOMER;
0
 
amritgillCommented:
does it give u any error when u create the stored proc on the oracle DB
0
 
Randy RichPresidentAuthor Commented:
That look like what I'm looking for, but do you have it in vb syntax or can anyone convert it?
0
 
Randy RichPresidentAuthor Commented:
Here is the code in vb:
        Dim myConnection As New OracleConnection
        myConnection.ConnectionString = "Data Source=CLIENTS;Persist Security Info=True;User ID=ACLINK360;Password=TINAMARIE;Unicode=True"
        Try
            myConnection.Open()
            Dim MyCommand As New OracleCommand
            MyCommand.Connection = myConnection
            MyCommand.CommandText = "INSERT_UPDATE_CUSTOMER"
            MyCommand.CommandType = CommandType.StoredProcedure
            MyCommand.Parameters.Add("IN_COMPANY_ID", OracleType.Double, 10).Value = 1
            MyCommand.Parameters.Add("IN_CUSTOMER_ID", OracleType.Double, 10).Value = 3
            MyCommand.Parameters.Add("IN_LAST_NAME_COMPANY", OracleType.VarChar, 80).Value = "Rich"
            MyCommand.Parameters.Add("IN_FIRST_NAME", OracleType.VarChar, 80).Value = "Randy"
            MyCommand.Parameters.Add("IN_PAYMENT_TERMS", OracleType.VarChar, 20).Value = ""
            MyCommand.Parameters.Add("IN_REQUIRE_PO", OracleType.Byte, 1).Value = 1
            MyCommand.ExecuteNonQuery()
        Finally
            myConnection.Close()
        End Try

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.