Oracle data transform using analytic lag row_number case

So I needed to transform this source data:

Event CategoryStart WeekEnd Week
LEC11
LEC111
LEC111
LEC33
SEM22
SEM46
SEM99
TUT112
TUT46

Into this output format:

Event CategoryNumber of OccurrencesStart WeekEnd Week
LEC311
LEC222
LEC333
LEC2411
SEM122
SEM146
SEM199
TUT113
TUT246
TUT1712

Seems simple, but required a combination of CASE, LAG, and ROW_NUMBER – as Tom Kyte steps through in detail in this article from 2004.

Here is the solution:

SELECT q5.event_category , q5.num , MIN ( q5.week ) AS start_week , MAX ( q5.week ) AS end_week
  FROM (
       SELECT q4.week , q4.event_category , q4.num
            , MAX ( q4.rn ) OVER ( PARTITION BY q4.event_category ORDER BY q4.week ) AS max_rn
        FROM (
             SELECT q3.week , q3.event_category , q3.num
                  , CASE
                       WHEN q3.num != q3.num_prev THEN q3.row_num
                       WHEN q3.week != q3.week_prev + 1 THEN q3.row_num
                       END AS rn  -- mark the beginning of each group
               FROM (
                    SELECT q2.week , q2.event_category , q2.num
                         , LAG  ( q2.num  , 1 , 0 ) OVER ( PARTITION BY q2.event_category ORDER BY q2.week ) AS num_prev
                         , LAG  ( q2.week , 1 , 0 ) OVER ( PARTITION BY q2.event_category ORDER BY q2.week ) AS week_prev
                         , ROW_NUMBER ( ) OVER ( PARTITION BY q2.event_category ORDER BY q2.week ) AS row_num
                      FROM (
                           SELECT t.event_category , q1.week , COUNT(*) as num
                             FROM timetable t
                             JOIN (
                                  SELECT LEVEL AS week
                                    FROM DUAL
                                 CONNECT BY LEVEL <= 52
                                  ) q1
                               ON q1.week BETWEEN t.startw AND t.endw
                            GROUP BY t.event_category , q1.week
                           ) q2
                    ) q3
             ) q4
       ) q5
 GROUP BY q5.event_category , q5.num , q5.max_rn
 ORDER BY q5.event_category , MIN ( q5.week )
;

Sample data:

CREATE TABLE timetable ( startw NUMBER , endw NUMBER , event_category VARCHAR2(3) ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 1 , 11  ,'LEC' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 1 , 11  ,'LEC' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 1 , 1 , 'LEC' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 3 , 3 , 'LEC' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 2 , 2 , 'SEM' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 4 , 6 , 'SEM' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 9 , 9 , 'SEM' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 1 , 12 , 'TUT' ) ;
INSERT INTO timetable ( startw , endw , event_category ) VALUES ( 4 , 6 , 'TUT' ) ;
Posted in Uncategorized | Leave a comment

oracle update select merge

If you want to update one table based on the values in another table, use ‘UPDATE = (SELECT) WHERE (SELECT)’ or ‘MERGE WHERE’, as in examples (3) and (3a) below.

Sample data:

DROP TABLE a ;
CREATE TABLE a ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '1' , 'yes' , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '2' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '3' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '4' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '5' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '5' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '7' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '8' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '9' , NULL , NULL ) ;
INSERT INTO a ( id , text1 , text2 ) VALUES ( '0' , NULL , NULL ) ;
DROP TABLE b ;
CREATE TABLE b ( id VARCHAR2(10) , text1 VARCHAR2(10) , text2 VARCHAR2(10) ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '1' , 'yes' , NULL ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '2' , 'maybe' , 'yes' ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '3' , NULL , NULL ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( '4' , NULL , 'maybe' ) ;
INSERT INTO b ( id , text1 , text2 ) VALUES ( 'ne' , 'maybe' , 'maybe' ) ;
COMMIT ;

Two of the rows in table a – rows 2 and 4 – have different values than their equivalent rows in table b. So ideally I want to update just those 2 rows in table a.

1) Simplest method works ok in terms of getting to the correct end result, but updates the whole of table a, all 10 rows. That doesn’t matter for the end result, because is updating columns that already match to the same value, but is an unnecessary performance overhead.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id ) ;

