Stack Overflow
Stack Overflow

Questions tagged [varray]

VARRAY (variable-size arrays) is a PL/SQL datatype

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Reference:

119 questions
8
votes
1 answer

PL/SQL use VARRAY in IN CLAUSE

Is it possible to use VARRAY in IN CLAUSE of pl/sql?
Lohit
  • 891
  • 5
  • 14
  • 26
8
votes
1 answer

Oracle - How to read from VARRAY

I have a column in my table that contains a VARRAY of VARCHAR2, So I want to create a select-statement that gives me all the columns and all the objects of the VARRAY next to eachother, is there a possible way to do that? Example: CREATE TYPE arr AS…
Anton Kan
  • 123
  • 1
  • 8
8
votes
2 answers

Oracle User defined aggregate function for varray of varchar

I am trying to write some aggregate function for the varray and I get this error code when I'm trying to use it with data from the DB: ORA-00600 internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], [] [koxsihread1],…
baju
  • 511
  • 5
  • 19
6
votes
2 answers

How to add values to a VARRAY using a loop

I have a VARRAY and I want to add elements to this VARRAY by using a loop. This is what I have tried so far. DECLARE TYPE code_array_ IS VARRAY(26) OF VARCHAR2(6); codes_ code_array_; BEGIN FOR i IN 1..26 LOOP codes_(i) :=…
Dinidu Hewage
  • 2,169
  • 6
  • 40
  • 51
6
votes
1 answer

Can I slice a PL/SQL collection?

I've got a PL/SQL VArray that I'm filling with a BULK COLLECT query like this: SELECT id BULK COLLECT INTO myarray FROM aTable Now I'd like to pass a slice of this collection into another collection, something like this: newarray :=…
Thorsten
  • 12,921
  • 17
  • 60
  • 79
5
votes
1 answer

How to use SQL 'IN' (or 'ANY') operator with VARRAY in PL/SQL

My .NET code is currently using ODP.NET to call a stored procedure many times to operate on various rows in many tables. The .NET code has an array of the rows to change. Only one parameter changes in each call, and I'd like to pass the array from…
Andy Jacobs
  • 933
  • 3
  • 10
  • 18
5
votes
1 answer

PL/SQL maximum size of VARRAY

I'm trying to figure out the possible upper bound of VARRAY in PL/SQL. We sure can define VARRAY type as TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL]; Oracle documentation has this to say: Each varray is…
Vagiz Duseev
  • 702
  • 1
  • 7
  • 12
5
votes
2 answers

oracle sql varray contains an element

I have a type declaration like this: FUNCTION ... IS TYPE stati_va IS VARRAY (10000) OF varchar(1); stati stati_va; v_counter INTEGER := 0; BEGIN stati := stati_va (); --this is actually in a loop so the…
wasp256
  • 5,943
  • 12
  • 72
  • 119
5
votes
3 answers

Difference between NESTED TABLE and VARRAY

I know basic difference and uses of both. But, what I'm looking for as an answer is, why exactly VARRAY introduced ? Since, we can do same thing using NESTED TABLE what can be done using VARRAY whereas vice-versa is not possible in some scenario.…
Ravi
  • 30,829
  • 42
  • 119
  • 173
5
votes
1 answer

How to deal with VARRAYs (Oracle 9i) in JDBC?

I am trying to write a small program which has to store and retrieve values from database. The database is object relational. Here're the DDL statements : create or replace type Guy as object (name varchar(80), address varchar(80), dateOfBirth…
missingfaktor
  • 90,905
  • 62
  • 285
  • 365
4
votes
1 answer

How to count the number of elements in all Oracle varrays from table?

I have a table like this: CREATE TABLE spatial_data ( id NUMBER PRIMARY KEY, geometry SDO_GEOMETRY); SDO_GEOMETRY has a field sdo_ordinates with the following type: TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER I can get the number of…
Dmitry D
  • 760
  • 12
  • 24
4
votes
1 answer

SELECT Values from Varray ORACLE SQL

I am using apex.oracle and the error I get is [unsupported data type]. The explanation: I have a table named Playlist and I want to store an array of songs into the Songs field. For this reason I've defined a type named PlaylistSongs of varray of…
Alex
  • 125
  • 2
  • 9
4
votes
2 answers

Hibernate and Oracle VARRAYS/NESTED TABLE

Oracle supports the use of VARRAYS and NESTED TABLE data types, allowing multivalued attributes. (http://www.orafaq.com/wiki/NESTED_TABLE) I am currently using Hibernate 3 as my ORM framework, but I can't see how I can map Hibernate to a NESTED…
TBW
  • 128
  • 1
  • 9
3
votes
1 answer

How to define a dictionary like structure in Oracle PL SQL?

How to define in PL/SQL the following structure: A list of strings which contains multiple rows. Example: 'User A' --> (1) --> 1 (2) --> 2 'User B' --> (1) …
Stef Heyenrath
  • 9,335
  • 12
  • 66
  • 121
3
votes
1 answer

Is there something like Python's list zip function for collections in Oracle SQL?

While refactoring some complex PL/SQL packages for performance, several times I had the following situation: Input: Two PL/SQL VARRAYs A and B or TABLEs of the same length (for example, SYS.ODCIVarchar2List), e.g. A := SYS.ODCIVarchar2List('Summer',…
hvb
  • 2,484
  • 1
  • 10
  • 13
1
2 3 4 5 6 7 8