Re: OT: environment variable
On Fri, 19 Jan 2007 13:52:40 -0600, David Dooling wrote: Hi David > If you want the Perl program to alter the environment of the > process that executed it (a shell, cron, another script, etc.), > that is not possible. Errr, actually it is possible, at least under Windows. And that's another good reason to discuss this in another forum, where you'll get expert advice. -- Cheers Ron Savage, [EMAIL PROTECTED] on 20/01/2007 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: environment variable - Mea Culpa
On 1/19/07, Jonathan Leffler <[EMAIL PROTECTED]> wrote: The solution I proposed works [...] I sent it on the 17th. Unfortunately, I only sent it to Oscar, not to dbi-users as well. Sorry - my mistake - both then and earlier today. This subject should now be closed. -- Jonathan Leffler <[EMAIL PROTECTED]> #include Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?
Thanks -- will give that a try.
Can I assume this means you believe that DBD::Oracle/DBI should
indeed be pre-caching rows in the reading from the cursor that was
received from the procedure? That is, it is reasonable to think that
it should be doing pre-caching and that it's a bug (configuration
problem, etc.) that it's not working? This is my assumption, but I'm
not sure -- perhaps I'm expecting something that doesn't really work that way.
Thanks!
Joel Noble
[EMAIL PROTECTED]
At 12:30 PM 1/19/2007, Reidy, Ron wrote:
Joel,
To really know understand the problem, you should gather an extended SQL
trace (event 10046) at the DB level at level 8 or higher -
http://orafaq.com/faqdbain.htm#EVENTS
After the program has completed, you will need to get the trace file
from the server's udump directory and format it with tkprof for easy
reading.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc
-Original Message-
From: Joel Noble [mailto:[EMAIL PROTECTED]
Sent: Friday, January 19, 2007 11:07 AM
To: dbi-users@perl.org
Subject: DBD::Oracle: Row cache fetch ahead on cursor returned from
PL/SQL stored proc?
Hello, all!
I'm having slow performance reading from a cursor that is returned from
a stored procedure. Network tracing and strace confirms that a
round-trip is being done to the Oracle DB to fetch each row, with no
pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5
and the ora10202_32 client.
If I'm reading the DBD::Oracle docs correctly, a cursor returned from a
PL/SQL stored proc will be considered a "nested cursor" (even though
there are no cursors nested inside the rows returned). So, I've set the
ora_max_nested_cursors and the RowCacheSize database handle attributes
right after connecting. (I've also tried them in the connect.) This
does not seem to change the behavior.
Here's some sample code I've culled out of my program. Any ideas
welcome!
[...]
my $dbh = DBI->connect( "dbi:Oracle:$config{'OracleDB'}",
$config{'OracleUser'},
$config{'OraclePassword'},
{ RaiseError=> 1,
PrintError => 0,
AutoCommit => 1 } ) ;
$dbh->{RowCacheSize} = 10;
$dbh->{ora_max_nested_cursors} = 16;
##
## Pull cursor, dump to file
##
my $cursor;
my @errors;
open (DUMPFILE, '>', "/tmp/dumpfile.txt");
eval {
my $sth = $dbh->prepare( q{
BEGIN
SOME_PKG.GET_CURSOR(:someid,:cursor );
END;
} );
$sth->bind_param(":someid", 42);
$sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type =>
ORA_RSET});
$sth->execute;
};
if ($@) {
print STDERR "$DBI::err -- $DBI::errstr\n";
} else {
while (my @row = $cursor->fetchrow_array) {
print DUMPFILE join ("\t",@row) . "\n";
}
}
[...]
Thank you!
Joel Noble
[EMAIL PROTECTED]
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information
is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
patches available for DBD::Multi, and benchmarking results
Some other folks may be interested in the patches I've published for DBD::Multi recently: http://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Multi Perhaps most interesting might be a benchmarking script I made, and related optimization patches: http://rt.cpan.org/Ticket/Display.html?id=24460 Unfortunately, my benchmarks found the raw overhead of DBD::Multi be significant. My patches reduced the overhead from 212% to 187%... I suspect the overhead is much, much lower in a real-world scenario, but I still get the sense there is further room for improvement. Do people have other solutions for load-balancing and failover that they recommend, at least for read-only access? Today I ran across ResourcePool http://search.cpan.org/dist/ResourcePool/ and SQL-Relay http://sqlrelay.sourceforge.net/ But I find few references to people actually using these tools. I'm also trying to weigh if my efforts to boost performance might be better spent on a caching solutions, including memcached, or some form of HTTP-level caching... Thanks for your feedback! Mark
Re: environment variable
Once more, there is more than one way to do it ...
Modifying %ENV, perhaps with local, seems to be the cleanest and fastest
way. do { local $ENV{'FOO'}='bar'; local $ENV{'GOD'}='Larry';
system('/usr/local/bin/shellscript.sh','--do-something'); };
Fiddling with the shell in system() looks AT LEAST dangerous to me
(assuming some of the environment values may come from the network).
system "FOO=$bar /usr/local/bin/shellscript.sh --do-something" may look
harmless, but what happens if $bar is "x rm -rf $HOME;" ?
On most Unix-like systems, there is /usr/bin/env, which does not only
dump the environment, but may also modify or reset it before passing
control to another program. Type man env to learn what env can to for
you. Something like
system('/usr/bin/env','-','FOO=bar','GOD=Larry','/usr/local/bin/shellscript.sh','--do-something')
should work.
On Windows, think about porting the "shell" script to Perl.
Oh, by the way: What is the relation of this question to Perl's Database
Interface?
Alexander
Oscar Gomez wrote:
how can i export a variable from program perl to shell script through environment
variable.
Thank you
--
Open WebMail Project (http://openwebmail.org)
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
Re: OT: environment variable
On Wed, Jan 17, 2007 at 8:59AM, Oscar Gomez wrote:
> how can i export a variable from program perl to shell script through
> environment variable.
This is not really a DBI question, so you will have better luck
posting this type of question to perl-monks
(http://www.perlmonks.org/) or similar forum.
If you want to spawn a process from your Perl script and have that
have an altered environment, you can change the environment variable
in your Perl program and then call the program (using system, exec, or
backticks):
$ENV{XYZ} = 1;
system(...) == 0 or die "system call failed";
If you don't want the environment of the Perl script altered, you can
set the environment variable in the command:
system("XYZ=1 && export XYZ && ...") == 0 or die "system call failed";
If you want the Perl program to alter the environment of the process
that executed it (a shell, cron, another script, etc.), that is not
possible.
--
David Dooling
RE: environment variable
The standard Unix semantics is this: a child process inherits the
environment from its parent. Therefore if you (export and) set an
environment variable in a process and then run a script, it will see
that environment variable.
Furthermore, _Programming Perl, 3rd ed_ by Wall et all says this about
%ENV on p. 661:
Setting a value in %ENV changes the environment for both your process
and child processes launched after the assignment.
I tried this test program and it worked:
$ENV{MCGILLICUDY} = 'corncob pipe';
system( qq{echo \$MCGILLICUDY} );
-Will
> -Original Message-
> From: Scott Smith [mailto:[EMAIL PROTECTED]
> Sent: Friday 19 January 2007 14:53
> To: Reidy, Ron
> Cc: Oscar Gomez; dbi-users@perl.org
> Subject: Re: environment variable
>
>
> Reidy, Ron wrote:
> > Oscar,
> >
> > Short answer - you cannot (sort of). This is because your
> shell script
> > will execute in a sub shell of your perl program.
> >
> > However, you can do something like this:
> >
> > # untested
> > system("export VAR=val; /path/to/your/shell/script.sh");
>
> The shell also takes a series of zero or more key-value pairs
> at the start of any command:
>
> system("VAR=val VAR2=anotherval /path/to/any/program");
>
> This has the same effect as exporting when you're doing it
> with Perl's system call, but it's good to
> keep in mind when you don't want to keep the variable setting
> or override in your general shell
> session. i.e.
>
> LD_PRELOAD=/usr/lib/special_old_library.so /usr/bin/oldprogram
>
> Scott Smith
> Genome Sequencing Center
>
- - - - - Appended by Scientific Atlanta, a Cisco company - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is solely
intended for the named addressee (or a person responsible for delivering it to
the addressee). If you are not the intended recipient of this message, you are
not authorized to read, print, retain, copy or disseminate this message or any
part of it. If you have received this e-mail in error, please notify the sender
immediately by return e-mail and delete it from your computer.
Re: environment variable
The solution I proposed works - and I tested it.
#!/bin/perl -w
$ENV{MY_ENVIRONMENT_VARIABLE} = "Quixotic Response";
system("env");
However, this is Perl - TMTOWTDI!
If you want to undo the setting after running the shell, either localize
%ENV or delete the new variable.
{
local(%ENV) = %ENV;
$ENV{MY_ENVIRONMENT_VARIABLE} = "Quixotic Response";
system("env");
}
or
#!/bin/perl -w
$ENV{MY_ENVIRONMENT_VARIABLE} = "Quixotic Response";
system("env");
delete $ENV{MY_ENVIRONMENT_VARIABLE};
Ron's solution won't work unless you are running a sane shell (C shell
doesn't like export, a true Bourne shell doesn't like export with the
assignment!).
And, if you're using a sane shell, you don't need the export, you can simply
write:
system("VAR=val /path/to/your/shell/script");
Well, that worked nicely for me with Korn Shell (and would work with Bourne
Shell), but won't work with C shell (again). You can have multiple
environment variables set if you need to:
system("VAR1=val1 VAR2=val2 /usr/bin/env");
And Scott's response arrived before I sent this but after I'd typed it.
Find "Csh Programming Considered Harmful" via Google if you don't understand
why C shell is not a good idea.
On 1/19/07, Reidy, Ron <[EMAIL PROTECTED]> wrote:
Short answer - you cannot (sort of). This is because your shell script
will execute in a sub shell of your perl program.
However, you can do something like this:
# untested
system("export VAR=val; /path/to/your/shell/script.sh");
I think that might work for you.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Oscar Gomez [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 8:59 AM
To: dbi-users@perl.org
Subject: environment variable
how can i export a variable from program perl to shell script through
environment
variable.
Thank you
--
Open WebMail Project (http://openwebmail.org)
This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not
the
intended recipient, please be aware that any disclosure, copying,
distribution
or use of the contents of this information is prohibited. Please notify
the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
--
Jonathan Leffler <[EMAIL PROTECTED]> #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Re: environment variable
Reidy, Ron wrote:
Oscar,
Short answer - you cannot (sort of). This is because your shell script
will execute in a sub shell of your perl program.
However, you can do something like this:
# untested
system("export VAR=val; /path/to/your/shell/script.sh");
The shell also takes a series of zero or more key-value pairs at the start of
any command:
system("VAR=val VAR2=anotherval /path/to/any/program");
This has the same effect as exporting when you're doing it with Perl's system call, but it's good to
keep in mind when you don't want to keep the variable setting or override in your general shell
session. i.e.
LD_PRELOAD=/usr/lib/special_old_library.so /usr/bin/oldprogram
Scott Smith
Genome Sequencing Center
RE: environment variable
Oscar,
Short answer - you cannot (sort of). This is because your shell script
will execute in a sub shell of your perl program.
However, you can do something like this:
# untested
system("export VAR=val; /path/to/your/shell/script.sh");
I think that might work for you.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Oscar Gomez [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 8:59 AM
To: dbi-users@perl.org
Subject: environment variable
how can i export a variable from program perl to shell script through
environment
variable.
Thank you
--
Open WebMail Project (http://openwebmail.org)
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: :Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?
Joel,
To really know understand the problem, you should gather an extended SQL
trace (event 10046) at the DB level at level 8 or higher -
http://orafaq.com/faqdbain.htm#EVENTS
After the program has completed, you will need to get the trace file
from the server's udump directory and format it with tkprof for easy
reading.
--
Ron Reidy
Lead DBA
Array BioPharma, Inc
-Original Message-
From: Joel Noble [mailto:[EMAIL PROTECTED]
Sent: Friday, January 19, 2007 11:07 AM
To: dbi-users@perl.org
Subject: DBD::Oracle: Row cache fetch ahead on cursor returned from
PL/SQL stored proc?
Hello, all!
I'm having slow performance reading from a cursor that is returned from
a stored procedure. Network tracing and strace confirms that a
round-trip is being done to the Oracle DB to fetch each row, with no
pre-caching. Using DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5
and the ora10202_32 client.
If I'm reading the DBD::Oracle docs correctly, a cursor returned from a
PL/SQL stored proc will be considered a "nested cursor" (even though
there are no cursors nested inside the rows returned). So, I've set the
ora_max_nested_cursors and the RowCacheSize database handle attributes
right after connecting. (I've also tried them in the connect.) This
does not seem to change the behavior.
Here's some sample code I've culled out of my program. Any ideas
welcome!
[...]
my $dbh = DBI->connect( "dbi:Oracle:$config{'OracleDB'}",
$config{'OracleUser'},
$config{'OraclePassword'},
{ RaiseError=> 1,
PrintError => 0,
AutoCommit => 1 } ) ;
$dbh->{RowCacheSize} = 10;
$dbh->{ora_max_nested_cursors} = 16;
##
## Pull cursor, dump to file
##
my $cursor;
my @errors;
open (DUMPFILE, '>', "/tmp/dumpfile.txt");
eval {
my $sth = $dbh->prepare( q{
BEGIN
SOME_PKG.GET_CURSOR(:someid,:cursor );
END;
} );
$sth->bind_param(":someid", 42);
$sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type =>
ORA_RSET});
$sth->execute;
};
if ($@) {
print STDERR "$DBI::err -- $DBI::errstr\n";
} else {
while (my @row = $cursor->fetchrow_array) {
print DUMPFILE join ("\t",@row) . "\n";
}
}
[...]
Thank you!
Joel Noble
[EMAIL PROTECTED]
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
DBD::Oracle: Row cache fetch ahead on cursor returned from PL/SQL stored proc?
Hello, all!
I'm having slow performance reading from a cursor that is returned from a
stored procedure. Network tracing and strace confirms that a round-trip is
being done to the Oracle DB to fetch each row, with no pre-caching. Using
DBI-1.52, DBD-Oracle-1.18 on Linux with Perl 5.8.5 and the ora10202_32 client.
If I'm reading the DBD::Oracle docs correctly, a cursor returned from a PL/SQL
stored proc will be considered a "nested cursor" (even though there are no
cursors nested inside the rows returned). So, I've set the
ora_max_nested_cursors and the RowCacheSize database handle attributes right
after connecting. (I've also tried them in the connect.) This does not seem
to change the behavior.
Here's some sample code I've culled out of my program. Any ideas welcome!
[...]
my $dbh = DBI->connect( "dbi:Oracle:$config{'OracleDB'}",
$config{'OracleUser'},
$config{'OraclePassword'},
{ RaiseError=> 1,
PrintError => 0,
AutoCommit => 1 } ) ;
$dbh->{RowCacheSize} = 10;
$dbh->{ora_max_nested_cursors} = 16;
##
## Pull cursor, dump to file
##
my $cursor;
my @errors;
open (DUMPFILE, '>', "/tmp/dumpfile.txt");
eval {
my $sth = $dbh->prepare( q{
BEGIN
SOME_PKG.GET_CURSOR(:someid,:cursor );
END;
} );
$sth->bind_param(":someid", 42);
$sth->bind_param_inout(":cursor", \$cursor, 0, {ora_type => ORA_RSET});
$sth->execute;
};
if ($@) {
print STDERR "$DBI::err -- $DBI::errstr\n";
} else {
while (my @row = $cursor->fetchrow_array) {
print DUMPFILE join ("\t",@row) . "\n";
}
}
[...]
Thank you!
Joel Noble
[EMAIL PROTECTED]
Re: Positively Archaic Versions of DBI and DBD::Oracle
You can send me the code for DBD::Oracle if you want. I can load into to subversion http://svn.perl.org/modules/dbd-oracle and have people can have access online from there. They would be under the tags dir. cheers - Original Message - From: "Jonathan Leffler" <[EMAIL PROTECTED]> To: "Peter J. Holzer" <[EMAIL PROTECTED]>; Sent: Thursday, January 18, 2007 5:52 PM Subject: Positively Archaic Versions of DBI and DBD::Oracle On 1/18/07, Peter J. Holzer <[EMAIL PROTECTED]> wrote: [...interesting stuff deleted...] > You can find DBI modules back to 1.13 at > > http://www.cpan.org/modules/by-authors/id/TIMB/ > > The Oracle versions only go back to 1.14 - and may not cover the latest ones > either; you can hunt those down yourself. You can find even older versions on BackPAN: http://backpan.cpan.org/authors/Tim_Bunce/ starts with DBI-0.89 and DBD-Oracle-0.47 from 1997. Only that far back? Would they be interested in DBI versions 0.69, 0.73, 0.74, 0.75, 0.77, 0.78, 0.79, and 0.81..0.88 too? I have them stashed away... I also have DBD::Oracle 0.43..0.46 too. I'd be happy to give them - ideas on who to contact? I don't see any information on who or how to submit such stuff at http://backpan.cpan.org/ - that just gives a directory listing. For DBD::Informix, I have all released (and probably some unreleased) versions back to 0.23, but they already seem to have those (or enough that the difference is unlikely to matter). The versions prior to 0.25 are under Alligator Descartes' directory, not mine. -- Jonathan Leffler <[EMAIL PROTECTED]> #include Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org "I don't suffer from insanity - I enjoy every minute of it."