2) Adding an IN check excludes the rows in a that don’t have a counterpart in b, now down to 4 updates.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id )
WHERE a.id IN ( SELECT b.id FROM b ) ;

EXISTS could be used instead of IN, except it requires slightly more code. The second line would be a longer:

WHERE EXISTS ( SELECT NULL FROM b WHERE a.id = b.id ) ;

EXISTS and IN are functionally the same and perform the same; unlike NOT EXISTS and NOT IN which handle NULLs differently.

2b) This MERGE statement is identical, 4 rows merged:

MERGE INTO a USING ( SELECT * FROM b ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2 ;

Merge can do more: INSERT when not matched, delete based on a where clause; so becomes useful when that is needed.

2c) Using an inline view is also identical, 4 rows updated, but this needs a primary or unique constraint to be in place on the table which is not being updated. Without that it will fail with ORA-01779: cannot modify a column which maps to a non key-preserved table.

ALTER TABLE b ADD CONSTRAINT b_pk PRIMARY KEY ( id ) ;
UPDATE (
   SELECT a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
     FROM a JOIN b ON a.id = b.id
   ) v
SET v.a_text1 = v.b_text1 , v.a_text2 = v.b_text2 ;

3) Adding more conditions to the IN clause gets us to the desired point, 2 rows updated the first time it is run, no rows updated on subsequent runs.

UPDATE a SET ( a.text1 , a.text2 ) = ( SELECT b.text1 , b.text2 FROM b WHERE a.id = b.id )
WHERE a.id IN (
   SELECT b.id FROM b
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
) ;

The NVLs are needed because ‘something=NULL’ always evaluates to false.

3b) Again, MERGE can do the same thing, 2 rows merged initially, none subsequently:

MERGE INTO a USING ( SELECT * FROM b ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2
   WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
      OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

That works ok for a 1:1 join, but where the second table returns multiple values then this will either toggle the update around the different values each time it runs, orfail altogether with ORA-30926: unable to get a stable set of rows in the source tables depending on the number of multiples. A workaround such as a MAX function or similar will ensure unique values are selected

INSERT INTO b ( id , text1 , text2 ) VALUES ( '4' , 'duplicate' , 'duplicate' ) ;
MERGE INTO a USING ( SELECT b.id , MAX ( b.text1 ) AS text1 , MAX ( b.text2 ) AS text2 FROM b GROUP BY b.id ) b
ON ( a.id = b.id )
WHEN MATCHED THEN UPDATE SET a.text1 = b.text1 , a.text2 = b.text2
   WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
      OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

NULL values of id are handled ok without workarounds like this.
3c) And again, using an inline view can do the same thing, 2 rows updated initially, none subsequently.

ALTER TABLE b ADD CONSTRAINT b_pk PRIMARY KEY ( id ) ;
UPDATE (
   SELECT a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
     FROM a JOIN b ON a.id = b.id
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
   ) v
SET v.a_text1 = v.b_text1 , v.a_text2 = v.b_text2 ;

Those statements only tell you the number of rows changed. If you want to know the details of what was changed, and you know for sure that no one else is modifying the data at that time, then you can run a report before the update/merge:

SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
  FROM a JOIN b ON a.id = b.id
 WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
    OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' ) ;

More likely you need certainty that what you are reporting on is really changed, that means a begin..end block is needed to ensure transaction isolation from any other data modifications at that time. That means writing the report to a log table rather than just selecting the information:

BEGIN
   INSERT INTO log_table
   SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2 , SYSDATE
     FROM a JOIN b ON a.id = b.id
    WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
       OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
   ;
   -- main UPDATE or MERGE statement goes here.
END ;

PL/SQL can be a better alternative, provided is not a large number of updates where performance is needed.

set serverout on
BEGIN
   FOR r1 IN (
      SELECT a.id , a.text1 AS a_text1 , a.text2 AS a_text2 , b.text1 AS b_text1 , b.text2 AS b_text2
        FROM a JOIN b ON a.id = b.id
       WHERE NVL ( a.text1 , 'x' ) != NVL ( b.text1 , 'x' )
          OR NVL ( a.text2 , 'x' ) != NVL ( b.text2 , 'x' )
      )
   LOOP
      dbms_output.put_line ( 'Updating ' || r1.id || ' from ' || r1.a_text1 || ' , ' || r1.a_text2 || ' to ' || r1.b_text1 || ' , ' || r1.b_text2 ) ;
      UPDATE a SET a.text1 = r1.b_text1 , a.text2 = r1.b_text2 WHERE a.id = r1.id ;
   END LOOP ;
