Solved

stored procedure issue with package

Posted on 2013-06-28
4
452 Views
Last Modified: 2013-07-25
issue : if i am calling stored procedure from  "Package1.PROCEDURE1"; then its works.but if i am using only storedprocedure then its not works
(if put procedure in package its work but without package its not works when i passed array from c# code.
 Wrong number or type of arguments

try

            {

                objCommand = new OracleCommand();
             
                objCommand.Connection = GetConnectionString();

                    objCommand.CommandText = "Package1.PROCEDURE1";

                      objCommand.CommandType = CommandType.StoredProcedure;

                OracleParameter param =new OracleParameter();

                param.OracleDbType=OracleDbType.Varchar2;

                param.CollectionType=OracleCollectionType.PLSQLAssociativeArray;

                param.Value=new string [2] {"4","3"};

                param.Size=20;

                 objCommand.Parameters.Add(param);

                //Output parameter to the stored procedure

                //objCommand.Parameters.Add("po_output_cnt", OracleDbType.Varchar2, 10).Direction = ParameterDirection.Output;

 
                objCommand.ExecuteNonQuery();

                 
                //Close the database connection

                CloseConnection();

 

                //converting return parameter into boolean.

                //if (string.IsNullOrWhiteSpace(returnStatus) || returnStatus.Equals("0"))

                    //returnValue = false;

                //else

                    //returnValue = true;

 

            }

--------------------
Prcodeure
create or replace package package1
as
type name is table of varchar2(20) index by pls_integer;
PROCEDURE PROCEDURE1(param in name);
end package1;
 
create or replace package body package1 as
PROCEDURE PROCEDURE1(param in name) is
BEGIN
for i in param.first..param.last loop
insert into jobs(jobid) values(param(i));
end loop;
END PROCEDURE1;
end package1;
 
Error :- Wrong number or type of arguments
0
Comment
Question by:nicemanish
[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
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39283886
It should not be that way unless some other code changes were done for the standalone procedure and the packaged procedure code.
0
 

Author Comment

by:nicemanish
ID: 39283902
could you plz more elobarate ....if put procedure in package its work but without package its not works when i passed array from c# code.
how can i achive this functionality if passes array and using only stored procedure
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 250 total points
ID: 39283906
I am not a C# code expert but the oracle standalone procedure/packaged procedure if it has 2 arguments, then 2 arguments need to be passed from C# code.

I am not sure how it can work for packaged procedure but not for standalone procedure but wait for some other  experts suggestions.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39284049
With the procedure declared as stand-alone did you create the TYPE as stand-alone as well?

You might try with the generic pre-defined list:

create or replace PROCEDURE PROCEDURE1(param in   sys.odcivarchar2list) is
BEGIN
for i in param.first..param.last loop
insert into jobs(jobid) values(param(i));
end loop;
END;
/

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
c# DateTime Format validation 4 70
locate sql commands in C# visual studio Project 6 45
Header Font Size in Grid View 6 27
asp.net repeater 2 17
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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