• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1032
  • Last Modified:

Error passing Array to PL/SQL Package

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
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

Any suggestions?
  • 2
  • 2
1 Solution
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))
BobMinerAuthor Commented:
Thats not it.  Same error.
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.
BobMinerAuthor Commented:
k, thanks.  Rather annoying but workable.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now