Tuesday, 20 October 2009

oracle db - how to identify compilation errors

1.
select * from user_errors;

2.
SELECT RTRIM(INITCAP(e.type))||' '||e.name||': Line '||
TO_CHAR(e.line)||' Column '||TO_CHAR(e.position) linecol,
s.text sourceline,RPAD(' ',(e.position - 1))||'^' errpos,
e.text error
FROM user_source s,user_errors e
WHERE e.type = s.type
AND e.name = s.name
AND e.line = s.line
ORDER BY e.name,e.sequence

The latter one will give you the exact line where the error occcurs

Wednesday, 23 September 2009

encrypting partitions with LUKS in Linux

with these commands we encrypt a full partition located under /dev/sde1
(UUID=7a89446f-c43f-41b1-bc2b-99c74c64cdeb)

In the below steps I use dcfldd (which is much more user friendly than dd and gives you progress status) and cryptsetup.

sudo apt-get install dcfldd cryptsetup

sudo dcfldd if=/dev/urandom of=/dev/sde1 statusinterval=10 bs=10M conv=notrunc
sudo cryptsetup luksFormat /dev/sde1
sudo cryptsetup luksOpen /dev/sde1 maxtor_encrypted
sudo mkfs.ext3 /dev/mapper/maxtor_encrypted
sudo mkdir /mnt/maxtor_enc
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime


it is safer to use UUIDs instead of /dev/sde1 because even if order of the drives changes (USB drives, or because of your BIOS) you always can uniquely identify the one with the unique UUID.
You can get the UUIDs of all of your disks' partitions by running
$ blkid

Now let's write to scripts for mounting and umounting our encrypted drive:
chris@emeadb:~/bin$ cat mycryptmount
#!/bin/bash
export PATH=/usr/local/bin/:/bin/:/usr/bin/
echo "mounting encrypted drive"

df | grep maxtor_enc && sudo /home/chris/bin/mycryptumount
echo "maxtor_enc umounted"

sudo cryptsetup luksOpen /dev/disk/by-uuid/7a89446f-c43f-41b1-bc2b-99c74c64cdeb maxtor_encrypted
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime

chris@emeadb:~/bin$ cat mycryptumount
#!/bin/bash
export PATH=/usr/local/bin/:/bin/:/usr/bin/

sudo umount /dev/mapper/maxtor_encrypted
sudo cryptsetup luksClose maxtor_encrypted


chmod +x both of them

now you are able to mount with
$ mycryptmount

and umount with
$ mycrypumount

Sunday, 13 September 2009

Thursday, 28 May 2009

ruby oracle oci8 UTF8 corruption

I have a small script oci1.rb:
require 'oci8' 
conn = OCI8.new("test","test","test_db")
File.open("out.txt","wb") do |out|
tabs = conn.exec('SELECT * from test_utf8') do |r|
out.write(r.join(','))
out.write("\n")
end
end
conn.logoff

that runs perfectly on windows producing from my test_db:
polish,łóśżć 
russsian,фывафыва
german,äöü

when I run exactly the same on my linux box:
polish,loszc 
russsian,????????
german,aou

I tested then a small script that reads and writes UTF8 on linux to be sure my linux terminal can display UTF8
(read_write_utf8.rb):
File.open("utf8_out.txt","wb") do |out| 
File.open("utf8.txt","r").each do |line|
out.write(line)
end
end

and that works good!
chris@emeadb:~/work/ruby/oci$ ruby read_write_utf8.rb 
chris@emeadb:~/work/ruby/oci$ cat utf8.txt
polish,łóśżć
russsian,фывафыва
german,äöü
chris@emeadb:~/work/ruby/oci$ cat utf8_out.txt
polish,łóśżć
russsian,фывафыва
german,äöü


Finally the issue was with my NLS_LANG variable that was not set in the Linux box on the account I used for running the script.

I tried first to set it up in the ruby script itself:
ENV['NLS_LANG']='AMERICAN_AMERICA.UTF8'

