Solved

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

Posted on 2008-06-16
8
1,313 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
  • 4
  • 4
8 Comments
 
LVL 3

Expert Comment

by:amritgill
Comment Utility
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
Comment Utility
Didn't work but thanks.
0
 
LVL 3

Expert Comment

by:amritgill
Comment Utility
the error is coz of the syntax error in your proc...the correct syntax is AS not IS ..
0
 

Author Comment

by:Randy Rich
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 3

Expert Comment

by:amritgill
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
grab and show facebook pages event calendars 6 40
Oracle regular expression 6 29
null value 14 57
Different Delete Messages 7 10
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now