Oraperl proplem and its circumvention.

I have recently encountered a serious problem using the latest version of oraperl linked with the OCI libraries shipped with Oracle 7.0.12.

The symptom is that, when oraperl is used to process a SELECT statement with more than 16 items in its select list, the first column of the result is corrupted.

The cause of the problem is a bug in Oracle. The bug is present in 7.0.12 and 7.0.13, but not in Oracle version 6. It causes the OCI routine odsc (or odescr) to give wrong answers in certain circumstances.

I have discovered that the problem can be circumvented by re-linking oraperl in "non-deferred mode". Under SunOS this is done by including $ORACLE_HOME/rdbms/lib/kpundf.o in the link.

I would strongly advise anyone using oraperl with Oracle7 to re-link their oraperl.

Since several people have asked how to build oraperl for Oracle7, and since it is not obvious, I have taken the liberty of including the Makefile I used in this posting. It is for Oracle 7.0.12 running under SunOS 4.1.2, and will need modification for other versions of Oracle or other OSs.

Note that the underlying perl must be system V flavoured. Building a system V perl under SunOS 4.1.x is another problem.

Charles Jardine, University of Cambridge (UK) Computing Service.

Cut here

# Change these to your ORACLE installation directory and Perl source directory
#

ORACLE_HOME	= /jackdaw/oracle
SRC		= /jackdaw/perl/perl-4.035

# The following lines are extracted from $ORACLE_HOME/rdbms/lib/oracle.mk
# They must be checked whenever a new version of Oracle is installed,
# as the contents of oracle.mk may change.

LIBHOME=$(ORACLE_HOME)/lib

CC=/usr/5bin/cc

LDFLAGS=-Bdynamic -L$(LIBHOME)

LIBCORE=$(LIBHOME)/libcore.a

LLIBCORE=-lcore

LLIBORA=-lora

OSNTAB= $(LIBHOME)/osntab.o

LIBOCIC=$(LIBHOME)/libocic.a

OTHERLIBS=`cat $(ORACLE_HOME)/rdbms/lib/sysliblist`

LIBSQLNET=$(LIBHOME)/libsqlnet.a
LLIBSQLNET=-lsqlnet

NETLIBS=$(OSNTAB) $(LLIBSQLNET) TTLIBS= $(NETLIBS) $(LLIBORA) $(LLIBSQLNET) CLIBS=$(OTHERLIBS) NDFOPT= $(ORACLE_HOME)/rdbms/lib/kpundf.o

OCILDLIBS= $(LIBOCIC) $(TTLIBS) $(LLIBCORE)
# end of extract from oracle.mk

ALL_ORA_LDFLAGS = $(LDFLAGS)
# Note the inclusion of NDFOPT in the following. It causes oraperl to
# be linked in non-deferred mode. This is a workround for a bug present
# in both 7.0.12 and 7.0.13. The symptom of this bug is that when oraperl
# processes a select yielding more than 16 columns, the first column
# is processed incorrectly.

ALL_ORA_LIBS = $(NDFOPT) $(OCILDLIBS) $(CLIBS)
# Perl Definitions, taken from $SRC/usub/Makefile
# Don't include the curses libraries here - they go in CURSELIB
#

GLOBINCS	= 
LOCINCS		= 
LIBS		= `. $(SRC)/config.sh; echo $$libs`

# Oraperl Definitions

# Set DEBUG to -DDEBUGGING, -DPERL_DEBUGGING or leave blank (see orafns.h)
# If it is not blank, uncomment the definition of DBUG_O
#

DEBUG		= 


#DBUG_O		= dbug/dbug.o

# Curses libraries, only required if you want to build Coraperl
# You may also need -ltermlib or -ltermcap
#

CURSELIB = -lcurses

# Row cache size for SELECT statements.
# If you want to change the default, uncomment this and set the value you want
#
#CACHE = -DCACHE_SIZE=5

# Bind variable padding.
# If you want binding an empty string to provoke an error, uncomment this line.
#
#BIND = -DNO_BIND_PADDING

# Some system-specific things
#
# If your system library does not include strtoul, uncomment the next line
STRTOUL = strtoul.o
#
# If your malloc() returns anything other than a char *, set the appropriate
# type here (don't include the *)

MALLOC_PTR_TYPE=void
#
# If you are using Perl v3 instead of v4, uncomment the next line
# STR_2MORTAL = -Dstr_2mortal=str_2static

# Database, username and password to use for testing
#

TESTDATA = t scott tiger

#
# Leave these blank lines so that patches to what is below
# won't be upset by your changes to the setups above.
#

# From here on, you shouldn't need to change anything. If you do, let me know.

SRCS		= oracle.mus orafns.c getcursor.c colons.c usersub.c \
		  debug.c strtoul.c
OBJS		= oracle.o orafns.o getcursor.o colons.o debug.o $(STRTOUL)
OOBJS		= $(OBJS) usersub.o $(DBUG_O)
COBJS		= $(OBJS) cusersub.o $(DBUG_O)
HDRS		= patchlevel.h orafns.h
DEFS		= $(STRTOL) $(PUTENV) $(STR_2MORTAL) $(DEBUG) $(CACHE) $(BIND)

CFLAGS		= -Idbug -I$(SRC) $(GLOBINCS) $(LOCINCS) $(DEFS)

oraperl: $(SRC)/uperl.o $(OOBJS)
	$(CC) $(ALL_ORA_LDFLAGS) -o oraperl $(SRC)/uperl.o $(OOBJS)			\
	      -lm $(ALL_ORA_LIBS) $(LIBS)

coraperl: $(SRC)/uperl.o $(COBJS) $(SRC)/usub/curses.o
	$(CC) $(ALL_ORA_LDFLAGS) -o coraperl $(SRC)/uperl.o $(COBJS) \
		$(SRC)/usub/curses.o	\
	      -lm $(ALL_ORA_LIBS) $(LIBS) $(CURSELIB)

all:	oraperl coraperl

test:	oraperl
	_at_oraperl -e '&ora_version'
	_at_(cd testdir ;							      \
	  rm -f My-Results ;						      \
	  echo "Testing oraperl, please wait ..." ;			      \
	  for i in *.pl	; do ../oraperl $$i $(TESTDATA) ; done > My-Results ; \
	  if cmp -s Standard-Results My-Results ;			      \
	  then echo "Test successful" ;					      \
	  else echo "Test failed - compare My-Results with Standard-Results" ;\
	  fi; echo)

# We use oraperl although perl would suffice, because we know where it is!

install:	oraperl install.pl
	_at_./oraperl ./install.pl ${SRC}

cusersub.c:	usersub.c
	_at_rm -f cusersub.c
	ln usersub.c cusersub.c

cusersub.o:	cusersub.c
	$(CC) -c $(CFLAGS) -DCURSES cusersub.c

oracle.c: $(SRC)/usub/mus oracle.mus
	perl $(SRC)/usub/mus oracle.mus >oracle.c

$(OOBJS) $(COBJS):	$(HDRS)

dbug/dbug.o:
	(cd dbug ; $(MAKE) dbug.o)
	_at_echo "	(back to main directory)"

clean:
	(cd dbug ; $(MAKE) clean)
	_at_echo "	(back to main directory)"
	rm -f nohup.out *.o oracle.c cusersub.c
	rm -f testdir/My-Results listing tags core

realclean clobber:	clean
	(cd dbug ; $(MAKE) clobber)
	(cd doc ; $(MAKE) clobber)
	_at_echo "	(back to main directory)"
	rm -f oraperl coraperl

listing:
	pr -fn Makefile $(HDRS) $(SRCS) >listing

docs:
	(cd doc ; $(MAKE) docs)
	_at_echo "	(back to main directory)"

shar:	clean
	shar -n oraperl-v2 -a -s kstock_at_encore.com -F -o :Part -l 64	\
		Readme [C-Q]* Row* [S-z]*