Solved

PLS-00703: multiple instances of named argument in list

Posted on 2009-04-06
3
2,609 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 500 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 500 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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
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.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

628 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