• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value

By admin

Description

This script creates a function to convert Hexadecimal value to decimal.

Example

SQL>  select hextodec('BABA11') from dual;

HEXTODEC('BABA11')
------------------
          12237329

Starting from Oracle 8i, there is a new format model(‘X’) for the to_number function which can be used to accomplish the same task. For the above example, you could issue the following:

SQL> select to_number('BABA11','XXXXXX') from dual; 

TO_NUMBER('BABA11','XXXXXX')
----------------------------
                    12237329

Execution Environment:

SQL, SQL*Plus, iSQL*Plus

Access Privileges:

No special privileges are required to run this script.

Usage:

$ sqlplus [user]/[pw]@[SCRIPTFILE]

The script

Create or replace FUNCTION hextodec (hexnum in char) RETURN number IS  
 x	number;  
 digits number; 
 result	number := 0;                                            
  
 current_digit char(1);                            
 current_digit_dec number;                                            
BEGIN                                                    
 digits := length(hexnum);                                       
 for x in 1..digits loop                                     
   current_digit := upper(SUBSTR(hexnum, x, 1));                          
   if current_digit in ('A','B','C','D','E','F') then           
     current_digit_dec := ascii(current_digit) - ascii('A') + 10;    
   else                  
     current_digit_dec := to_number(current_digit); 
   end if;                                                            
   result := (result * 16) + current_digit_dec;                  
 end loop;                                                      
 return result;                                                
END; 
/

Filed Under: oracle

Some more articles you might also be interested in …

  1. RMAN: SET NEWNAME Command Using SQL
  2. How to Roll Forward a standby database using RMAN incremental backup in 11g
  3. ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  4. How To Create Device Alias For ASM Disks Using mknod On Linux/Unix
  5. How to check and repair ACFS with FSCK
  6. When to Use Startup/Shutdown Database and Alter Database Command in Oracle 12c
  7. How to Failover a Service During Instance Shutdown Using SRVCTL
  8. How to Define PDB Listeners With Different Ports In a Oracle database Multitenant Setup
  9. How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)
  10. Oracle SQL script to Show current Users and SQL being Executed

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary