Jaki1980 has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks , I am new to perl , I was trying to invoke sql prompt using my perl as follow : 
my $rp = system('sqlplus user/pass@instance @myScript.sql');
print $rp;

[download]
Now , I am able to to get to the sql prompt however the myScript.sql is not being excuted. If I try it from the Unix command line it works fine. Can someone please advice on why the script is not being run? I am not allowed to use DBI otherwise, it would be much easy. thanks much

20040115 Edit by BazB: Changed title from 'SQL issue'

Replies are listed 'Best First'.
Re: Running SQL*Plus from Perl
by Plankton (Vicar) on Jan 14, 2004 at 21:40 UTC
    You should use a here doc. like so ... 
    ...
open( SQLPLUS, "|sqlplus /nolog" ) or die "Can't run sqlplus\n" );
print SQLPLUS <<SQL;
connect user/pass@instance
@myScript
SQL
....

    [download]

    Plankton: 1% Evil, 99% Hot Gas.
[reply]
[d/l]
      thanks Plankton. It worked!!!!
[reply]
Re: Running SQL*Plus from Perl
by pg (Canon) on Jan 15, 2004 at 04:05 UTC

    There must be some other problems, as that's the right syntax to use sqlplus. I tested your stuff, and it worked.

    In the same directory, I had those two testing files:

    a.pl:

    my $rp = system('sqlplus dcs3000/dcua1@AAUA1.WORLD @a.sql');
print $rp;

    [download]

    a.sql: (Do you have that quit at the end?)

    VARIABLE a NUMBER
begin
:a := 0;
end;
/
print a
quit

    [download]

    From unix shell, I typed:

     
perl -w a.pl

    [download]

    Everything appears to be fine, and it did run to the end. Quit sqlplus and quit perl.

[reply]
[d/l]
[select]
      I've just started using Perl about 4 hours ago, but I think I might see a problem with your test: Try setting your variable 'a' to a value other than 0 and see what happens ;) You just happened to set it to the same value that is returned to the process: 0, which means no errors.
[reply]
Re: Running SQL*Plus from Perl
by Abigail-II (Bishop) on Jan 14, 2004 at 21:25 UTC
    If your program is run, can it find the myScript.sql file? Are you sure the current working directory is the one the file is in?

    Abigail

[reply]
      it is in the same directory . thats why when I run it from the command line it work fine , however from the script , it get me as far as the sql prompt .
[reply]

Back to Seekers of Perl Wisdom