From: | Peter Koukoulis <pkoukoulis(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | equivalent for md5, clobs and varchar2 list |
Date: | 2017-09-14 00:01:09 |
Message-ID: | CABpxA9j1NECAUOVXEcXXxD40QbpJUhp92baakdsc8stV73UHuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Yogesh Sharma | 2017-09-14 03:29:53 | Re: Postgres DB is failed due to pg_Xlog is continues full. |
Previous Message | Troy Hardin | 2017-09-13 14:42:18 | Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start |