Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PLS-00703: multiple instances of named argument in list

Posted on 2009-04-06
3
Medium Priority
?
2,885 Views
Last Modified: 2013-11-19
I am getting this error when caling an Oracle stored procedure. The call is inside a foreach (DataRow dataRow in tempTable.Rows) loop and the first record updates fine, but on the second call, I get the error so I'm guessing I'm not releasing something. Can someone suggect something for this?

Remember its blowing on the second record.
foreach (DataRow dataRow in tempTable.Rows)
 {
      DO STUFF ......
 
    cmd.CommandText = "USER_TABLE_UPDATE";
    cmd.CommandType = CommandType.StoredProcedure;
     OracleParameter parameter0 = new OracleParameter();
     parameter0.ParameterName = "RESULT";
     parameter0.OracleType = OracleType.VarChar;
     parameter0.Direction = ParameterDirection.Output;
 
    parameter0.Size = 30;
    cmd.Parameters.Add(parameter0);
    cmd.Parameters.Add(new OracleParameter("pACTION", OracleType.VarChar)).Value = "update";
   cmd.Parameters.Add(new OracleParameter("pUSERID", OracleType.VarChar)).Value = userid;
   cmd.Parameters.Add(new OracleParameter("pFIRST_NAME", OracleType.VarChar)).Value = fname;
    cmd.Parameters.Add(new OracleParameter("pLAST_NAME", OracleType.VarChar)).Value = lname;
    cmd.Parameters.Add(new OracleParameter("pEMAIL", OracleType.VarChar)).Value = email;
    cmd.Parameters.Add(new OracleParameter("pPHONE", OracleType.VarChar)).Value = phone;
    cmd.Parameters.Add(new OracleParameter("pJOB_LEVEL_ID", OracleType.VarChar)).Value = job_lev;
    cmd.Parameters.Add(new OracleParameter("pMANAGER_DN", OracleType.VarChar)).Value = mgr_dn;
   int int_ins = cmd.ExecuteNonQuery();
 
TOP OF STORED PROCEDURE
 
CREATE OR REPLACE PROCEDURE OISWEB.USER_TABLE_UPDATE(RESULT	 OUT VARCHAR2,
 pACTION   VARCHAR2,
pUSERID 	   USERS.USER_ID %TYPE,
pFIRST_NAME  VARCHAR2,
pLAST_NAME   VARCHAR2,
pEMAIL	   VARCHAR2,
 pPHONE	   VARCHAR2,
 pJOB_LEVEL_ID	 USERS_EXT.JOB_LEVEL_ID%TYPE,
 pMANAGER_DN	 USERS_EXT.MANAGER_DN%TYPE
)

Open in new window

0
Comment
Question by:sjarmy
[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
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
daveamour earned 1500 total points
ID: 24084441
Looks to me like you are adding an extra parameter each time so see if there is a way to clear the parameters eg:
foreach (DataRow dataRow in tempTable.Rows)
 {
      DO STUFF ......
 
    cmd.CommandText = "USER_TABLE_UPDATE";
    cmd.CommandType = CommandType.StoredProcedure;
    OracleParameter parameter0 = new OracleParameter();
    parameter0.ParameterName = "RESULT";
    parameter0.OracleType = OracleType.VarChar;
    parameter0.Direction = ParameterDirection.Output;
    //Clear the parameters collection here,
 
    parameter0.Size = 30;
    cmd.Parameters.Add(parameter0);
 
Aso you can move the following outside the loop - no need to do it each time:
    cmd.CommandText = "USER_TABLE_UPDATE";
    cmd.CommandType = CommandType.StoredProcedure;

0
 
LVL 19

Assisted Solution

by:daveamour
daveamour earned 1500 total points
ID: 24084452
I had a quick look and the SqlCommand has a clear method fro the parameters so this probably does too but if not let me know an dI'll show you another way.
0
 

Author Closing Comment

by:sjarmy
ID: 31577738
I ended up recoding it all togeter and had it working by the time a received the solution.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

722 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