[GENERAL] equivalent for md5, clobs and varchar2 list

[Peter Koukoulis]

Hi

is there an equivalent of a odcivarchar2list in PostgreSQL. I'm running the
code in Oracle 11gr2.
I know that the equivalent of  dbms_crypto. hash(  " " ,2) is md5(), but I
cannot find anything similar to odcivarchar2list?
I am constrained by not being able to declare types in the database. I am
using 9.6.3 on Debian.

drop table fruit;
create table fruit (id number, descr varchar2(20), expire_date date, price
number(4,2));
insert into fruit values (1, 'apple', sysdate + 10, 12.22);
insert into fruit values (2, 'banana', sysdate + 12, 0.22);
commit;

set serveroutput on
declare
  l_clob clob;
  l_list sys.odcivarchar2list;
  l_md5  clob;
begin

-- MD5 result
  select cast(multiset
          (select
lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(id||descr||to_char(expire_date,
'yyyymmdd')||to_char(price,'FM99.99')),2))) as hash_val
           from ( select id,descr,expire_date,price from fruit )
           )  as sys.odcivarchar2list)
  into l_list
  from dual;

  for t in (
 select distinct column_value as val from table(l_list) ) loop
    l_clob := l_clob || t.val;
  end loop;

  select lower(rawtohex(dbms_crypto.hash(utl_raw.cast_to_raw(l_clob ),2)))
  into l_md5
  from dual;

  dbms_output.put_line(l_md5);

end;
/

The output is cba90cee4a9e710cd807331eb91a0143

Thanks
P