I’m not exactly sure if there’s an existing function in Oracle to convert ROW_ID into a Siebel Row_ID Number format. I already know the formula so I created a function. I’ve been using it for a project numerous times so I needed to create this reusable function.


CREATE OR REPLACE FUNCTION SH_RowIdToRowIdNum(v_rowid VARCHAR2) RETURN VARCHAR2
IS
v_pre VARCHAR2(30):='';
v_series VARCHAR2(30):='';
iCtr INTEGER:=0;
v_char VARCHAR2(1):='';
i_val INTEGER:=0;
i_Sum INTEGER:=0;
v_RowNum VARCHAR2(30):='';
BEGIN

v_series := SUBSTR(v_rowid,instr(v_rowid,'-')+1);
FOR iCtr IN 1..LENGTH(v_series)
LOOP
v_char := SUBSTR(v_series,iCtr*-1,1);

IF INSTR('0123456789',v_char) > 0 THEN
i_val := v_char;
ELSE
i_val := ASCII(v_char)-55;
END IF;
i_Sum := i_Sum + (i_val * power(36, iCtr-1));

END LOOP;
v_RowNum := SUBSTR(v_rowid,1,INSTR(v_rowid,'-')) || i_Sum;
RETURN v_RowNum;
END;

sample usage

SELECT SH_RowIdToRowIdNum('1-5GYG0'), SH_RowIdToRowIdNum('1-IB79I')
FROM dual

output

1-9189216 1-30755718

Reference:
http://onlysiebel.blogspot.com/2009/10/what-is-row-id.html

Comments are closed.

Post Navigation