using oraperl with Oracle 7 (revised)

This is a revised version of an earlier post. It incorporates some feedback I received after the first version, and has been crossposted to comp.lang.perl. I am not planning to post any further revised versions.

Although Oracle has issued a press release claiming that oraperl was written by Kevin Stock "for use with Oracle7 databases", Kevin Stock has made it clear in a response to the earlier version of this post that he has never had access to Oracle v7, and that oraperl was written for v6. This post details some of the hoops that I had to jump through in order to install oraperl 2.4, using perl 4.036, SunOS 4.1.4, and Oracle 7.1.4. Many, but not all, of the comments in this post describe problems that you will run into on any operating system. In particular, Charles Jardine <cj10_at_ucs.cam.ac.uk> points out that the makefile for Solaris 2 is different. I would like to thank Kevin Stock, Charles Jardine, and Tim Bunce for their helpful correspondences during this process.

1. mus

Look at the first line of the file $(SRC)/usub/mus (where $(SRC) is the root directory of your perl installation). It will say #!/usr/bin/perl
even if you don't keep perl in /usr/bin. This is true, independent of what you do during the configuration and installation of perl. Change this line so that the script will look for perl in the right place. Note that there is sometimes a limit on the length of the pathname+executable string that is allowed on this line.

If you ran make while $(SRC)/usub/mus was looking for perl in the wrong place, then there will be an empty oracle.c file in the directory containing your oraperl source. It is imperative that you remove this file. Go to the directory that contains your oraperl source, and type "make clean". That should get rid of it.

Please take care of this step BEFORE trying to get the correct makefile. Otherwise, an incorrect mus script can be a very hard problem to find. In comment number 2, we will see that when compiling oraperl for Oracle 7, you have to arrange the libraries to eliminate the unresolved symbols. However, having an incorrect mus script can also cause unresolved symbols. It is easy to assume that the last remaining unresolved symbol is also Oracle's fault, and to spend a substantial amount of time trying to figure out what is wrong with your makefile, when in fact you have a problem with this perl script.

2. The makefile

The first thing you discover when you start the installation process is that the makefile that comes with the distribution looks for Oracle's libraries in the wrong place. This, of course, is because Oracle moved the libraries when it went from 6 to 7. Well, hidden away in a dark corner of Oracle's web site, you will find a distribution of oraperl. The URL is

http://dozer.us.oracle.com:8080/ftp/util/perl/

You can get to there by going to http://www.oracle.com, then "Worldwide Web Interface Kit", then "Download it!", then "browse the Perl archive".

The oraperl-v2.4.tar.Z file in this directory is, as far as I can tell, the same as it is anywhere. However, in the same directory, you will find a file, Makefile.oraperl.v7 ! This is a Makefile written and made available by some merciful Oracle employee (I phrase it like that, because tech support has assured me that Oracle as an corporation has never even heard of oraperl).

This makefile is especially nice, because it makes it clear how to turn the Oracle 7 "deferred" "feature" on and off. The author of the makefile forgot that not everybody has their environment variables set up so that the compiler will automatically look for libraries in the Oracle distribution, so I had to add the line

LDFLAGS=-Bdynamic -L$(LIBHOME)

and then LDFLAGS had to be put in the compilation command:

oraperl: $(SRC)/uperl.o $(OOBJS)

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

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

If this doesn't work (perhaps because you are not using SunOS 4.1.4), or if you are determined to write your own makefile, I suggest you get a copy of

Bulletin 104220.448, Understanding and Modifying Precompiler and OCI Makefiles

A good way to check that you have a good makefile is to compile cdemo1.c using oracle.mk, look at the command line that is used to do this compilation, and then make sure that you are linking to the same libraries and in the same order when you try to make oraperl.

It is possible to get one final unresolved symbol if DEBUG is set to some option that requires perl to have been compiled in a way that allows debugging. I don't know much about this; if you get a single unresolved symbol that is related to debugging, try changing DEBUG. Here are some comments about this point from Kevin Stock:

Kevin Stock <kstock_at_auspex.com> wrote:

I don't know why, but this just started being an issue a few months ago. Admittedly, I could have documented it more clearly.

Simply put, Oraperl includes a mechanism for generating debugging traces which uses the DBUG package released by Fred Fish. In order to arrange for some integration with Perl's debugging, I included code for Oraperl's debugging state to be initialised from Perl's -D flag. However, not everyone compiled Perl with debugging included, so this is a compile-time option.

The rules are:

	If you don't want debugging, leave DEBUG blank
	If you want Oraperl debugging, but don't have Perl debugging,
		set DEBUG to -DDEBUGGING
	If you want Oraperl debugging, and you have Perl debugging,
		set DEBUG to -DPERL_DEBUGGING

