I am trying to cast a number to a varchar with the CAST function in order to be able to concatenate inside dbms_output.put_line().
Please note that you need to be able to understand collection types in Oracle PL-SQL to be able to understand my code.
Please see below code:
CREATE TYPE items_va AS VARRAY(5) OF orders_nt;
/
CREATE TYPE items_nt AS TABLE OF VARCHAR(60);
/
CREATE TYPE orders_ot AS OBJECT (order_id NUMBER, items items_nt);
/
CREATE OR REPLACE TYPE orders_nt IS TABLE OF orders_ot;
/
CREATE OR REPLACE TYPE orders_va IS VARRAY(5) OF orders_ot;
/
CREATE TABLE monthly_orders
(act_id NUMBER,
act_month VARCHAR2(8),
order_info orders_nt)
NESTED TABLE order_info STORE AS order_store
(NESTED TABLE items STORE AS item_store);
INSERT INTO monthly_orders
(act_id,
act_month,
order_info)
Values
(1,
'JANUARY',
orders_nt(
orders_ot(1, items_nt('Bike', 'Treadmill')),
orders_ot(2, items_nt('Weights'))
)
);
DECLARE
CURSOR cur_emp IS
select cast( collect(order_info) as orders_va)
from monthly_orders;
empt_t orders_va;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO empt_t;
CLOSE cur_emp;
FOR i IN empt_t.FIRST .. empt_t.LAST LOOP
dbms_output.put_line('Index counter: '|| CAST(empt_t(i).order_id as VARCHAR));
END LOOP;
END;
If my logic is correct I must see a string like this one “Index counter: 1” print out for me.
Advertisement
Answer
Guessing at your problem specification, there seem to be two problems in your code.
First, you define an object type and a nested table type – as nested table of objects. Your table column data type is not the object type you defined, but the collection of such objects (the nested table type). Finally, you define orders_va as a variable array of objects, and you attempt to collect values from the table column into this array. This is the first problem: the column values are not individual objects, they are collections of objects. If you want to collect them into the orders_va array, then the array must be an array of nested tables of objects, not an array of individual objects.
After you make this change, in the anonymous block you will not be able to reference the order_id member of an element of the array – because the array elements are nested tables of objects, they are not individual objects. To access the object members, you will need to loop over nested table elements (within the outer loop over array elements).
The two changes are in the definition of orders_va and the loop in the anonymous block.
........
CREATE OR REPLACE TYPE orders_va IS VARRAY(5) OF orders_nt; -- not orders_ot!
/
........
DECLARE
CURSOR cur_emp IS
select cast( collect(order_info) as orders_va)
from monthly_orders;
empt_t orders_va;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO empt_t;
CLOSE cur_emp;
FOR i IN empt_t.FIRST .. empt_t.LAST LOOP
for j in empt_t(i).first .. empt_t(i).last loop -- add this nested loop!
dbms_output.put_line('Index counter: '||
CAST(empt_t(i)(j).order_id as VARCHAR));
end loop;
END LOOP;
END;
/
Index counter: 1
Index counter: 2
PL/SQL procedure successfully completed.
Note that empt_t is an array of nested tables of objects; empt_t(i) is one of those nested tables, and empt_t(i)(j) is the j’th object in the nested table. i corresponds to rows in your table; j corresponds to individual objects in the nested table which is an atomic value in your table column (in row i).