Anonymous TABLE or VARRAY type in Oracle
SQL table and varray types
An interesting solution was given by user APC here. For future readers of this question, it may be interesting to see that this query provides what I'm really interested in:
select coll_type, elem_type_name, type_name, length, upper_bound
from all_coll_types
where owner = 'SYS'
and elem_type_name IN ('VARCHAR2', 'NUMBER')
order by coll_type, elem_type_name, type_name;
Resulting in (in Oracle 11g):
+-------------+--------------+----------------------+------+-----------+
|COLL_TYPE |ELEM_TYPE_NAME|TYPE_NAME |LENGTH|UPPER_BOUND|
+-------------+--------------+----------------------+------+-----------+
|TABLE |NUMBER |KU$_OBJNUMSET |{null}| {null}|
|TABLE |NUMBER |KU$_XMLCOLSET_T |{null}| {null}|
|TABLE |NUMBER |ORA_MINING_NUMBER_NT |{null}| {null}|
|TABLE |VARCHAR2 |DBMS_AW$_COLUMNLIST_T | 100| {null}|
|TABLE |VARCHAR2 |DBMS_DEBUG_VC2COLL | 1000| {null}|
|TABLE |VARCHAR2 |HSBLKNAMLST | 30| {null}|
|TABLE |VARCHAR2 |KU$_VCNT | 4000| {null}|
|TABLE |VARCHAR2 |ORA_MINING_VARCHAR2_NT| 4000| {null}|
|VARYING ARRAY|NUMBER |AWRRPT_NUM_ARY |{null}| 30|
|VARYING ARRAY|NUMBER |JDM_NUM_VALS |{null}| 999|
|VARYING ARRAY|NUMBER |ODCIGRANULELIST |{null}| 65535|
|VARYING ARRAY|NUMBER |ODCINUMBERLIST |{null}| 32767|
|VARYING ARRAY|NUMBER |SQL_OBJECTS |{null}| 2000|
|VARYING ARRAY|NUMBER |TABLESPACE_LIST |{null}| 64000|
|VARYING ARRAY|VARCHAR2 |AQ$_JMS_NAMEARRAY | 200| 1024|
|VARYING ARRAY|VARCHAR2 |AQ$_MIDARRAY | 32| 1024|
|VARYING ARRAY|VARCHAR2 |AWRRPT_VCH_ARY | 80| 30|
|VARYING ARRAY|VARCHAR2 |DBMSOUTPUT_LINESARRAY | 32767| 2147483647|
|VARYING ARRAY|VARCHAR2 |DBMS_XS_ROLELIST | 1024| 4096|
|VARYING ARRAY|VARCHAR2 |FLASHBACKTBLIST | 30| 100|
|VARYING ARRAY|VARCHAR2 |HSBLKVALARY | 4000| 250|
|VARYING ARRAY|VARCHAR2 |JDM_ATTR_NAMES | 60| 999|
|VARYING ARRAY|VARCHAR2 |JDM_STR_VALS | 4000| 999|
|VARYING ARRAY|VARCHAR2 |KU$_DROPCOLLIST | 4000| 1000|
|VARYING ARRAY|VARCHAR2 |KUPC$_LOBPIECES | 4000| 4000|
|VARYING ARRAY|VARCHAR2 |ODCIRIDLIST | 5072| 32767|
|VARYING ARRAY|VARCHAR2 |ODCIVARCHAR2LIST | 4000| 32767|
|VARYING ARRAY|VARCHAR2 |RE$NAME_ARRAY | 30| 1024|
|VARYING ARRAY|VARCHAR2 |RE$RULE_LIST | 65| 1024|
|VARYING ARRAY|VARCHAR2 |SQLPROF_ATTR | 500| 2000|
|VARYING ARRAY|VARCHAR2 |TXNAME_ARRAY | 256| 100|
+-------------+--------------+----------------------+------+-----------+
It looks as though ORA_MINING_NUMBER_NT and ORA_MINING_VARCHAR2_NT will be the best match for my needs.
PL/SQL indexed array types
If using Oracle 12c and PL/SQL, there's also the possibility to use any of the DBMS_SQL types, which can be unnested using the TABLE(..) constructor. There are:
DBMS_SQL.CLOB_TABLEDBMS_SQL.BINARY_FLOAT_TABLEDBMS_SQL.BINARY_DOUBLE_TABLEDBMS_SQL.BLOB_TABLEDBMS_SQL.BFILE_TABLEDBMS_SQL.DATE_TABLEDBMS_SQL.NUMBER_TABLEDBMS_SQL.UROWID_TABLEDBMS_SQL.VARCHAR2_TABLEDBMS_SQL.TIME_TABLEDBMS_SQL.TIME_WITH_TIME_ZONE_TABLEDBMS_SQL.TIMESTAMP_TABLEDBMS_SQL.TIMESTAMP_WITH_LTZ_TABLEDBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLEDBMS_SQL.INTERVAL_DAY_TO_SECOND_TABLEDBMS_SQL.INTERVAL_YEAR_TO_MONTH_TABLE
Give alias to anonymous column of global collection type in SELECT FROM TABLE(collection)
Alright, found a solution. It could be done with column_value as a name of column:
with t_cur_ids as (
select column_value as id from table(atn_cur_ids);
)
select text from t_texts
join t_cur_ids on t_texts.id = t_cur_ids.id
How to update a varray type within a table with a simple update statement?
I don't believe you can update a single object's value within a varray from plain SQL, as there is no way to reference the varray index. (The link Alessandro Rossi posted seems to support this, though not necessarily for that reason). I'd be interested to be proven wrong though, of course.
I know you aren't keen on a PL/SQL approach but if you do have to then you could do this to just update that value:
declare
l_object_list my_object_varray;
cursor c is
select l.id, l.object_list, t.*
from my_object_table l,
table(l.object_list) t
where t.value1 = 10
for update of l.object_list;
begin
for r in c loop
l_object_list := r.object_list;
for i in 1..l_object_list.count loop
if l_object_list(i).value1 = 10 then
l_object_list(i).value2 := 'obj 4 upd';
end if;
end loop;
update my_object_table
set object_list = l_object_list
where current of c;
end loop;
end;
/
anonymous block completed
select l.id, t.* from my_object_table l, table(l.object_list) t;
ID VALUE1 VALUE2 VALUE3
---------- ---------- ---------- ----------
1 1 object 1 10
1 2 object 2 20
1 3 object 3 30
2 10 obj 4 upd 10
2 20 object 5 20
2 30 object 6 30
SQL Fiddle.
If you're updating other things as well then you might prefer a function that returns the object list with the relevant value updated:
create or replace function get_updated_varray(p_object_list my_object_varray,
p_value1 number, p_new_value2 varchar2)
return my_object_varray as
l_object_list my_object_varray;
begin
l_object_list := p_object_list;
for i in 1..l_object_list.count loop
if l_object_list(i).value1 = p_value1 then
l_object_list(i).value2 := p_new_value2;
end if;
end loop;
return l_object_list;
end;
/
Then call that as part of an update; but you still can't update your in-line view directly:
update (
select l.id, l.object_list
from my_object_table l, table(l.object_list) t
where t.value1 = 10
)
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd');
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
You need to update based on relevant the ID(s):
update my_object_table
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd')
where id in (
select l.id
from my_object_table l, table(l.object_list) t
where t.value1 = 10
);
1 rows updated.
select l.id, t.* from my_object_table l, table(l.object_list) t;
ID VALUE1 VALUE2 VALUE3
---------- ---------- ---------- ----------
1 1 object 1 10
1 2 object 2 20
1 3 object 3 30
2 10 obj 4 upd 10
2 20 object 5 20
2 30 object 6 30
SQL Fiddle.
If you wanted to hide the complexity even further you could create a view with an instead-of trigger that calls the function:
create view my_object_view as
select l.id, t.* from my_object_table l, table(l.object_list) t
/
create or replace trigger my_object_view_trigger
instead of update on my_object_view
begin
update my_object_table
set object_list = get_updated_varray(object_list, :old.value1, :new.value2)
where id = :old.id;
end;
/
Then the update is pretty much what you wanted, superficially at least:
update my_object_view
set value2 = 'obj 4 upd'
where value1 = 10;
1 rows updated.
select * from my_object_view;
ID VALUE1 VALUE2 VALUE3
---------- ---------- ---------- ----------
1 1 object 1 10
1 2 object 2 20
1 3 object 3 30
2 10 obj 4 upd 10
2 20 object 5 20
2 30 object 6 30
SQL Fiddle.
Casting an Oracle Table array types to a single table array type
Declare then to be the same type from the outset:
DECLARE
TYPE Varchar2_Array IS TABLE OF VARCHAR2(80);
array1 Varchar2_Array := Varchar2_Array('a', 'b', 'c', 'd', 'e', 'k', 'f', 'g', 'h', 'i');
array2 Varchar2_Array := Varchar2_Array('c', 'd', 'f', 'h', 'i', 'j', 'm');
array3 Varchar2_Array := Varchar2_Array('j', 'l', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z');
array4 Varchar2_Array;
BEGIN
array4 := array1 MULTISET UNION DISTINCT array2 MULTISET UNION DISTINCT array3;
END;
how to print out the whole table using anonymous block in pl sql?
try with something like this.
SET SERVEROUTPUT ON
BEGIN
-- A PL/SQL cursor
FOR cursor1 IN (SELECT * FROM table1)
LOOP
DBMS_OUTPUT.PUT_LINE('Column 1 = ' || cursor1.column1 ||
', Column 2 = ' || cursor1.column2);
END LOOP;
END;
/
Related Topics
SQL Comma-Separated Row with Group by Clause
Slow Simple Update Query on Postgresql Database with 3 Million Rows
Get Month Name from Date in Oracle
Sql, How to Concatenate Results
Query a Database Based on Result of Query from Another Database
How to Import .SQL Files into SQLite 3
How to Get Return Value of a Stored Procedure
How to Edit a Table in Order to Enable Cascade Delete
Find the Real Column Name of an Alias Used in a View
Why Do Null Values Come First When Ordering Desc in a Postgresql Query
How to Determine the Status of a Job
Get Month from Datetime in SQLite
Postgresql Generate Sequence with No Gap
Display Names of All Constraints for a Table in Oracle SQL
How to Compare the Current Row with Next and Previous Row in Postgresql
How to Create a Pivot Query in SQL Server Without Aggregate Function