?
Solved

stored procedure issue with package

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

615 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