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
0
votes
1 answer

Convert SDO_ORDINATE_ARRAY to built-in VARRAY (to create function-based index)

Oracle 18c: I can get SDO_ORDINATE_ARRAY values from SDO_GEOMETRY objects: create table test_table (shape sdo_geometry); insert into test_table (shape) values (sdo_geometry('linestring(10 20, 30 40, 50 60)')); insert into test_table (shape) values…
User1974
  • 276
  • 1
  • 17
  • 63
0
votes
1 answer

Use negative number to extract element at end of varray list (in a custom member function)

Oracle 18c: I have a custom member function that works as expected. It lets me extract an element from a varray by index. For example, extract the first ordinate in an SDO_GEOMETRY's sdo_ordinates varray attribute using GetOrdinates(1) = 10. CREATE…
User1974
  • 276
  • 1
  • 17
  • 63
0
votes
1 answer

Get varray values as concatenated text list

In db<>fiddle for Oracle 18c: If I select a varray, then db<>fiddle returns an empty resultset, which is misleading. with data as (select sys.odcivarchar2list('a', 'b', 'c') as my_array from dual) select my_array from…
User1974
  • 276
  • 1
  • 17
  • 63
0
votes
2 answers

Replace value in SDO_ELEM_INFO_ARRAY varray

I have an existing SDO_GEOMETRY in Oracle 18c: sdo_geometry(2003, null, null, sdo_elem_info_array(1, 1003, 1), sdo_ordinate_array(665287.423,4857578.086, 676832.320,4878119.585)) In the sdo_elem_info_array varray, I want to replace the final…
User1974
  • 276
  • 1
  • 17
  • 63
0
votes
1 answer

Convert VARRAY to text (for concatenation)

I have a query that outputs a VARRAY: select a.sdo_geom.sdo_elem_info from ( select sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom from dual ) a In SQL Developer, the VARRAY gets output as…
User1974
  • 276
  • 1
  • 17
  • 63
0
votes
0 answers

Oracle : Convert NCHAR column value into Hexadecimal when fetching VARRAY in XML format?

Test CREATE OR REPLACE TYPE DTOBJECT AS OBJECT ( project_no NUMBER(2), datecol DATE, ncharcol nchar(50), nvarchar2col nvarchar2(50) ); CREATE OR REPLACE TYPE vDTOBJECT AS VARRAY(500) OF DTOBJECT; CREATE TABLE qatest.varray_datetime (id…
akg
  • 43
  • 1
  • 5
0
votes
1 answer

Add Column of Unbounded Array to SQL DB table

Is there a datatype described by an unbounded array (unlike VARRAY, where the original MAX size must be known) for SQL databases using Oracle? I'm trying to add a column to a DB table that has an array, but I don't know how big the array could…
CDA the Programmer
  • 87
  • 1
  • 6
  • 20
0
votes
1 answer

how to create a function to print the sum of all salaries of employees, maximum salary and minimum salary from a table using pl/sql function

Below is the code which I've used to store the values in a varray and finally return the varray. But I'm encountering an error at line 12 saying "Line/Col: 12/8 PLS-00103: Encountered the symbol "EMP_TYPE" when expecting one of the following: := . (…
kartik
  • 1
  • 1
0
votes
3 answers

Oracle change column datatype from varray

In Oracle database I have two schemas with two tables that mirror each other, except for one column which in both tables is varray of numbers, but in schema A it's A.VARRAY, and in schema B it's B.VARRAY. As a result I'm unable to migrate data…
user14984135
  • 1
  • 2
0
votes
1 answer

How do you search for a string in a varray in SQL (Oracle)

How would I query a varray attribute for a specific string, such as a phone number array that contains the number 013579 or begins with 0883? Also, how to I query to count the number of values in an array (e.g. check if a varray contains 3 values)
Space Turtle 84
  • 3
  • 3
0
votes
1 answer

How to execute procedure with input parametr (Varray OF INT)?

CREATE OR REPLACE TYPE list_of_int IS VARRAY(10) OF INT; CREATE OR REPLACE PROCEDURE my_procedure( in_lista in list_of_int ) AS ... exec my_procedure( [1,2,3] ); How to execute procedure with input parametr VARRAY?
malababajaga
  • 15
  • 5
0
votes
1 answer

ORACLE - select data from VARRAY OF REF Object

I am using ORACLE and want to access an Object of an array with REF. This is my Structure: create type articulo as object ( codigo_barras number, nombre varchar2(100), precio number, categoria varchar2(100), stock number …
Francisco Belda
  • 1
0
votes
2 answers

checking structure of GLobally defined Varray or nested table in PL/SQL

I am modifying a package. In one of the procedure I got below line. query_det_arr ecc_query_det_arr_type := ecc_query_det_arr_type(NULL); this ecc_query_det_arr_type is not defined anywhere inside the package. as per my understanding this must be…
user14046507
0
votes
1 answer

concatenate VArray to String and use in dynamic SQL - Oracle

I need to dynamically create a query and execute using Execute Immediate, I am facing the problem in appending the Vaaray variable. Getting Error pls-00306 wrong number or types of arguments in call to || Vaaray //Its a type number select ver_id…
Siva
  • 9,043
  • 12
  • 40
  • 63
0
votes
0 answers

VARRAY user defined element_type

For VARRAY in Oracle syntax is: CREATE [OR REPLACE ] TYPE type_name AS | IS VARRAY(max_elements) OF element_type [NOT NULL]; Can I create element_type as a record type in varray similar to nested table? If not why? For example I will have 3 as…
TBose
  • 115
  • 2
  • 10
1 2 3 4
5
6 7 8