Some OCI8 comments

Brian_Candler · 1 May 2003 14:58

Some notes/comments on ruby-oci8-0.1.3 which I’ve just been struggling to
install on a Solaris 2.8/Oracle 9.2.0 system - may be helpful to anyone else
attempting the same.

Firstly it was failing to compile as follows:

checking for OCIInitialize()… no
./oraconf.rb:110:in initialize': cannot compile OCI (RuntimeError) from extconf.rb:4:in new’
from extconf.rb:4

$ tail -3 mkmf.log
ld: fatal: file /u01/app/oracle/product/9.2.0/lib//libclntsh.so: wrong ELF class: ELFCLASS64
ld: fatal: File processing errors. No output written to conftest
collect2: ld returned 1 exit status

The problem here is one I’ve come across before: Oracle is looking in
…/lib when it should be looking in …/lib32

I have the linker environment right:

$ crle
crle -c /var/ld/ld.config -l /usr/lib:/usr/local/lib:/usr/local/ssl/lib:/u01/app/oracle/product/9.2.0/lib32:/u01/app/oracle/product/9.2.0/rdbms/lib32

The solution is to edit src/oraconf.rb, change
‘NOKPIC_CCFLAGS=’ to ‘NOKPIC_CCFLAGS32=’ and ‘build’ to ‘build32’ in these
two places:

      make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS32="

…
command = “|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk build32 #{make_opt}”

After that it compiles!

The other suggestion I have is a patch for OraDate (part of this has been
submitted already). The attached patch does two things:

adds a compare (<=>) operator. In particular this allows you to do
if date1 == date2 …
which otherwise fails.
changes the default to_s format from
YYYY/MM/DD HH:MM:SS
to YYYY-MM-DD HH:MM:SS

The latter is, I believe, a more accurate ISO date format. It also happens
to be the same as mysql provides.

Regards,

Brian.

ruby-oci8-oradate.patch (2.67 KB)

Brian_Candler · 2 May 2003 09:49

changes the default to_s format from
YYYY/MM/DD HH:MM:SS
to YYYY-MM-DD HH:MM:SS

The latter is, I believe, a more accurate ISO date format. It also happens
to be the same as mysql provides.

Actually, I have come across a problem here, trying to insert dates which
were exported as CSV from another database; e.g. inserting string
“2003-05-02 10:45:00” into an Oracle DATE column, I get:

/usr/local/lib/ruby/site_ruby/1.6/DBD/OCI8/OCI8.rb:37:in `raise_error’: ORA-01861: literal does not match format string (DBI::DatabaseError)

Does anyone know what ‘format string’ Oracle is expecting? If I try
“2003/05/02 10:45:00” it doesn’t work either.

Of course I can insert
TO_DATE(?,‘YYYY-MM-DD HH24:MI:SS’)

but I have a generic table-loader which just takes a CSV file and just uses
INSERT on each row… it would be nice for string-format dates to be mapped
into DATE columns automatically.

Cheers,

Brian.

P.S. I see that the OCI8 DBD in ruby-oci8-0.1.3 supports mapping of Oracle
error codes to the appropriate DBI exceptions - excellent!

KUBO_Takehiro · 2 May 2003 13:53

Brian Candler B.Candler@pobox.com writes:

Some notes/comments on ruby-oci8-0.1.3 which I’ve just been struggling to
install on a Solaris 2.8/Oracle 9.2.0 system - may be helpful to anyone else
attempting the same.
(snip)
The solution is to edit src/oraconf.rb, change
‘NOKPIC_CCFLAGS=’ to ‘NOKPIC_CCFLAGS32=’ and ‘build’ to ‘build32’ in these
two places:
      make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS32="
…
command = “|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk build32 #{make_opt}”

After that it compiles!

Does gcc make 32 bit object in any case? If so, I’ll change oraconf.rb
as following.

— oraconf.rb~ 2003-03-08 22:29:01.000000000 +0900
+++ oraconf.rb 2003-05-02 22:45:05.000000000 +0900
@@ -211,12 +211,18 @@
STDOUT.flush

  make_opt = "CC='echo MARKER' EXE=/dev/null ECHODO=echo ECHO=echo GENCLNTSH='echo genclntsh'"

make_target = 'build'
if @cc_is_gcc && /solaris/ =~ RUBY_PLATFORM
  # suggested by Brian Candler.

  make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS="

```
  if /92./ =~ @version 
```

    make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS32="

```
    make_target = 'build32'
```
```
  else
```

    make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS="

```
  end
end
```

command = "|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk build #{make_opt}"

command = "|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk #{make_target} #{make_opt}"
marker = /^\s*MARKER/					 
echo = /^\s*echo/
libs = nil

The other suggestion I have is a patch for OraDate (part of this has been
submitted already). The attached patch does two things:

adds a compare (<=>) operator. In particular this allows you to do
if date1 == date2 …
which otherwise fails.

Thanks. I accept your patch about this.

changes the default to_s format from
YYYY/MM/DD HH:MM:SS
to YYYY-MM-DD HH:MM:SS

The latter is, I believe, a more accurate ISO date format. It also happens
to be the same as mysql provides.

Hmm, I don’t accept your patch about this.

If I change that format, I’ll do it on major version up.
On minor version up users will expect that the program running on
older version will run on newer version without change. Your patch may
cause problems on some other programs.

In addition OraDate will be obsolete or deprecated on the next major
release. OCIDate (see $ORACLE_HOME/rdbms/demo/orl.h) or OCI::Date will
be used instead of OraDate. You can change default format as following.
OCIDate.format = ‘YYYY-MM-DD HH24:MM:SS’

···

–
KUBO Takehiro

KUBO_Takehiro · 2 May 2003 13:55

Brian Candler B.Candler@pobox.com writes:

Actually, I have come across a problem here, trying to insert dates which
were exported as CSV from another database; e.g. inserting string
“2003-05-02 10:45:00” into an Oracle DATE column, I get:

/usr/local/lib/ruby/site_ruby/1.6/DBD/OCI8/OCI8.rb:37:in `raise_error’: ORA-01861: literal does not match format string (DBI::DatabaseError)

Does anyone know what ‘format string’ Oracle is expecting? If I try
“2003/05/02 10:45:00” it doesn’t work either.

How to check ‘format string’:
SELECT value FROM v$nls_parameters WHERE parameter = ‘NLS_DATE_FORMAT’;
Default date format of Oracle is ‘DD-MON-RR’.

Of course I can insert
TO_DATE(?,‘YYYY-MM-DD HH24:MI:SS’)

Or you can change ‘format string’ by the following SQL:
ALTER session SET nls_date_format = ‘YYYY/MM/DD HH24:MI:SS’;
or
ALTER session SET nls_date_format = ‘YYYY-MM-DD HH24:MI:SS’;

P.S. I see that the OCI8 DBD in ruby-oci8-0.1.3 supports mapping of Oracle
error codes to the appropriate DBI exceptions - excellent!

Oracle error codes are too many. I just cover a little of them.
You can see all of the Oracle error codes on $ORACLE_HOME/rdbms/mesg/oraus.msg.

Cheers

···

–
KUBO Takehiro

Brian_Candler · 2 May 2003 14:42

Does gcc make 32 bit object in any case? If so, I’ll change oraconf.rb
as following.

Unfortunately I know little about Solaris, and I don’t have a Sun ‘cc’ to
compare with ‘gcc’

I’m not convinced it’s specific to Oracle 9.2 problem. The box where I had
this problem was running gcc 3.1. I’ve just gone back to the older box
(running 9.0.1 / gcc 2.95.3) and compiled ruby-oci8-0.1.3 and it does
compile without patching. However the demo.mk has both ‘build’ and ‘build32’
targets, just the same as Oracle 9.2

It’s not impossible that I had frigged something manually in those Makefiles
a long time ago to make it compile, I can’t be sure; or it might be a gcc
2.95.3/3.1 difference.

Perhaps it would be better as an option to extconf.rb, like
–force-32bit
or something.

The latter is, I believe, a more accurate ISO date format. It also happens
to be the same as mysql provides.

Hmm, I don’t accept your patch about this.

If I change that format, I’ll do it on major version up.
On minor version up users will expect that the program running on
older version will run on newer version without change. Your patch may
cause problems on some other programs.

In addition OraDate will be obsolete or deprecated on the next major
release. OCIDate (see $ORACLE_HOME/rdbms/demo/orl.h) or OCI::Date will
be used instead of OraDate. You can change default format as following.
OCIDate.format = ‘YYYY-MM-DD HH24:MM:SS’

That solves the problem nicely, thank you.

Cheers,

Brian.

···

On Fri, May 02, 2003 at 10:53:46PM +0900, KUBO Takehiro wrote:

Brian_Candler · 2 May 2003 14:47

Works perfectly. Thank you.

Regards,

Brian.

···

On Fri, May 02, 2003 at 10:55:16PM +0900, KUBO Takehiro wrote:

Or you can change ‘format string’ by the following SQL:
ALTER session SET nls_date_format = ‘YYYY/MM/DD HH24:MI:SS’;
or
ALTER session SET nls_date_format = ‘YYYY-MM-DD HH24:MI:SS’;

KUBO_Takehiro · 3 May 2003 08:44

Brian Candler B.Candler@pobox.com writes:

Does gcc make 32 bit object in any case? If so, I’ll change oraconf.rb
as following.

Unfortunately I know little about Solaris, and I don’t have a Sun ‘cc’ to
compare with ‘gcc’

I’m not convinced it’s specific to Oracle 9.2 problem. The box where I had
this problem was running gcc 3.1. I’ve just gone back to the older box
(running 9.0.1 / gcc 2.95.3) and compiled ruby-oci8-0.1.3 and it does
compile without patching. However the demo.mk has both ‘build’ and ‘build32’
targets, just the same as Oracle 9.2

It’s not impossible that I had frigged something manually in those Makefiles
a long time ago to make it compile, I can’t be sure; or it might be a gcc
2.95.3/3.1 difference.

Perhaps it would be better as an option to extconf.rb, like
–force-32bit
or something.

Hmm, how about the follow patch.
If error on ‘build’, add ‘32’ as postfix and retry.
It is available both Sun cc and gcc, I assume.

— ruby-oci8-0.1.3.old/src/oraconf.rb 2003-03-08 22:29:01.000000000 +0900
+++ ruby-oci8-0.1.3/src/oraconf.rb 2003-05-03 14:40:17.000000000 +0900
@@ -103,9 +103,18 @@
@libs = get_libs()

  $CFLAGS += ' ' + @cflags

$libs += " -L#{CONFIG["libdir"]} " + @libs

$libs += " -L#{CONFIG['libdir']} " + @libs
unless have_func("OCIInitialize", "oci.h")

```
  raise 'cannot compile OCI' 
```

```
  ok = false
```
```
  case RUBY_PLATFORM
```
```
  when /solaris/
```
```
    if /9../ =~ @version
```
```
  	@libs = get_libs('32')
```

  	$libs += " -L#{CONFIG['libdir']} " + @libs

  	ok = true if have_func("OCIInitialize", "oci.h")

```
    end
```
```
  end
```
```
  raise 'cannot compile OCI' unless ok
end
```
ensure
$CFLAGS = original_CFLAGS
@@ -206,18 +215,18 @@
end
end # get_cflags

def get_libs

print("Running make for $ORACLE_HOME/rdbms/demo/demo_rdbms.mk ...")

def get_libs(postfix = ‘’)

print("Running make for $ORACLE_HOME/rdbms/demo/demo_rdbms.mk (build#{postfix}) ...")
STDOUT.flush

make_opt = "CC='echo MARKER' EXE=/dev/null ECHODO=echo ECHO=echo GENCLNTSH='echo genclntsh'"
if @cc_is_gcc && /solaris/ =~ RUBY_PLATFORM
  # suggested by Brian Candler.

  make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS="

  make_opt += " KPIC_OPTION= NOKPIC_CCFLAGS#{postfix}="
end

command = "|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk build #{make_opt}"

```
marker = /^\s*MARKER/					 
```

command = "|make -f #{@oracle_home}/rdbms/demo/demo_rdbms.mk build#{postfix} #{make_opt}"

marker = /^\s*MARKER/
echo = /^\s*echo/
libs = nil
Logging::open do

···

On Fri, May 02, 2003 at 10:53:46PM +0900, KUBO Takehiro wrote:

–
KUBO Takehiro

Topic		Replies	Views
Oracle, ruby-oci8 and gcc ruby-talk	4	186	15 February 2003
Resources for installation problems ruby-talk	0	82	21 July 2003
[ANN] ruby-oci8 1.0.2 ruby-talk	10	173	3 July 2008
[ANN] ruby-oci8 2.0.4 ruby-talk	0	136	28 February 2010
[ANN] ruby-oci8 2.0.5 ruby-talk	0	137	12 June 2011

Some OCI8 comments

Related topics