Solved

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

Posted on 2008-06-16
8
1,318 Views
Last Modified: 2008-06-23
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

0
Comment
Question by:Randy Rich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 3

Expert Comment

by:amritgill
ID: 21799601
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
 

Author Comment

by:Randy Rich
ID: 21799623
Didn't work but thanks.
0
 
LVL 3

Expert Comment

by:amritgill
ID: 21799672
the error is coz of the syntax error in your proc...the correct syntax is AS not IS ..
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Randy Rich
ID: 21801715
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
 
LVL 3

Expert Comment

by:amritgill
ID: 21810182
does it give u any error when u create the stored proc on the oracle DB
0
 
LVL 3

Accepted Solution

by:
amritgill earned 500 total points
ID: 21810216
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
 

Author Comment

by:Randy Rich
ID: 21812591
That look like what I'm looking for, but do you have it in vb syntax or can anyone convert it?
0
 

Author Comment

by:Randy Rich
ID: 21852207
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question