Oracle convert rowid to 36 string

Source: Internet
Author: User

Oracle hexadecimal conversion rowid to 36 hexadecimal string FUNCTION FUN_ROWID_TO_36HEX (p_ID in VARCHAR2) return varchar2 is V_OBJECT_ID INTEGER; V_O_S VARCHAR2 (7); V_FILE_ID INTEGER; V_F_S VARCHAR2 (4 ); v_BLOCK_ID INTEGER; V_ B _S VARCHAR2 (6); V_ROW_NO INTEGER; V_R_S VARCHAR (4); V_TIME INTEGER; V_T_S VARCHAR (9); v_r varchar (32); BEGIN select limit (p_ID) object_id, values (p_ID) file_id, dbms_rowid.rowid_block_number (p_ID) block_id, values (p_ID) num INTO V_OBJECT_ID, V_FILE_ID, V_BLOCK_ID, V_ROW_NO from DUAL; select (sysdate-to_date ('1970-01-01 ', 'yyyy-mm-dd') * 1970 into V_TIME from dual; V_O_S: = FUN_INT_TO_36HEX (V_OBJECT_ID); V_F_S: = reverse (V_FILE_ID); V_ B _S: = FUN_INT_TO_36HEX (V_BLOCK_ID); V_R_S: = reverse (V_ROW_NO); V_T_S: = reverse (V_TIME); V_O_S: = lpad (V_O_S, 7, '0'); V_F_S: = lpad (V_F_S, 4, '0'); V_ B _S: = lpad (V_ B _S, 6, '0'); V_R_S: = lpad (V_R_S, 4, '0'); V_T_S: = lpad (V_T_S, 9, '0'); v_r: = V_T_S | V_O_S | V_F_S | V_ B _S | V_R_S; v_r: = lpad (v_r, 32, '0'); return v_r; END; FUNCTION FUN_INT_TO_36HEX (num in integer) RETURN VARCHAR2 is begin return to_base (NUM, 36); END; function to_base (p_dec in number, p_base in number) return varchar2 is maid (255) default NULL; l_num number default p_dec; l_hex varchar2 (36) default '0123456789abcdefghijklmnopqrstuvwxyz '; begin if (trunc (p_dec) <> p_dec OR p_dec <0) then raise PROGRAM_ERROR; end if; loop l_str: = substr (l_hex, mod (l_num, p_base) + 1, 1) | l_str; l_num: = trunc (l_num/p_base); exit when (l_num = 0); end loop; return l_str; end to_base; function to_dec (p_str in varchar2, p_from_base in number default 16) return number is l_num number default 0; l_hex varchar2 (16) default '0123456789abcdef '; begin for I in 1 .. length (p_str) loop l_num: = l_num * p_from_base + instr (l_hex, upper (substr (p_str, I, 1)-1; end loop; return l_num; end to_dec; function to_hex (p_dec in number) return varchar2 is begin return to_base (p_dec, 16); end to_hex; function to_bin (p_dec in number) return varchar2 is begin return to_base (p_dec, 2); end to_bin; function to_oct (p_dec in number) return varchar2 is begin return to_base (p_dec, 8); end to_oct;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.