equivalent for md5, clobs and varchar2 list

Started by Peter Koukoulisover 8 years ago2 messagesgeneral
Jump to latest
#1Peter Koukoulis
pkoukoulis@gmail.com

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

#2George Neuner
gneuner2@comcast.net
In reply to: Peter Koukoulis (#1)
Re: equivalent for md5, clobs and varchar2 list

On Thu, 14 Sep 2017 00:01:09 +0000, Peter Koukoulis
<pkoukoulis@gmail.com> wrote:

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.

MD5 would be 'char(34)' - MD5 produces a 32 character result, and
Postrgesql adds a 2 character tag.
https://www.postgresql.org/docs/current/static/pgcrypto.html

CLOB would be be 'text', or equivalently, 'varchar' without a length
qualifier. Postgresql does not distinguish character LOBs as a
separate type, and 'text' is just shorthand for unlimited 'varchar'.
https://www.postgresql.org/docs/current/static/datatype-character.html

I had to look up odcivarchar2list - according to the Oracle docs it is
a 'varray(m) of varchar(n)'.

The equivalent in Postgresql would be 'varchar(n)[m]'.
https://www.postgresql.org/docs/9.6/static/arrays.html

Hope this helps.
George

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general