Solved

PLS-00703: multiple instances of named argument in list

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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

739 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