• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3858
  • Last Modified:

PLS-00703: multiple instances of named argument in list

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
sjarmy
Asked:
sjarmy
  • 2
2 Solutions
 
daveamourCommented:
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
 
daveamourCommented:
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
 
sjarmyAuthor Commented:
I ended up recoding it all togeter and had it working by the time a received the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now