Solved

stored procedure issue with package

Posted on 2013-06-28
4
453 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

Independent Software Vendors: 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

695 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