?
Solved

stored procedure issue with package

Posted on 2013-06-28
4
Medium Priority
?
454 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 750 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 750 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 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