[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Error passing Array to PL/SQL Package

Posted on 2003-12-02
4
Medium Priority
?
1,025 Views
Last Modified: 2007-12-19
Trying to pass an array of stings into a PL/SQL package. I get the following error:

Error 3001
Arguments are of the wrong type, out of acceptable range or in conflict with one another.

Package Declaration:
Create or replace WriteSchedByArray
IS
TYPE my_Array IS varray(400) OF VARCHAR2(200);
PROCEDURE MassWriteSched(
                          SchedArray IN my_Array,
                          PercentDone OUT NUMBER
                          );
END WriteSchedByArray;


VB Code::: (ConnO is Open)
  Dim p1 As New ADODB.Parameter
  Dim p2 As New ADODB.Parameter
  Dim cCmd As ADODB.Command
  Dim y As Integer
 y = -1
   Set cCmd = New ADODB.Command
    With cCmd
      .CommandText = "schedule.writeschedbyarray.massWritesched"
      .CommandType = adCmdStoredProc
       .ActiveConnection = ConnO

*** Error is here ***
      Set p1 = .CreateParameter("ary", adArray, adParamInput, 400, arySchd(0))
*** Error is here ***

       Set p2 = .CreateParameter("PercentDone", adInteger, adParamOutput)
       .Parameters.Append p1
       .Parameters.Append p2
       .Execute


Any suggestions?
Bob
0
Comment
Question by:BobMiner
[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
  • 2
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9860947
I think Oracle varray index start with 1 , not 0 , different from all other programming languages.

try to change that line to this:
Set p1 = .CreateParameter("ary", adArray, adParamInput, 400, arySchd(1))
0
 

Author Comment

by:BobMiner
ID: 9861385
Thats not it.  Same error.
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 2000 total points
ID: 9861467
BobMiner:
I think this problem popped up before in this forum.
I found out that Passing VB array to pl/sql stored procedure does not appear to be supported.
The workaround is pass  a comma-delimited string to pl/sql stored procedure and inside pl/sql stored procedure, of course, you have to have a parsing mechanism to handle it.

you can search this site to find the solution.
0
 

Author Comment

by:BobMiner
ID: 9861849
k, thanks.  Rather annoying but workable.
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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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