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

Can I accept values in a VARRAY in PL/SQL directly from the user?

I am looking for something like below for the purpose of accepting array variables from the user itself, I know the below code is wrong, but it is just for the sake of giving the idea of my requirement. DECLARE type names_array IS VARRAY(5) OF…
user2555148
  • 19
  • 2
  • 5
0
votes
1 answer

How to realize that an Oracle SQL OBJECT VARRAY elements using as foreign key

I created an OBJECT type and a VARRAY: CREATE TYPE termek_adat IS OBJECT( termek_id number(3), termek_db number(3)); / CREATE type TERMEK_INF is VARRAY(10000) OF termek_adat; I created a table: CREATE TABLE Elad( elad_id number(3), termek_i…
user1839790
  • 59
  • 5
0
votes
1 answer

How do I select an array from the results of a query?

I've created a VARRAY type: CREATE TYPE my_array AS varray(1024) OF VARCHAR2(512); I know I can create an instance of my_array from a list of values using my_array(): SELECT my_array('foo', 'bar'); But is there a way to create an instance from the…
theory
  • 9,178
  • 10
  • 59
  • 129
0
votes
2 answers

Invoke Oracle stored procedure with param input varying array from C#

I have a stored procedure in Oracle which receives an input parameter of type varchar2 varying array. The procedure works and if you invoke it from SQL, what I need is called from C#. My script is this: CREATE OR REPLACE PROCEDURE…
ronpy
  • 262
  • 1
  • 13
0
votes
2 answers

Drawback of large LIMIT of VAR-types

Is there any drawback (except allowing the value or array to grow too large) to setting the max integer size of a VARCHAR or VARRAY to a value significantly larger than actually necessary (or setting it to the max size allowed)?
Steven
  • 13,501
  • 27
  • 102
  • 146
-1
votes
2 answers

Convert SDO_ORDINATE_ARRAY to string using a function (for a function-based index)

Oracle 18c: I have 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 (sdo_geometry('linestring(70 80,…
User1974
  • 276
  • 1
  • 17
  • 63
-1
votes
1 answer

Use Query Result Cache in query with varray

Test #1: I have a query that successfully invokes the Query Result Cache hint: /*+ result_cache */. with data (id) as ( select 1 from dual union all select 2 from dual ) select /*+ result_cache */ id from data Line 2 in the explain plan…
User1974
  • 276
  • 1
  • 17
  • 63
-1
votes
1 answer

Extract vertices from SDO_GEOMETRY lines and store as points in SDO_GEOMETRY_ARRAY

Oracle 18c: Using this sample data: with data (asset_id, shape) as ( select 100, sdo_geometry('linestring (10 20, 30 40)') from dual union all select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)') from…
User1974
  • 276
  • 1
  • 17
  • 63
-1
votes
1 answer

Collections not working in PLSQL

By below code is not working for cursor on PLSQL. I receive an error message. declare type abc is varray(10) of number; cursor x is select Empno from emp where rownum <10; a abc; counter number :=1; begin …
Nigel
  • 47
  • 1
  • 2
  • 8
-1
votes
1 answer

How can i select an element from a Varray with the like Keyword

can somebody please help me with how to select a phone array in sql statement where the phone is an array Create type phone as varray (3) of varchar2(13); select name, phone from person_table where value(p) is of (type) and ( phone starts with…
Timmy Barr
  • 11
  • 1
  • 3
-1
votes
1 answer

Query Maximum VARRAY value

How can I query for the maximum value inside a varray? create type myWave as varray(10) of int; create table foo (id number, yVals myWave); insert into foo values (1, myWave(1, 8, 5)); insert into foo values (2, myWave(1, 3, 4)); insert into foo…
Steven
  • 13,501
  • 27
  • 102
  • 146
-1
votes
1 answer

How to use varray in plsql

Below code block is throwing error.. DECLARE TYPE dbadaptor IS VARRAY (9) OF VARCHAR2(20); min_date DATE; max_date DATE; db dbadaptor := dbadaptor('wsf_ds_11765_c', 'wsf_ds_11750_c', 'wsf_ds_11756_c',…
user1452037
  • 13
  • 1
  • 2
-1
votes
1 answer

function return varray error

I keep getting a error when i run this code, What wrong with the code? create or replace function f_vars(line varchar2,delimit varchar2 default ',') return line_type is type line_type is varray(1000) of varchar2(3000); sline varchar2…
Jamil Smith
  • 77
  • 1
  • 2
  • 6
-2
votes
1 answer

getting ORA-06550 error

Declare type v_year_arr is varray(100) of emps%rowtype; v_deptid emps.department_id%type :=90; v_rem1 number(5,2); v_rem2 number(5,2); v_rem3 number(5,2); v_year v_year_arr; Begin select * bulk collect into v_year from emps where…
user2664165
  • 1
1 2 3 4 5 6 7
8