Solved

PLS-00703: multiple instances of named argument in list

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
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 …

920 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

16 Experts available now in Live!

Get 1:1 Help Now