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

How to pass the list of parameters with TYPE VARRAY of VARCHAR2 to the PL-SQL stored procedure and access list of parameters in loop

I want to send parameter list with TYPE VARRY of VARCHAR2 to a stored procedure in oracle and access each parameter in loop. Any help will be wonderful. Thank you!
Sneha
  • 45
  • 12
0
votes
1 answer

How to pass list of table names as parameter in stored procedure in Oracle SQL Developer? How to use PLSQL VARRAY or nested table?

I have a code which accepts a table name as parameter and creates subpartitions on that table name for the partitions. My table is partitioned on list of source system codes and range subpartitoned on monthly basis. Question: If suppose I have…
Sneha
  • 45
  • 12
0
votes
1 answer

searching employees records by getting index input by user

I have a employees table having less than 15 records , Table has below three columns EmpId Emp_name department I want to get the index value by user. According to this index value the plsql block of code that takes the first name value from the…
Adeel Aslam
  • 1,285
  • 10
  • 36
  • 69
0
votes
2 answers

Executing a query using a Collection as argument in Oracle

Is there any way I can execute immediate a query with a collection as its argument. I want to define a type as type my_type as table of number and then execute a dynamic query (created by concating proper parts) using execute immediate QUERY using…
Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69
0
votes
1 answer

Varray data type is not getting mapped with .net core entity using Devart oracle dot connect 9

I am trying to fetch data from a table(oracle db 12c) using Devart EFCore 9.8 and Oracle dot connect trial license in MSEFCore 2.2.4. But the VARRAY column is not getting mapped. It's throwing an exception - System.InvalidOperationException: The…
Ajijul
  • 1
  • 3
0
votes
1 answer

Concurrency level of VARRAY vs Nested tables

I'd like to know what would have a better concurrency level, VARRAY or nested tables. Is it really true that VARRAY only need one input/output operation ot load the collection? It's for a reservations systems. Regards,
llinasenc
  • 11
  • 1
0
votes
1 answer

dynamic allocation of values in Varray in PLSQL

I want to dynamically allocate the values of a VARRAY. So, for example, I declared a varray of size 5. In that varray I want to dynamically insert the 5 values from user input. How to do this ?
Raj kumar Mishra
  • 21
  • 1
  • 5
0
votes
1 answer

Getting ORA-06532: Subscript outside of limit

I have created varray collection type inorder to get a serv_item_id column value from serv_item table. But while executing im getting subsript outside of limit error. declare type t1 is varray(1000) of serv_item%rowtype; v_t1 t1:=t1(); n number…
Raja sekar
  • 79
  • 2
  • 11
0
votes
2 answers

Getting error while select 20 record for first time in FOR loop and rest for anther time in oracle

In Oracle, total 36 record i am getting by using PL/SQL variable type v_arr is varray(25) of varchar2(20). Inside the PL/SQL block i am using 2 different FOR loop for 2 different operation. in the 1st time i am using For i in 1 .. v_arr.count(20)…
user8487380
  • 156
  • 3
  • 18
0
votes
2 answers

How to manipulate VARRAYS in sql (oracle)?

Supposing i am using a table person, and persons might have multiple last names, so that attribute should be a varray of 3 elements for example (it's not about where to store last names), here is a simple sql for creating the type last name, the…
BHA Bilel
  • 331
  • 5
  • 14
0
votes
1 answer

How to count number of items in a specific type in a table in sql?

I have a table named Movie, with actors attribute. actors_type is specific and looks like this: GEORGE.ACTOR_TYPE('Clint Eastwood', 'Christopher Carley', 'Bee Vang', 'Ahney Her') ACTOR_TYPE is implemented as a varray(5) of varchar(20) the query I…
Samantha
  • 71
  • 1
  • 7
0
votes
1 answer

Writing an SQL statement that references a nested VARRAY

I am trying to write an SQL statement that queries a column actors (defined as VARRAY(5)) in a table named movie without using PL/SQL, to answer the question: List any actors that appear in multiple movies, showing the movie title and actor select…
J Tran
  • 3
  • 1
0
votes
1 answer

not able to delete an element from array

PLS-00306: wrong number or types of arguments in call to 'DELETE' create or replace PACKAGE BODY MYPACKAGE AS PROCEDURE LeaveDates4 ( I_STDATE IN DATE, I_ENDDATE IN DATE, O_DATES OUT DateArray ) AS …
Shilpa M B
  • 13
  • 4
0
votes
2 answers

Access a varray type defined inside a package using oci_new_collection

Hello I am trying to pass in varrays from PHP to Oracle. I am using OCI8 and have earlier worked with varrays as arguments in stored procedures, and on compilation the types of those varrays are created. So while making collection instance on the…
macha
  • 7,337
  • 19
  • 62
  • 84
0
votes
1 answer

Error when insert into table with a varray column through package procedure

PROBLEM IS HOW TO INSERT INTO VARRAY- I HAVE A PROCEDURE WHERE I AM DECLARE IN PARAMETER AS TABLE DATATYPE IN PROCEDURE I USE INSERT STATEMENT TO INSERT INTO TABLE. I AM FACING PROBLEM TO INSERT INTO VARRAY ALREADY I AM A DECLARING A VARY TYPE IN…
Saumya Ranjan
  • 75
  • 1
  • 3
  • 11
1 2 3 4 5
6
7 8