END ;
Posted in Scripts | Leave a comment

Run Windows interactive scheduled task even if user not logged in

http://superuser.com/questions/616206/run-interactive-task-even-if-user-is-not-logged-on-windows?rq=1

Problem arises when a windows scheduled task needs to fire up a GUI – if there is no session logged in then it fails to run because has nowhere to display the GUI.

Caveat: I haven’t tried this solution myself yet, don’t know if there are any unpleasant side effects from it or not.

Posted in Uncategorized | Leave a comment

Data from MySQL to Oracle with dg4odbc database link or mysqldump

To transfer data from MySQL into Oracle

1) Best method is to use a database link. That way the MySQL table is available in the Oracle database for use in queries, joins, DML, PL/SQL.

Simple guides on how to do to that are:

  1. Shishir Tekade http://www.shishirtekade.com/2014/01/connect-oracle-to-mysql-server-through.html
  2. Pythian http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/
  3. My Oracle Support 1320645.1 ‘Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link’ https://support.oracle.com/epmos/faces/DocContentDisplay?id=1320645.1

1b) For a critical/enterprise system it might be worth paying for Oracle’s Hetrogenous Services Database Gateway. That works the same as above but substitutes Oracle’s proprietary driver files in place of free ODBC. The advantage is Oracle Support would fully support this, not pass the buck onto the free ODBC drivers.

2) Alternatively can have a regularly running scheduled task or cron that copies data from MySQL into Oracle. The mysqldump utility used for this is often installed by default with linux, in /usr/bin. To avoid failed copies from leaving empty tables in the target database, a safety check (e.g. count below) is needed before proceeding.

rm -f data.tmp

# 1) Extract list of insert statements from mysql into file data.tmp
mysqldump --host=myhost --user=myuser --password=mypassword --complete-insert --no-create-info --skip-opt --compatible=oracle --skip-quote-names \
    --compact mydatabase mytable > data.tmp

# 2) Change table name from mytable to mytable_tmp in insert statements
sed -i 's/mytable/mytable_tmp/g' data.tmp

# 3) Insert those into Oracle - using temp table and begin/end check to only proceed if reasonable number of rows.
sqlplus -s username/password@database << END_ORACLE
set serverout on
-- Use MySQL datetime format
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS' ;
DELETE FROM mytable_tmp ;
@data.tmp
-- But only copy this data to the live table if looks to be a reasonable amount of data.
DECLARE
   l_count NUMBER ;
BEGIN
   SELECT COUNT(*) INTO l_count FROM mytable_tmp ;
   IF l_count > 100
   THEN
      dbms_output.put_line ( 'Copying ' || l_count || ' rows from MySQL into mytable' ) ;
      DELETE FROM mytable ;
      INSERT INTO mytable SELECT * FROM mytable_tmp ;
   ELSE
      dbms_output.put_line ( 'Error: insufficient rows loaded, not proceeding with data copy.' ) ;
   END IF ;
END ;
/
END_ORACLE

Removing –no-create-info option from mysqldmp will generate oracle compatible create table DDL statements, useful for first setup.

h/t Slim Backwater.

Posted in Uncategorized | Leave a comment

Analytics row_number to identify first parent row in oracle pl/sql cursor loop

Let’s say you have 10m parent rows and 11m child rows and need to process them row-at-a-time in pl/sql. Two methods of doing that are below.

  • The second method will fire the combined select just the once, picking up all 11m rows in one go.
  • The first method will fire the child select 10m times individually, with a different id value passed into the where clause each time. It uses bind variables for that, so doesn’t hard parse 10m times, but still will take time.

The first method is the simplest to read, maintain and debug, so stick with that unless performance is required.

Method 1 – traditional way, two cursor loops

