Re: [GENERAL] equivalent for md5, clobs and varchar2 list
On Thu, 14 Sep 2017 00:01:09 +, Peter Koukouliswrote: >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
[GENERAL] equivalent for md5, clobs and varchar2 list
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, 'mmdd')||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