Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

owa.vc_arr help!

652458Nov 10 2008 — edited Nov 10 2008
Hey folks,

Sorry to come again with possibly another dumb question but I'm kinda struggling with how to deal with arrays in pl/sql, and in particular those of type vc_arr.

Let me try and explain what I'm trying to do.

There is a procedure, let's call it postData which is part of a package let's call it xml_api taking two arguments as so:

xml_api.postData(name_array in owa.vc_arr, value_array in owa.vc_arr)

Now what I'm trying to do is implement is piece of pl/sql in apex so I can populate 2 arrays (name_array and value_array) and send it off to this procedure. Unfortunately, this does not seem to be quite as straightforward as it would seem. To start with, I can't find any details documentation on the owa.vc_arr type which would help me understand how to populate arrays and then the bits of information I found to just populate arrays in general seem to vary in syntax from

arr := ('item1','item2');

to

arr ==> ('item1','item2');

The book I have speaks of Varrays, Associative arrays and nested tables but I'm not sure whether any of those are relevant to what I'm trying to)

I tried the following but needless to say it gave me an error:

declare

name_array owa.vc_arr;
value_array owa.vc_arr;

begin

name_array := ('AuthenticationProtocol');
value_array := ('0');

xml_api.postData(name_array, value_array);
end;

and the error

ORA-06550: line 9, column 15: PLS-00382: expression is of wrong type ORA-06550: line 9, column 1: PL/SQL: Statement ignored ORA-06550: line 10, column 16: PLS-00382: expression is of wrong type ORA-06550: line 10, column 1: PL/SQL: Statement ignored

I hope this makes any sense to anyone, and again I apologise if this seems blindingly obvious but perhaps I have a complete misconception of how oracle handles arrays and collections...

Thanks folks

Comments

94799
The collection is declared in the OWA package header in the SYS schema.
CREATE OR REPLACE PACKAGE owa
IS

(...)

   TYPE vc_arr IS TABLE OF VARCHAR2 (32000)
      INDEX BY BINARY_INTEGER;
      
(...)
      
END;
/
Population of collections is relatively straightforward but it does vary by type (nested table / varray / index-by). Suggest a read of:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm#TDDDG49000
652458
Thanks but that doesn't really tell me what type of array I should be looking at. I don't intend to populate the arrays from table values but from fixed values and session data.
94799
Hint...
TYPE vc_arr IS TABLE OF VARCHAR2 (32000)
     *INDEX BY BINARY_INTEGER*;
Edited by: padders on Nov 10, 2008 7:22 AM
Satyaki_De
Hint...
TYPE vc_arr IS TABLE OF VARCHAR2 (32000)
*INDEX BY BINARY_INTEGER*;
Why BINARY_INTEGER ?

Why Not PLS_INTEGER?

Regards.

Satyaki De.
94799
Don't have a go at me, I didn't write the OWA package ;-)

Prior to associative arrays you had to use BINARY_INTEGER anyway, although I recall that Steven Feuerstein telling me that it internally used PLS_INTEGER anyway.
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> DECLARE
  2     TYPE type_name IS TABLE OF NUMBER
  3         INDEX BY PLS_INTEGER;
  4  BEGIN
  5     NULL;
  6  END;
  7  /
   TYPE type_name IS TABLE OF NUMBER
                     *
ERROR at line 2:
ORA-06550: line 2, column 22:
PLS-00315: PL/SQL TABLE declarations must currently use binary_integer indexes
ORA-06550: line 2, column 4:
PL/SQL: Item ignored

SQL>
652458
Well I've worked out a way for the apex compiler not to complain any more so we'll see if the data actually gets passed as it should with the following code:
declare
name_array owa.vc_arr;
value_array owa.vc_arr;

begin

name_array(1) := ('AuthenticationProtocol');
name_array(2) := ('CardBrand');

value_array(1) := ('0');
value_array(2) := ('visa');

xml_api.postData(name_array, value_array);
end;
It's a bit clumsy but at least it seems to work for now
William Robertson
name_array(1) := ('AuthenticationProtocol');
You don't need brackets to assign individual values to variables, it's just 
var := value;
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 8 2008
Added on Nov 10 2008
#pl-sql
7 comments
4,511 views