Search notes:

Oracle: SQL Loader Example: Evaluate a value with case when

The following example evaluates the first value in the CSV file to be loaded with a cascade of case when then to translate an (numeric) id into a character string.
create table tq84_sql_loader_case_when (
   type_   varchar2(3),
   num     integer
);
Github repository Oracle-Patterns, path: /SQLLoader/ex-case-when/create_table.sql
load data
infile 'load.dat' -- "str '\r\n'"
truncate into table tq84_sql_loader_case_when
fields terminated by ';' (
  type_ "case :type_ 
               when '1' then 'foo'
               when '2' then 'bar'
               when '3' then 'baz'
               else        '???'
         end",
  num char
)
Github repository Oracle-Patterns, path: /SQLLoader/ex-case-when/load.ctl
2;42
1;9
3;18
6;123
3;5
Github repository Oracle-Patterns, path: /SQLLoader/ex-case-when/load.dat
$ sqlldr control=load.ctl userid=rene/rene@ora18

See also

Other SQL Loader examples

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759428580, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/tools/sql-loader/examples/case-when(72): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78