However, the debugging code makes some assumptions which are not true for Oracle v7 (in particular the datatypes) which can lead to core dumps, so for v7, it's better to compile without debugging.

(End of quote)

3. the system-dependent data structure

To understand this section, you really need a good grasp of C. If you have trouble with this explanation, you might want to try to find somebody who has some C experience to take a look at it.

In the oraperl source, in orafns.h, you will find that "struct csrdef" contains a comment that it is Operating system dependent. To ensure that it is correct for your operating system, you should glance briefly at your Installation and Configuration Guide (page 15-6 for me). Then, for the real work, compare csrdef to struct cda_def in the file $(ORACLE_HOME)/rdbms/demo/ocidfn.h, and make sure that they represent the same structure (Although they have different field names, the size and order of the fields must be the same). To do this, you will have to find the #define statements in ocidfn.h that describe what oracle means by "sb2", "sword", "dvoid", and so on. Finally, you will have to play some sort of trick to pad the resulting structure out to be 64 bytes long. oraperl does this by hard coding in the number of bytes it needs to pad it out to 64 bytes in the last field, crsfill. Oracle does it by making two copies of the structure, calling one of them cda_head, and then making the size of the last field in the second copy ("cda_def") dependent on the size of cda_head. here is what I do:

   unsigned char crsfill[64-sizeof(struct csrdef_head)]; /* private, reserved fill */

4. orlon freezing?

Bug report number 227321 describes a problem with 7.1.3, that causes applications that set environment variables and then call orlon to freeze. oraperl is one such application. Both the bug report and Oracle Technical Support (and the LANGUAGE group in particular) maintain that the problem was fixed in version 7.1.3, and does not exist in version 7.1.4. However, the bug report contains some code that was used to demonstrate the problem, and in my installation of version 7.1.4, this bug still seems to manifest itself. In response to a recent post, C.J. Jardine independently confirmed the existence of this bug:

C.J. Jardine <cj10_at_cam.ac.uk> wrote:

The bug is indeed still there in 7.1.4 under SunOs 4.1.x
(in my case 4.1.2).

Oracle support seem to think that the bug has something to do with Motif libraries. This is not true.

The bug does not seem to be present in 7.1.4 under Solaris 2.4. U.K. tech. support say bug is fixed in 7.1.6 under 4.1.x.

(End of quote)

To work around this bug, do not set any environment variables in your oraperl script before you call ora_login, and when you do call ora_login, the first argument must be the empty string. This means that you have to be sure that your environment variables ORACLE_SID and ORACLE_HOME are set properly before you execute any oraperl script. It is probably also possible to pass the SID to ora_login as part of the username (for example, ora_login("", "SCOTT/TIGER_at_PROD", "")), although I have not tested this.

This workaround is based on information from Kevin Stock.

Kevin Stock <kstock_at_auspex.fr> wrote:

If you pass an empty string as the first argument to &ora_login(), then Oraperl won't touch your environment. If you pass PROD, then it will set ORACLE_SID to PROD.

The rules for the first argument are:

	if it is blank, do nothing
	otherwise, if it contains a colon, assign it to TWO_TASK
	otherwise, assign it to ORACLE_SID

The rationale is that by making the ORACLE_SID an argument to &ora_login() instead of an environment variable, it is easier to make programs more robust (since they no longer rely on the environment being correct, and so can be used by people whose default ORACLE_SID is different) and also to write programs which access multiple databases.

Kevin

(End of quote)

5. As a bonus, here is a bug report about a bug in the bug reporting service itself.

WorldWide Support Requestor <supreq_at_us.oracle.com> wrote:

From: WorldWide Support Requestor <supreq_at_us.oracle.com> To: EMJORDAN_at_MEDG.LCS.MIT.EDU
Subject: bug 40821 216136 261162 165442 212785 165423 72161 40821 38907
(EMJORDAN_at_MEDG.LCS.MIT.EDU's SUPREQ request)

    We are currently experiencing a RDBMS bug that will not all WWREQ to query more than one bug at a time with the BUG service. We upgraded to the new 7.1 version of the bug database and have encountered some problems with this functionality. It is assigned at a high priority and will be worked on as soon as possible. There is a workaround, but we anticipate that this functionality will be back shortly. Thanks for your patience.

    This is the problem: (where nnnnn represents a bug number)

        bug nnnnn nnnnn

        will generate an oracle 600 error.

    The Workaround: split your multiple requests into separate lines in your

                     mail note to WWREQ.

        bug nnnnn
        bug nnnnn

(End of quote)

Eric
emjordan_at_medg.lcs.mit.edu Received on Fri May 26 1995 - 00:00:00 CEST