Calling a sql script from perl

Discussion in 'Perl Misc' started by dn_perl, Nov 17, 2003.

  1. dn_perl

    dn_perl Guest

    I am calling a shell script from a perl script and the shell script
    invokes an sql script. I would like to invoke the sql script direct
    from perl.
    How can I do it?

    Perl script :
    system("call_shell_script 'input_file' ");

    Contents of shell script 'call_shell_script' :

    sqlplus -s << EOH 2>gen_rep.errlog
    spool ${LOGSQL}
    spool off

    Can I simply combine the two steps above with these lines in perl
    script :
    (I don't have access to perl environment today, so can't try this

    system("sqlplus -s << EOH 2>gen_rep.errlog");
    spool ${LOGSQL}
    spool off
    dn_perl, Nov 17, 2003
    1. Advertisements

  2. dn_perl

    Mina Naguib Guest

    Hash: SHA1

    use DBI;

    use DBD::Oracle;

    See and

    Version: GnuPG v1.2.3 (GNU/Linux)
    Comment: Using GnuPG with Mozilla -

    -----END PGP SIGNATURE-----
    Mina Naguib, Nov 17, 2003
    1. Advertisements

  3. It was a dark and stormy night, and managed to scribble:
    By using sqlplus I take it you are using Oracle.

    While attempting to read your mind I think the following untested code is what you want to do:

    #set up load file
    my $logsql='rep.log';

    #do a pipe open, send stderr to a file
    open(FILEHANDLE,'|sqlplus -s 2>gen_rep.errlog');
    print FILE "userid/passwd\n";

    # write the result of this sqlplus session to $logsql
    print FILE, "spool $logsql\n";

    # run an Oracle script
    print FILE, "@call_sql_script.sql\n";

    # can even do some inline sql
    print FILE, "select * from tab;\n";
    close (FILE);

    For more on pipe opens see

    Gregory Toomey, Nov 17, 2003
  4. On 16 Nov 2003 18:09:04 -0800
    my $sql_cmd = <<EOH;
    spool ${LOGSQL}
    spool off

    system("sqlplus -s $sql_cmd 2>gen_rep.errlog");

    Read over the following by typeing (at the command line):
    perldoc -q "Why don't my <<HERE documents work"

    And might I suggest you check out the DBI module :)



    Copyright notice: all code written by the author in this post is
    released under the GPL.
    for more information.

    a fortune quote ...
    Facts are stubborn, but statistics are more pliable.
    James Willmore, Nov 17, 2003
  5. dn_perl

    dn_perl Guest

    That '@' character is the stumbling block. I tried escaping it
    within double quotes "\@call_sql_script.sql" and I also tried
    using single quotes to treat the string as a literal
    as in '@call_sql_script.sql'. But neither approach worked.

    The actual statement which I call from the shell script is :
    @call_sql_script.sql $read_input_from_this_file
    So single quoting the sqlplus command is not going to work
    ultimately anyway.

    dn_perl, Nov 18, 2003
  6. On 17 Nov 2003 19:49:17 -0800
    A workaround might be to read the 'call_sql_script.sql' into a
    variable and the include the variable in the here doc. However, if it
    were me, I'd use the DBI module :)


    Copyright notice: all code written by the author in this post is
    released under the GPL.
    for more information.

    a fortune quote ...
    Vote anarchist
    James Willmore, Nov 18, 2003
  7. dn_perl

    JR Guest

    I have run into the problem you describe several times, and think I
    may have a solution for you (at least within the UNIX environment, and
    connecting to the an Oracle database).

    The problem is that the SQL*PLUS environment needs to be set before
    you can call SQL*PLUS from within a perl program (doing a system
    callout doesn't work alone, because SQL*PLUS doesn't inherently know
    it's own environment, or at least that's what my DBA tells me).

    As an aside, I use a class that stores this info. in one of my
    directories, and by using that class, I only need one line to actually
    call SQL*PLUS within my perl scripts (my actual SQL*PLUS statement),
    using OO notation (if you want a copy of the class, I'll mail it to
    you; it's very simple and also something you could easily write on
    your own, with just a basic understanding of OO programming in
    Perl-I'm certainly no expert). Errors and valid data sets are
    returned in an array as they would otherwise appear within SQL*PLUS.
    If you can't install DBI (where I work, DBI is installed on some of
    our UNIX machines, but not others, and I'm not allowed to install it
    where it doesn't exist. What a pain), I suggest doing something like
    rolling your own class and using the internals of the class to handle
    your connections. This will make easier the process of handling
    Oracle version changes (you'll only have to change one line in one
    class, as opposed to changing myriad lines in many scripts).

    Anyway, the below code should get you started (all you need to do is
    just change the connect, settings, query and maybe the environment).
    Good luck.

    #!/perl -w
    use strict;
    use diagnostics;

    my $rec;
    $rec->{ENVIRONMENT} = '9.2.0';
    @{$rec->{PATH}} = `set path=\$PATH`;

    $rec->{CONNECT} = 'jroland/[email protected]';
    @{$rec->{SETTINGS}} = 'set wrap off pagesize 0';
    @{$rec->{QUERY}} = 'select table_name from user_tables;';

    my $script = "SQL_OUTFILE" . time . ".SQL";
    my $sfile = "$script" . "_OUTFILE";

    open OUTFILE, ">$script" or die "Cannot open $script: $!";
    print OUTFILE join "\n", @{$rec->{SETTINGS}}, "spool $sfile",
    @{$rec->{QUERY}}, "spool off", "exit;";
    close OUTFILE or die "Cannot close $script: $!";

    `@{$rec->{PATH}} sqlplus $rec->{CONNECT} \@$script`;

    open CLASS_SPOOLFILE, "$sfile" or die "Cannot open $sfile: $!";
    while(<CLASS_SPOOLFILE>) {
    push @{$rec->{QUERY_RETURNS}}, $_;
    close CLASS_SPOOLFILE or die "Cannot close $sfile: $!";

    `rm $script $sfile`;

    print "$_\n" for (@{$rec->{QUERY_RETURNS}});
    JR, Nov 19, 2003
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.