Monday, April 28, 2008

& in Oracle insert or update statements

If you have & or % in your Oracle insert or update statements - you probably would like to do the following:


SQL> set define off
SQL> [your insert or update query containing & and %]
SQL> set define on

Wednesday, April 23, 2008

Export from Oracle table to CSV file

Sometimes you need to export result of the query to CSV file. Here is a nice example of how to do this kind of operations.


SQL> SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF
SQL> spool outfile.csv
SQL> select '"'|| column_1 || '",' || column_2 || ',' || column_3 from mytable where [your where statement]
...
...
SQL> spool off
SQL> exit

In this case column_1 data will be surrounded by "" (you may need this if you have , character in field data).

Update: SET LINESIZE determines a maximum number of characters in the line. So if you expect output bigger than 500 lines you must set this to a bigger value.

Tuesday, April 8, 2008

Cent OS 5.1 + Oracle 10g

Apart of modifying /etc/redhat-release to "redhat-4", there are a couple of packages that you will need to install in order to make Oracle 10g work with Cent OS 5.1:


yum install \
compat-db-4.2.52-5.1 \
glibc-devel-2.5-18.el5_1.1 \
libX11-devel-1.0.3-8.0.1.el5 \
libXau-devel-1.0.1-3.1 \
libXdmcp-devel-1.0.1-2.1 \
libXmu-1.0.2-5 \
libXtst-devel-1.0.1-3.1 \
mesa-libGL-devel-6.5.1-7.5.el5 \
openmotif-2.3.0-0.3.el5 \
xorg-x11-proto-devel-7.1-9.el5.centos