Remove/unload java object in Oracle database

drop java class "package.Class";

to remove all java objects, use:

$ORACLE_HOME/javavm/install/rmjvm.sql

Linux X11 security problem

Today I ran a script under linux and got such error:
Xlib: connection to ":0.0" refused by server

X11 security: the user that tries to open a window
is an other one than the one that is logged-in (even root cannot access
this). Start by removing X11 security with `xhost +`.


The solution is to execute xhost + under root user. After that, a message was displayed "access control disabled, clients can connect from any host".

VC++ Includeverzeichniss Makros

Using macros to configure projects.

Eigenschaft -> C/C++ -> Allgemein -> Zusaetzliche Includeverzeichniss -> Makros>>

Oracle Coordinate System Transformation Functions

-- how to extract srids
SELECT *
FROM mdsys.cs_srs cs
WHERE cs.cs_name LIKE '%UTM%'
AND cs.cs_name LIKE '%32%';

-- how to make a spatial point (WGS84)
SELECT mdsys.sdo_geometry(2001, 8307, mdsys.sdo_point_type(n.x, n.y, NULL), NULL, NULL) AS geometry
FROM nodes n;

-- how to make a converted spatial point (UTM Zone 32, Northern Hemisphere (WGS 84))
SELECT t.geometry.sdo_point.x as x, t.geometry.sdo_point.y as y FROM (SELECT sdo_cs.transform(mdsys.sdo_geometry(2001, 8307, mdsys.sdo_point_type(n.x, n.y, NULL), NULL, NULL), 82344) AS geometry FROM nodes n) t;

82344 is SRID for UTM Zone 32, Northern Hemisphere (WGS 84).

Polymorphism

// abstract base class
#include 
using namespace std;

class CPolygon {
protected:
int width, height;
public:
void set_values (int a, int b)
  { width=a; height=b; }
virtual int area (void) =0;
};

class CRectangle: public CPolygon {
public:
int area (void)
  { return (width * height); }
};

class CTriangle: public CPolygon {
public:
int area (void)
  { return (width * height / 2); }
};

int main () {
CRectangle rect;
CTriangle trgl;
CPolygon * ppoly1 = ▭
CPolygon * ppoly2 = &trgl;
ppoly1->set_values (4,5);
ppoly2->set_values (4,5);
ppoly1->area();
ppoly2->area();
return 0;
}

Abstract Class vs. Interface

Abstract Class: can have non-abstract method; can have member variables; all the abstract methods must be overwritten in the extended classes.

Interface: describe some common rules, thus all methods are abstract; all member variables must be final static; In java, interface can be used for multi-inheritance purpose.

Geodetic coordinates translation - useful links

http://psas.pdx.edu/CoordinateSystem/Latitude_to_LocalTangent.pdf

http://en.wikipedia.org/wiki/Geodetic_system

http://www.cplusplus.com/reference/clibrary/cmath/

IMP and EXP with data pump

$ expdp hr/hr DIRECTORY=exp_dir DUMPFILE=hr_exp.dmp LOGFILE=hr_exp.log

$ CREATE DIRECTORY dir_dump AS 'D:/tmp'

create user hr2

$ GRANT READ, WRITE ON DIRECTORY dir_dump TO hr2

$ impdp hr2/hr2 SCHEMAS=hr2 DIRECTORY=dir_dump LOGFILE=hr2_imp.log DUMPFILE=hr_exp.dmp

"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.

Create index parallel nologging

alter table t1 nologging;
alter table t2 nologging;

create index T1_IDX on T1 (Field1, Field2, Field3, Field4) parallel 2 nologging;
create index T2_IDX on T2 (Field1, Field2, Field3, Field4) parallel 2 nologging;

parallel 2 for 2 CPUs.

Rebuild Spatial Index

When I excute "truncate table ..." or "delete from ...", sometimes I encounter ORA-29859 or ORA-29861 error. In most cases, I just rebuild the spatial index on the geometry column and the problem will be fixed.

DROP INDEX GEO_PS_POSITION_SX;
CREATE INDEX GEO_PS_POSITION_SX ON GEO_PS_POSITION (ROUTE) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Solution to ORA-01157 and ORA-01110

SQL>alter database datafile '/data02/oradata/test/testuser_.dbf' offline drop;
SQL>shutdown abort;
SQL>startup;