BEGIN
   FOR r_dept IN ( SELECT deptno , dname FROM dept )
   LOOP
      -- put parent (department) row processing code here.
      FOR r_emp IN ( SELECT empno , ename FROM emp WHERE emp.deptno = r_dept.deptno )
      LOOP
         -- put child (employee) row processing code here.
      END LOOP ;
   END LOOP ;
END ;

Method 2 – single cursor loop

BEGIN
   FOR r_both IN (
      SELECT dept.deptno
           , dept.dname
           , emp.empno
           , emp.ename
           , ROW_NUMBER() OVER ( PARTITION BY dept.deptno ORDER BY dept.deptno , emp.empno ) AS rn
        FROM dept
        LEFT OUTER JOIN emp  -- outer join if you want to process childless parents = departments with no employees assigned. 
          ON dept.deptno = emp.deptpno
   )
   LOOP
      IF r_both.rn = 1  -- starting to work on a different parent (department) record.
      THEN
         -- put parent (department) row processing code here.
      END IF ;
      -- put child (employee) row processing code here.
   END LOOP ;
END ;
Posted in PL/SQL, Uncategorized | Leave a comment

oracle substrb function to chop a string down to its first 4000 bytes

The substrb 1 , 4000 function will chop a string down to its first 4000 bytes, e.g.:

SELECT SUBSTR('1€3',2,1) , SUBSTRB('1€3',1,3) , LENGTH('1€3') , LENGTHB('1€3') FROM dual ;

Needed if using the (non default) option of ‘char’ in column lengths:

CREATE TABLE mytable ( col1 VARCHAR2(4000 CHAR) ) ;

You can change the default setting at session level:

ALTER SESSION SET nls_length_semantics = 'CHAR' ;

But that needs run every time you connect in, it doesn’t remember session settings after you disconnect. Permanently changing that parameter at database level is possible but not advisable.

You can see what is set as default at each of the 3 levels with:

SELECT * FROM nls_session_parameters  WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_instance_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;
SELECT * FROM nls_database_parameters WHERE parameter IN ( 'NLS_LENGTH_SEMANTICS' , 'NLS_CHARACTERSET' , 'NLS_NCHAR_CHARACTERSET' ) ORDER BY parameter ;

The NVARCHAR2 datatype could be useful if you want to use a different character set for a particular column.

Posted in Uncategorized | Leave a comment

Format sql files with sed: tab to space, pad commas with spaces, uppercase match

Shell script to make more readable all sql files in a directory – pads commas and brackets with whitespace, converts tabs to spaces, makes SQL keywords uppercase.

# FUNCTIONS
pad () {
    sed -i "s/\([^ ]\)$1/\1 $1/g" *sql
    sed -i "s/$1\([^ ]\)/$1 \1/g" *sql
}
upper () {
    sed -i "s/\(\<$1\>\)/\U\1/g" *sql
}
# MAIN
dos2unix *sql
# tabs to 4 spaces
sed -i 's/\t/    /g' *sql
# pad brackets commas semicolons
pad '('
pad ')'
pad ','
pad ';'
pad '!='
pad '='
pad '||'
# uppercase
upper nvl
upper decode
upper to_char
upper least
upper greatest
upper when
upper then
upper case
upper end
upper values
upper varchar2
upper in
upper date
upper upper
upper regexp_replace
upper sysdate
upper exists
upper replace
upper else
upper substr
upper count
upper distinct
upper partition
upper over
Posted in Uncategorized | Leave a comment

Laravel Symfony CodeIgniter

Laravel is the new CodeIgniter, a coding framework on top of PHP for simplified/standardised syntax. Complex PHP applications can become un-supportable without a coding framework like those in place.

To install and demo Laravel on WAMP:

(1) follow the steps in http://www.darwinbiler.com/how-to-install-laravel-on-wamp-for-beginners/ (note, uses a different WAMP php.ini file from normal).

(2) File c:\wamp\www\laraveltest\public\.htaccess then needs

RewriteBase /laraveltest/public/

added under the RewriteEngine line, as at http://stackoverflow.com/a/18412482

(3) Using WAMP click on wamp icon > apache > apache modules > scroll and check ‘rewrite_module Restart a LoadModule’ http://stackoverflow.com/a/14490649

(4) Then edit c:\wamp\www\laraveltest\app\routes.php and browse to http://localhost/laraveltest/public/user to see effects.

