Solved

PLS-00703: multiple instances of named argument in list

Posted on 2009-04-06
3
1,951 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
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 …
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

705 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

20 Experts available now in Live!

Get 1:1 Help Now