1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml" id="sixapart-standard">
5 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
6 <meta name="generator" content="Movable Type 5.2.3" />
8 <link rel="stylesheet" href="http://defaria.com/blogs/Status/styles-site.css" type="text/css" />
9 <link rel="alternate" type="application/atom+xml" title="Atom" href="http://defaria.com/blogs/Status/atom.xml" />
10 <link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://defaria.com/blogs/Status/index.xml" />
12 <title>Status for Andrew DeFaria: GPDB Web Update/Oracle testing</title>
14 <link rel="start" href="http://defaria.com/blogs/Status/" title="Home" />
15 <link rel="prev" href="http://defaria.com/blogs/Status/archives/000624.html" title="MySQL Client Libraries" />
16 <link rel="next" href="http://defaria.com/blogs/Status/archives/000626.html" title="GPDB Login" />
19 <rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
20 xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"
21 xmlns:dc="http://purl.org/dc/elements/1.1/">
23 rdf:about="http://defaria.com/blogs/Status/archives/000625.html"
24 trackback:ping="http://defaria.com/mt/mt-tb.cgi/14"
25 dc:title="GPDB Web Update/Oracle testing"
26 dc:identifier="http://defaria.com/blogs/Status/archives/000625.html"
27 dc:subject="Texas Instruments"
28 dc:description=" Added menus to GPDB web Tested and documented access of Oracle databases, such as GPDB, using standard Perl and standard Oracle libraries from various architectures..."
30 dc:date="2007-03-21T16:52:37-06:00" />
37 <script type="text/javascript" src="http://defaria.com/blogs/Status/mt-site.js"></script>
39 <body class="layout-one-column" onload="individualArchivesOnLoad(commenter_name)">
41 <div id="container-inner" class="pkg">
44 <div id="banner-inner" class="pkg">
45 <h1 id="banner-header"><a href="http://defaria.com/blogs/Status/" accesskey="1">Status for Andrew DeFaria</a></h1>
46 <h2 id="banner-description">Searchable status reports and work log</h2>
51 <div id="pagebody-inner" class="pkg">
53 <div id="alpha-inner" class="pkg">
55 <p class="content-nav">
56 <a href="http://defaria.com/blogs/Status/archives/000624.html">« MySQL Client Libraries</a> |
57 <a href="http://defaria.com/blogs/Status/">Main</a>
58 | <a href="http://defaria.com/blogs/Status/archives/000626.html">GPDB Login »</a>
62 <div class="entry" id="entry-625">
63 <h3 class="entry-header">GPDB Web Update/Oracle testing</h3>
64 <div class="entry-content">
65 <div class="entry-body">
67 <li>Added menus to GPDB web</li>
69 <li>Tested and documented access of Oracle databases, such as GPDB, using standard Perl and standard Oracle libraries from various architectures</li>
72 <div id="more" class="entry-more">
73 <p>Joe Ramey had worked on getting the Oracle Linux client API library to work with Perl. I am trying to validate that this works on all supported architectures where <i>all supported architectures</i> is considered the subset of:</p>
75 <li>Solaris (e.g. Stashu)</li>
76 <li>Linux 32-bit (e.g. Fad01)</li>
77 <li>Linux 64-bit AMD Opteron processor (e.g. DrTeeth)</li>
78 <li>Linux 64-bit Intel processor (e.g. Fad11)</li>
81 <h3>How to properly code Perl to access an Oracle DB</h3>
83 <p>Normal access to SQL databases in Perl usually consists of use'ing two different Perl modules. First a DBI.pm module which is the<i> <b>D</b>ata<b>B</b>ase <b>I</b>ndependent</i> interface and the DBD or <b><i>D</i></b><i>ata<b>B</b>ase <b>D</b>river</i> module for the particular SQL database involved. We are interested in Oracle here but others include mSQL, MySQL, etc.</p>
85 <p>Couple this with the fact that we wish to use the standard Perl (e.g. /apps/perl/5.8.3/bin/perl) and standard Perl modules (/apps/perl/modules-<<i>datecode</i>> where <i>datecode</i> is some combination of a year/month date code whose apparent LCD seems to be modules-0412).</p>
87 <p>So first our Perl script should start with:</p>
90 <font color="#3333ff">#!/apps/perl/5.8.3/bin/perl</font>
91 <font color="#ff0000">use strict</font>;
92 <font color="#ff0000">use warnings</font>;
94 <font color="#3333ff"># Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm</font>
95 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/perl/modules-0412/lib</font>";
98 <p>So as the comment indicates we are picking up the DBI.pm module from here. Next, normally, we'd simply include the lib path for the Oracle DBD.pm (or in the special case of Oracle, the Oracle.pm file) as well as the supporting shared library Oracle.so (which is architecturally dependent) by coding the following:</p>
100 <div class=code><pre>
101 <font color="#3333ff"># Perl/Oracle libraries</font>
102 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/oracle/perl/10.2.0.1.0/lib</font>";
105 <p>However, since Linux client support is not included in /apps/oracle/perl/10.2.0.1.0/lib (yet) we need to pick up Joe's stuff:</p>
107 <div class=code><pre>
108 <font color="#3333ff"># Linux API is in Ramey's home dir...</font>
109 <font color="#ff0000">use lib</font> "<font color="#009900">/home/ramey/oracle/perl/10.2.0.1.0/lib</font>";
112 <p>The next excerpt from my code, shown in it's entirety later, shows a lib path I used to use that allows Solaris clients to work correctly, however we wish to move away from this special cased library thus it's commented out here:</p>
114 <div class=code><pre>
115 <font color="#3333ff"># This is a non standard Oracle</font>
116 <font color="#3333ff">#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";</font>
119 <p>As the full code listing that follows shows I then use my current development libraries and attempt to log into the development version of GPDB, and Oracle database, and retrieve and display some rudimentary data:</p>
121 <p><font color="#999999">Entire code listing of ~x0062320/testgpdb.pl</font></p>
123 <div class=code><pre>
124 <font color="#3333ff">#!/apps/perl/5.8.3/bin/perl</font>
125 <font color="#ff0000">use strict</font>;
126 <font color="#ff0000">use warnings</font>;
128 <font color="#3333ff"># Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm</font>
129 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/perl/modules-0412/lib</font>";
131 <font color="#3333ff"># Perl/Oracle libraries</font>
132 <font color="#ff0000">use lib</font> "<font color="#009900">/apps/oracle/perl/10.2.0.1.0/lib</font>";
134 <font color="#3333ff"># Linux API is in Ramey's home dir...</font>
135 <font color="#ff0000">use lib</font> "<font color="#009900">/home/ramey/oracle/perl/10.2.0.1.0/lib</font>";
137 <font color="#3333ff"># This is a non standard Oracle</font>
138 <font color="#3333ff">#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
140 # Current development GPDB modules</font>
141 <font color="#ff0000">use lib</font> "<font color="#009900">/web/gpdb/cgi-bin</font>";
143 <font color="#ff0000">use GPDB::gpdb</font>;
145 <font color="#3333ff"># Log into GPDB - read only</font>
146 gpdb_login (<font color="#33cc00">undef</font>, <font color="#33cc00">undef</font>, "<font color="#009900">OracleDevelopment</font>");
148 <font color="#3333ff"># Get a project</font>
149 <font color="#ff0000">my</font> <font color="#3333ff"><b>@projects</b></font> = gpdb_getProject ("<font color="#009900">uma</font>");
151 <font color="#3333ff"># Play with it a little bit...</font>
152 <font color="#ff0000">my</font> <i><font color="#ff0000">%project</font></i> = %{<font color="#3333ff"><b>$projects</b></font> [0][0]};
154 <font color="#33cc00">print</font> "<font color="#009900">Project: ${project {PROJECT}{NAME}}\n</font>";
155 <font color="#33cc00">print</font> "<font color="#009900">Site: ${project {PROJECT}{SITE_NAME}}\n</font>";
157 <font color="#33cc00">print</font> "<font color="#009900">Vobs:\n</font>";
159 <font color="#ff0000">foreach</font> (@{<font color="#ff0000">$project</font> {CLEARCASE}}) {
160 <font color="#ff0000">my</font> <i><font color="#ff0000">%cc</font></i> = %{$_};
162 <font color="#33cc00">print</font> "<font color="#009900">\t${cc {VOB_TAG}}\n</font>";
163 } <font color="#3333ff"># foreach</font>
165 <font color="#33cc00">print</font> "<font color="#009900">done\n</font>";
170 <p>Here's the results running on the target architectures:</p>
172 <div class=code><pre>
173 $ ssh stashu testgpdb.pl
174 DBI connect('host=dflorad01.itg.ti.com;sid=flddsync;port=1521','cm_gpdb_readonly',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at /web/gpdb/cgi-bin/GPDB/DBHelper.pm line 136
175 Could not connect to database. Check $ORACLE_HOME. at /web/gpdb/cgi-bin/GPDB/primitive.pm line 81
176 $ ssh fad01 testgpdb.pl
185 $ ssh drteeth testgpdb.pl
194 $ ssh fad11 testgpdb.pl
196 '/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so' for module DBI: /lib/libc.so.6: version `GLIBC_2.3' not found (required by
197 /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so) at /apps/perl/5.8.3/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229.
198 at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259
199 BEGIN failed--compilation aborted at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259.
200 Compilation failed in require at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
201 BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
202 Compilation failed in require at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
203 BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
204 Compilation failed in require at ./testgpdb.pl line 20.
205 BEGIN failed--compilation aborted at ./testgpdb.pl line 20.
208 <p>So as we can see this is <b>not</b> working for Solaris or Linux 64-bit Intel.</p>
211 <p class="entry-footer">
212 <span class="post-footers">Posted by on March 21, 2007 4:52 PM</span> <span class="separator">|</span> <a class="permalink" href="http://defaria.com/blogs/Status/archives/000625.html">Permalink</a>
217 <div class="trackbacks">
218 <h3 id="trackback" class="trackbacks-header">TrackBack</h3>
219 <div id="trackbacks-info">
220 <p>TrackBack URL for this entry:<br />http://defaria.com/mt/mt-tb.cgi/14</p>
222 <div class="trackbacks-content">