(5) For database connectivity, edit c:\wamp\www\laraveltest\app\config\database.php to specify database name.

(6) To demo database connectivity, again edit c:\wamp\www\laraveltest\app\routes.php to add code like

Route::pattern('id', '[0-9]+');
Route::get('/user/{id}', function($id)
{
    // Only called if {id} is numeric.
    $user = DB::table('users')->where('id', $id)->first();
    var_dump($user);

    echo '

prev | next

'; });

And browse to http://localhost/laraveltest/public/user/1 to see working.

A table called ‘users’ was used for this demo, with an ‘id’ numeric field plus some text fields

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `email` varchar(400) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `users` (`id`, `name`, `email`, `description`) VALUES
(1, 'John Smith', 'john@email.com', 'John''s description field...'),
(2, 'Jane Doe', 'jane@email.com', 'Jane''s description field');

More database code at http://laravel.com/docs/queries#selects, and forms code at http://laravel.com/docs/html.

For debugging, look in file c:\wamp\www\laraveltest\app\storage\logs\laravel.log

Currently Laravel supports four database systems: MySQL, Postgres, SQLite, and Microsoft SQL Server, but does not support Oracle http://laravel.com/docs/database. However the Laravel-OracleDB driver package is avilable as an add on https://github.com/jfelder/Laravel-OracleDB.

An alternative to Laravel is Symfony.

Installation of Symfony requires composer – but that should already have been installed along with Laravel above, which means would now just need:

mkdir c:\wamp\www\symfony
cd c:\wamp\www\symfony
composer create-project symfony/framework-standard-edition myproject/ ~2.4
php app/console server:run

[Source: http://symfony.com/doc/current/quick_tour/the_big_picture.html]

Once installed, browse to http://localhost:8000/ and also http://localhost:8000/demo/hello/YourNameHere.

Posted in php | Leave a comment

PL/SQL bulk collect union

PL/SQL bulk collect will work with UNION ALL when written with an inline view:

DECLARE
   TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ;
   l_big l_type_assoc ;
BEGIN
     WITH q AS (
          SELECT ename AS name
            FROM emp
           UNION ALL
          SELECT dname AS name
            FROM dept
          )
   SELECT name
     BULK COLLECT INTO l_big
     FROM q
    ORDER BY name ;

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

If the array you are using is not an associative array (that is, does not have the INDEX BY clause in its declaration) then an alternative method is to merge arrays together after they are populated with multiset union:

DECLARE
   TYPE l_type_varray IS TABLE OF VARCHAR2(14) ;
   l_tmp l_type_varray ;
   l_big l_type_varray ;
BEGIN

   SELECT ename BULK COLLECT INTO l_tmp FROM emp ;

   l_big := l_tmp ;  -- copy the array

   SELECT dname BULK COLLECT INTO l_tmp FROM dept ;

   l_big := l_big MULTISET UNION ALL l_tmp ;  -- union the arrays

   FOR indx IN 1..l_big.COUNT
   LOOP
      DBMS_OUTPUT.put_line ( l_big(indx) ) ;
   END LOOP ;

END ;
/

PHP’s oci_bind_array_by_name will only work with associative arrays, returns error ‘ORA-06550: PLS-00306: wrong number or types of arguments in call’ if varrays are used.

Posted in Uncategorized | Leave a comment

Extended Hexadecimal in Oracle

Standard decimal > hexadecimal is done with:

select to_char ( 10 , 'x' ) from dual ;

But I wanted a kind of extended hexadecimal that would keep single letters going up beyond 15/f, so would return ‘g ‘for decimal 16 instead of returning hexadecimal ’10’.

Why did I want that? I needed to squeeze a 2 digit number column into a 1 character column in an extract transform load operation. Most of the source data was less than 16, but there were a few values higher than that, and a simple decimal > hexadecimal translation wouldn’t work for those.

The ascii value for ‘a’ is 97, so using the chr function to turn numbers into their ascii character equivalents gives:

SELECT CASE
           WHEN numcol BETWEEN  0 AND  9 THEN TO_CHAR ( numcol )
           WHEN numcol BETWEEN 10 AND 35 THEN CHR ( 87 + numcol )
       END AS extended_hexadecimal
  FROM mytable ;
Posted in Uncategorized | Leave a comment