Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SELECT loops with Dynamic Values from Array...

bostonmacosxOct 28 2013 — edited Oct 30 2013

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    

This post has been answered by bostonmacosx on Oct 29 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 27 2013
Added on Oct 28 2013
11 comments
1,272 views