SQL & PL/SQL

create or replace package pkg
as
  type vArray is varray(3) of varchar2(100);
  myArray vArray;
  function getArray(pIndex in integer) return varchar2;
end;
/
create or replace package body pkg
as
  function getArray(pIndex in integer) return varchar2
  is
  begin
    return myArray(pIndex);
  end;
begin
  myArray := vArray();
  myArray.extend;
  myArray(1) := 'karthick';
  myArray.extend;
  myArray(2) := 'oracle';
  myArray.extend;
  myArray(3) := 'nothing';
exception when others then 
  dbms_output.put_line(sqlerrm);
end;
/
select pkg.getArray(1), pkg.getArray(2), pkg.getArray(3)
  from dual
/

select
r.company_code,
r.date,
udm as d5_udm,
ddm as d5_ddm,
tr as d5_tr,
from (
select
q.company_code,
q.date,
(select avg(abs(highest_rate-opening_rate)) udm,
          avg(abs(opening_rate-lowest_rate)) ddm,
          avg(highest_rate-lowest_rate) tr
 from (select lowest_rate, highest_rate, opening_rate 
        from hkex.quotation q1 
        where not q1.is_trade_suspend = '0' -- as below
        and q1.company_code=q.company_code 
        and q1.date<=q.date 
        order by q1.date desc limit 5) t) as d5   -- not sure what limit 5 does (possibly first 5 rows?)
from hkex.quotation q
where q.is_trade_suspend = '0'  -- Oracle does not support boolean data type for columns so change col to varchar2(1) with '0' and '1' for false and true
) r;