So I've declared an array:
type array_cols1 is varray(7) of varchar2(100);
type array_cols2 is varray(7) of varchar2(100);
ora_names array_cols1:= array_cols1('VM_HOSTS_NUM','VM_NUMBER','VM_PHYS_MEM','VM_VIRT_MEM','VM_CPU_COUNT', 'VM_TOTAL_DISK','VM_PROVISIONED_DISK');
type array_name is varray(7) of varchar2(100);
common_names array_cols2 := array_cols2('HOST NUMBER','VM NUMBER','PHYSICAL MEMORY','VIRUTAL MEMORY','CPU COUNT', 'TOTAL DISK','PROVISIONED DISK');
arlength integer := ora_names.count;
Then I want to loop through them:
for i in 1 .. arlength LOOP
chart_series_data := chart_series_data|| ' <series name="'||common_names(i) ||'" type="Line" color="0x1D8BD1" >'||chr(10);
for c1 in (SELECT VM_REPORT_DATE LABEL, ora_names(i) THEVALUE from VM_CORE where VM_REPORT_DATE between add_months(SYSDATE,-24) and SYSDATE and lower(VM_DCNAME)=lower(:WHICHCHART))
loop
chart_series_data := chart_series_data || '<point name="'||c1.LABEL||'" ';
chart_series_data := chart_series_data || 'y="'||c1.THEVALUE||'"></point> '||chr(10);
end loop;
chart_series_data := chart_series_data|| ' </series>'||chr(10);
end loop;
The issus is when I go retrieve the c1.THEVALUE I'm getting back the literal from the array and not the column value. Is there any way to tell the SELECT that the vaiarble I'm plugging in is a column name and not a literal.
Thanks
Rob