asked on

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

amritgill

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

Randy Rich

ASKER

Didn't work but thanks.

amritgill

the error is coz of the syntax error in your proc...the correct syntax is AS not IS ..

Randy Rich

ASKER

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;

amritgill

does it give u any error when u create the stored proc on the oracle DB

ASKER CERTIFIED SOLUTION

amritgill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Rich

ASKER

That look like what I'm looking for, but do you have it in vb syntax or can anyone convert it?

Randy Rich

ASKER

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