that did not work.

the solution was to set it in the shell.
export NLS_LANG=AMERICAN_AMERICA.UTF8


you can set it in your .bashrc (depending on the shell you use) to make it default for the account.

Wednesday, 22 April 2009

debian change from dhcp to static IP

One would think to achieve the update from the DHCP assigned IP to the static one, the only thing to do is to update your /etc/network/interfaces

from:
iface eth0 inet dhcp

to:
iface eth0 inet static
address 10.165.248.252
netmask 255.255.248.0
gateway 10.165.248.1
broadcast 10.165.248.255
network 10.165.248.0

and restart:
/etc/init.d/networking restart

(optionally you can adjust your /etc/resolv.conf (to update DNS servers, and search paths)

The changed IP is immediately visible.

How surprised I was when after couple of hours the IP was changed back to the one from the DHCP pool. (resolv.conf was then automatically updated from the DHCP server)

Solution was to kill the dhcp3-client. (and change again the resolv.conf and restart networking)

Monday, 30 March 2009

ddl over database link in oracle 10gR2

recently I encountered a requirement to create a table on remote database DB_TARGET (working in DB_SOURCE)

for example:
create table table_a@dblink_to_db_target; --> does not work
-- ORA-02021: DDL operations are not allowed on a remote database

whereas
select 'hello' from dual@dblink_to_db_target; --> works

This is a workaround I used:
on TARGET_DB:
CREATE OR REPLACE 
FUNCTION prepare_table
( table_name in varchar2) return varchar2
IS
err_num NUMBER;
err_msg VARCHAR2(100);
sqlstmt varchar2(4000) := '';
tbl varchar2(30) := '';
BEGIN
if instr(table_name,'.') > 0 then
tbl := substr(table_name,instr(table_name,'.')+1);
sqlstmt := 'CREATE TABLE ' || tbl || ' as select * from ' ||
table_name;
execute immediate sqlstmt;
return 'table ' || tbl || ' successfully created';
else
return 'table in owners'' schema';
end if;
EXCEPTION
WHEN others THEN
err_msg := SUBSTR(SQLERRM, 1, 100);
return 'error when creating ' || tbl || '-' || err_msg || '-'
|| sqlstmt;
END;
/

this function creates a table specified as a table_name parameter only
if the "." is in the name => goal is to create a table in my_schema
from other user's schema.
this function works fine when calling this on DB_TARGET:
SQL> set serveroutput on; 
SQL> declare
2 result varchar2(300) := '';
3 begin
4 result := prepare_table('other_user.table_a');
5 dbms_output.put_line(result);
6 end;
7 /
table table_a successfully created

PL/SQL procedure successfully completed.

but then surprisingly to me:
when I try to call this function from DB_SOURCE:
SQL> set serveroutput on; 
SQL> declare
2 result varchar2(300) := '';
3 begin
4 result := prepare_table@dblink_to_db_target('other_user.table_a');
5 dbms_output.put_line(result);
6 end;
7 /
error when creating gcd_countries-ORA-02064: distributed operation not
supported-CREATE TABLE table_a as select * from other_user.table_a

PL/SQL procedure successfully completed.

I found a workaround with a wrapper procedure on DB_TARGET:
CREATE OR REPLACE 
PROCEDURE prepare_table_proc(table_name in varchar2)
IS
result varchar2(300) := '';
BEGIN
result := prepare_table(table_name);
dbms_output.put_line('success');
EXCEPTION
WHEN others THEN
dbms_output.put_line('err');
END;
/

when I call it from DB_SOURCE like this:
SQL> call prepare_table_proc@dblink_to_db_target('other_user.table_a'); 
Call completed.

And the table_a is created on DB_TARGET.

when we look closer why the function itself did not work we see in the description of the error message:
ORA-02064: distributed operation not supported
Cause: One of the following unsupported operations was attempted:
1. array execute of a remote update with a subquery that
references a dblink, or
2. an update of a long column with bind variable and an update of
a second column with a subquery that both references a dblink and a
bind variable, or
3. a commit is issued in a coordinated session from an RPC
procedure call with OUT parameters or function call.

in my case point 3. yields and hence the error message.

Oracle documentation comes with help in:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6148

it works only as a procedure and we cannot return a value to the invoking DB_SOURCE anyway.

maybe there's a better way...
right now I am happy with the workaround

Wednesday, 18 February 2009

why you should always use aliases in SQL (oracle)

let's create for this example following 2 tables (a and b):
create table a (org_id number, org_name varchar2(1)); 
insert into a values(1,'a');
insert into a values(2,'b');
insert into a values(3,'c');
commit;

create table b (name varchar2(1)); 
insert into b values('a');
insert into b values('b');
commit;


Now let's run this:
select * from a 
where a.org_id in (select org_id from b);
-- returns all 3 rows from a

select * from a
where a.org_id not in (select org_id from b);
-- returns 0 rows


The tricky part is, you might want the org_id in the subquery to be taken from b (which does not exist) and hence expect an INVALID IDENTIFIER error message as running only subquery:
select org_id from b 
*
ORA-00904: "ORG_ID": invalid identifier


But the initial query is correlated and actually the org_id in the subquery comes from outer table a and not from b.

That's why a good practice would be always to use aliases as prefixes to the columns.

In that way we can rewrite the query that will now raise an exception as expected:

select * from a 
where a.org_id not in (select b.org_id from b);
-- ORA-00904: "B"."ORG_ID": invalid identifier

Thursday, 22 January 2009

vim converting between MS Unicode UCS-2LE and UTF-8

Many times at work I need to convert between 2 different encodings from Unicode:
1. MS Unicode -> generated e.g. by Excel when Saving data as Unicode text
2. UTF8 encoding (standard)

Requirement VIM is compiled with multi_byte support.
check if in VIM normal mode
:echo has('multi_byte')

returns 1

In VIM normal mode to open a file with certain encoding:
:e ++enc=<encoding> <filename>

To save in different encoding:
:w ++enc=<encoding> <filename>

e.g.
open in ucs-2le -> save in utf-8
:e ++enc=ucs-2le /tmp/file_ucs2le.csv
:w ++enc=utf-8 /tmp/file_utf8.csv

It works even if your fileencoding in your .vimrc (_vimrc on Windows) is different and VIM normally would recognize as different encoding from the one you want.

++enc sets fileencoding and not encoding (filencoding is needed when opening or saving the file)

More on Unicode in VIM under
http://vim.wikia.com/wiki/Working_with_Unicode

Tuesday, 20 January 2009

compiling VIM 7.2 with GTK2 under debian etch

To install GTK2 is not an issue since we can use the apt-get install libgtk2.0 libgtk2.0-dev to install gtk2 with dev libraries.

The issue I encountered was when I tried to compile VIm 7.2 with GTK2, which could not be found:
checking for GTK - version >= 2.2.0... no
checking for GTK - version >= 1.1.16... yes; found version 1.2.10

Then I decided to compile GTK2 on my own.

I failed when trying to install GTK2.14 with GLIB2.18.4 because some function definitions changed.

Finally the versions I successfully installed were:
- gtk2.8.20
- glib2.9.6
- pangoo1.11
- atk1.25.2
- cairo1.8.6
- pixman0.13.2

then to compile VIM i used (/opt/gtk2 is the prefix where I installed just compiled gtk2):
export PKG_CONFIG_PATH="/opt/gtk2/lib/pkgconfig"
export LD_LIBRARY_PATH=/opt/gtk2/lib
export CPPFLAGS="-I/opt/gtk2/include"
export LDFLAGS="-L/opt/gtk2/lib"
./configure --prefix=/opt/vim/ --enable-perlinterp --enable-rubyinterp --enable-multibyte --enable-gui --with-features=big

Finally GTK2 found:
checking for GTK - version >= 2.2.0... yes; found version 2.8.20

happy VImming :)