apache - DBD::Oracle, Cursors and Environment under mod_perl -

need help, because can't find solution problems dbd::oracle.

so @ first, current situation:

• we running apache2 mod_perl 2.0.4 @ our company
  • apache web server set startup script setting environment variables (ld_library_path, oracle_home, nls_lang)
  • in httpd.conf there environment variables ld_library_path , oracle_home (via setenv)
• we using perl module dbi driver dbd::oracle connect our main database
  • before create new instance of dbi setting perl env variables, (%env). setting oracle_home , nls_lang.

so far, works fine. extending our system , need connect remote database. again, using dbi , dbd::oracle. there new conditions:

• new connection must run in parallel existing one
• tnsnames.ora new connection placed @ different location (not @ $oracle_home.'/network/admin')
• new database contents provided stored procedures, fetching dbd::oracle , cursors (like explained here: https://metacpan.org/pod/dbd::oracle#binding-cursors)
• the stored procedures returning object types , collection types, containing attributes of oracle type date
  • to these dates in readable format, set new env variable $env{nls_date_format}
  • to ensure date format additionally alter session alter session set nls_date_format ...

okay, works fine, too. if make new connection on console. new tns location found script, connection established , fetching data procedures cursor working. alle date types formatted specified.

now, if try make connection @ apache environment, fails. @ first datasource name not resolved dbi/dbd::oracle. think because of our new tnsnames.ora file or rather location not found dbi/dbd::oracle in apache context (published $env{tns_admin}). don't know why???

the second problem (if create dirty workaround our first one) date format, published $env{nls_date_format} working on first level of our cursor select.

begin open :cursor select * table(stored_procedure) end; 

the example above returns collection types of object containing date attributes. in apache context format published nls_date_format not recognized. if use simple form of example this

begin open :cursor select sysdate table(stored_procedure) end; 

the result (a single date field) formatted well. think subordinated structures not formatted because $env{nls_date_format} works in console context , not in apache context, too.

so there must problem perl environment variables (%env) running under apache , mod_perl. maybe problem of mod_perl?

i @ wit's end. maybe in whole wide world has solution ... , excuse english :-) if need further explanations, try define more precisely.

if problem changes %env made while processing request don't seem honoured, because mod_perl assumes might running multiple threads , doesn't change process environment when change %env, external libraries (like oracle client) or child processes don't see change.

you can work around first using prefork mpm, there aren't threading issues, , making changes environment using env::c instead of %env hash.