Solved

Increment hexadecimal number by 1 in Oracle

Posted on 2009-04-09
7
1,266 Views
Last Modified: 2013-12-18
Hello everybody,

I'm writing an Oracle stored procedure and I need to increment a hexadecimal number by 1.

For example,
From  02000014000004630509000300002586
To      02000014000004630509A00300002586

Is there a function / code  to accomplish this ?

Thank you for your help
0
Comment
Question by:edoc_admin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24109124
I'm not sure what your example is showing,.

that's a difference of 175921860444160
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24109212
in general though, you can turn a hex number into a decimal number and add it then convert back

02000014000004630509000300002586 + 1 =  02000014000004630509000300002587

SELECT TO_CHAR(
           TO_NUMBER('02000014000004630509000300002586', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') + 1,
           0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
       )
           x
  FROM DUAL
0
 

Author Comment

by:edoc_admin
ID: 24109804
Hi sdstuber,

Let me give you a specific example. I have a field (ROLE_ID type RAW) and there're 6 ROLE_ID records related to the same JOB_ID:

ROLE_ID                                                             JOB_ID
02000014000004630504000300002586   02000014000004630502000100002586
02000014000004630505000300002586   02000014000004630502000100002586
02000014000004630506000300002586   02000014000004630502000100002586
02000014000004630507000300002586   02000014000004630502000100002586
02000014000004630508000300002586   02000014000004630502000100002586
02000014000004630509000300002586   02000014000004630502000100002586

What I would like to calculate is the next one

ROLE_ID                                                             JOB_ID
0200001400000463050A000300002586   02000014000004630502000100002586

Could this be done ?

Thanks
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 20

Expert Comment

by:flow01
ID: 24110027
To get the result you expected , you shoult ad 10 to that part of the string that seems to be a hex number  !
Adding 1 results in 20000140000046305091

 1  SELECT V1HEX
  2  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT)    ) V1
  3  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT) + 1) V1P1
  4  , TO_CHAR((TO_NUMBER(V1HEX,HEXFORMAT) + 1),HEXFORMAT) V1HEXP1
  5  , HEXFORMAT
  6  --, ASCII
  7  FROM
  8  (
  9  SELECT V1 V1HEX, RPAD('X',LENGTH(V1),'X') HEXFORMAT
 10  , CONVERT(V1,'WE8EBCDIC500','US7ASCII') ASCII
 11  FROM
 12  (
 13  select '02000014000004630509000300002586' V1 FROM DUAL
 14  UNION
 15  select '020000140000046305090' V1 FROM DUAL
 16  )
 17* )
SQL> /

V1HEX
--------------------------------
V1
----------------------------------------
V1P1
----------------------------------------
V1HEXP1
----------------------------------------
HEXFORMAT
----------------------------------------
020000140000046305090
151115817523822371819664
151115817523822371819665
  20000140000046305091
XXXXXXXXXXXXXXXXXXXXX

02000014000004630509000300002586
2658457576133102747150782025131107718
2658457576133102747150782025131107719
  2000014000004630509000300002587
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


SQL> ed
Wrote file afiedt.buf

  1  SELECT V1HEX
  2  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT)    ) V1
  3  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT) + 10) V1P1
  4  , TO_CHAR((TO_NUMBER(V1HEX,HEXFORMAT) + 10),HEXFORMAT) V1HEXP1
  5  , HEXFORMAT
  6  --, ASCII
  7  FROM
  8  (
  9  SELECT V1 V1HEX, RPAD('X',LENGTH(V1),'X') HEXFORMAT
 10  , CONVERT(V1,'WE8EBCDIC500','US7ASCII') ASCII
 11  FROM
 12  (
 13  select '02000014000004630509000300002586' V1 FROM DUAL
 14  UNION
 15  select '020000140000046305090' V1 FROM DUAL
 16  )
 17* )
SQL> /

V1HEX
--------------------------------
V1
----------------------------------------
V1P1
----------------------------------------
V1HEXP1
----------------------------------------
HEXFORMAT
----------------------------------------
020000140000046305090
151115817523822371819664
151115817523822371819674
  2000014000004630509A
XXXXXXXXXXXXXXXXXXXXX

02000014000004630509000300002586
2658457576133102747150782025131107718
2658457576133102747150782025131107728
  2000014000004630509000300002590
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


SQL>

  1  select lpad(rownum,2,'0'), to_char(lpad(rownum,2,'0'),'XX') from user_objects
  2* where rownum < 32
SQL> /

LP TO_
-- ---
01   1
02   2
03   3
04   4
05   5
06   6
07   7
08   8
09   9
10   A
11   B
12   C
13   D
14   E
15   F
16  10
17  11
18  12
19  13
20  14
21  15
22  16
23  17
24  18
25  19
26  1A
27  1B
28  1C
29  1D
30  1E
31  1F

31 rows selected.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24110060
looks like your just adding 1 to the substring of the first 20 hex characters and leaving the characters 21 to the end alone.

SELECT HEXTORAW(TO_CHAR(
                    TO_NUMBER(SUBSTR(RAWTOHEX(role_id), 1, 20), RPAD('x', 20, 'x')) + 1,
                    RPAD('fm0', 22, 'X')
                )
                || SUBSTR(RAWTOHEX(role_id), 21))
  FROM your_table
where ....
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24110094
so, to get the next role_id for each job_id


SELECT job_id,
       HEXTORAW(TO_CHAR(
                    TO_NUMBER(SUBSTR(RAWTOHEX(maxrole), 1, 20), RPAD('x', 20, 'x')) + 1,
                    RPAD('fm0', 22, 'X')
                )
                || SUBSTR(RAWTOHEX(maxrole), 21)) next_role
  FROM (  SELECT job_id, MAX(role_id) maxrole
            FROM your_table
        GROUP BY job_id)

Open in new window

0
 

Author Closing Comment

by:edoc_admin
ID: 31568616
Thank you sdstuber. Your solution is what I was looking for.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question