"case" in SQL

UPDATE inventory SET price = price *
 CASE
   WHEN quantity > 20 THEN 0.75
   WHEN quantity BETWEEN 10 AND 20 THEN 0.90
   ELSE 0.95
   END;

SELECT employee_name
 CASE admin
   WHEN 1 THEN 'yes'
   ELSE 'no'
   END 'admin'
FROM employees;

VC++ project configuration tips

1. use "local setting" files (*.vsprops) to store the directory configurations. It can be reused for more than one projects.

ProjectType="Visual C++"
Version="8.00"
Name="lokale Einstellungen"
>
Name="OcciDir"
Value="D:\apps\oracle10g\OCI"
PerformEnvironmentSet="true"
/>
Name="BoostDir"
Value="D:\boost\Boost\include\boost_1_35_0"
PerformEnvironmentSet="true"
/>
Name="OcciLib"
Value="lib\MSVC\vc8"
PerformEnvironmentSet="true"
/>


2. The includes files' path should be in "C/C++ => Allgemein => Zusaetzliche Includeverzeichnisse".

3. The libs which are needed should be in "Linker => Allgemein => Zusaetzliche Bibliotheksverzeichnisse". If the libs can not be found, there will be errors like "fatal error LNK1104: Datei libboost_program_options-vc80-mt-1_35.lib kann nicht geoeffnet werden".

Debug database application in visual studio 2005

After upgrading from visual studio 2003 to visual studio 2005, some configurations are needed for debugging database applications.

1. download occi for vc8. Place it at $oracle_home\OCI\lib\MSVC\vc8.

2. add it to Path. Becareful, it must stay before $oracle_home\bin in path.

3. Go to project properties page. Linker -> Eingabe -> Zusaetzliche Abhaengigkeiten, use oraocci10d.lib for debug mode, oraocci10.lib for release mode.

Script to create user

# user auf Ziel-Rechner anlegen, zB script in CYGWIN

-- user anlegen
sqlplus 'sys/lbsys as sysdba' <
-- drop user $1 cascade;

-- vielleicht datafile ' ' noetig?
CREATE TABLESPACE $1
LOGGING
DATAFILE 'd:\ORADATA\\DAT_$1_1.dbf' SIZE 500M AUTOEXTEND
ON NEXT 200M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE USER $1 PROFILE DEFAULT
IDENTIFIED BY $1 DEFAULT TABLESPACE $1
QUOTA UNLIMITED
ON $1
ACCOUNT UNLOCK;
GRANT ALTER SESSION TO $1;
GRANT CREATE PROCEDURE TO $1;
GRANT CREATE SEQUENCE TO $1;
GRANT CREATE TRIGGER TO $1;
GRANT CREATE TYPE TO $1;
GRANT CONNECT TO $1;

GRANT CREATE TABLE TO $1;
GRANT CREATE view TO $1;
GRANT CREATE synonym TO $1;

EOF

Validate SDO_GEOMETRY

SELECT A.COMPLEX_ID, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY, M.diminfo) AS PROBLEM
FROM LB_GDF_COMPLEX A, USER_SDO_GEOM_METADATA M
WHERE M.table_name = 'LB_GDF_COMPLEX'
AND M.column_name = 'GEOMETRY'
AND A.feature_class = 1119
AND SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.GEOMETRY, M.diminfo) <> 'TRUE'

IMP and EXP

imp 'sys/syspwd as sysdba' FROMUSER=fu TOUSER=tu FILE=file.dmp ignore=y log=d:\dump_imp.log

imp 'sys/syspwd as sysdba' FROMUSER=fu TOUSER=tu FILE=file.dmp TABLES=(t1,t2,...,tn)

exp hildesheim/hildesheim@arden file=hildesheim.dmp

Same text, different binary

When PL/SQL code is checked into CVS, ASCII mode should be used, not binary mode. As we know, the same texts under Linux and Windows are not same in binary mode. For example, "NEWLINE" is LF under Linux but CR+LF under windows.

If PL/SQL code is written under windows but checked into CVS in binary mode, we will get problems when we wrap the code under Linux.