Solved

stored procedure issue with package

Posted on 2013-06-28
4
445 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
  • 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 76

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

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

Suggested Solutions

Title # Comments Views Activity
scanning dentists xray (the small ones) 3 44
Entity Framework 7 31
Vb. Net application freezes 9 30
Close form "before" open 3 24
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

910 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

22 Experts available now in Live!

Get 1:1 Help Now