PUBLIC:Re: Unwanted Output When Querying Via Perl

From: Eugen Nyffeler <eugen.nyffeler_at_ubs.com>
Date: Thu, 25 Jun 1998 10:12:59 +0200
Message-ID: <3592068B.5B87C74C@ubs.com>

Ryan Rucker wrote:
>
> Hello,
>
> I'm not quite sure if this is more of an SQL question or a Perl
> question, so I'm starting here first.
>
> I have this bit of Perl code (on a Unix box) that I use to connect to an
> Oracle database, run a query, and produce a text file of the results:
>
> open(SQLPLUS, "| sqlplus scott/tiger\@database")
> or die ("Couldn't run SQL*PLUS:$!");
>
> # grab rows from all_customers
>
> print SQLPLUS <<BYE;
> set heading off
> set termout off
> set echo off
> set feedback off
> spool /tmp/customers.txt
> select customer_id, customer_name from all_customers;
> spool off
> exit
> BYE
>
> close(SQLPLUS) or die ("Couldn't stop SQL*PLUS:$!\n");
>
> It runs all right and produces the file, but the output that I get
> (customers.txt) looks like this:
>
> SQL> select customer_id, customer_name from all_customers;
>
> 100000
> BIG JIM'S TIRE AND BATTERY
>
> 100001
> BUZZY BEE CAFE
>
> SQL> spool off
>
> I'm trying to find a way to suppress the two SQL> lines. In other
> words, I just want the data, not the SQL statements also.
>
> Any ideas on why this is happening and how to prevent it is appreciated.
>
> Ryan

Hi Ryan

You're using perl simple as a script to start sql*plus without any "real" perl
function. What you do is just the same as you would login into sql*plus from the
command line.
If you would like to use the perl together with oracle, have a look at oraperl.
your script would look soemthing like this (instade of printing to stdout
you can also print to a file ;-)

---

#!/usr/bin/oraperl # or where your oraperl is located

# connect to instance xyz as user abc with password ghj #
  unless ($lda = &ora_login("orcale_sid", "username", "password"))   {

        printf ("connect error\n");
  }

# create a cursor
#
  ($stmt) = ("select customer_id, customer_name from all_customers");   ($user_crs) = &ora_open ($lda, $stmt);   unless ($user_crs)
  {

        printf ("cursor open problem\n");
  }

# retrieve the data and print it
#

  printf("customer_id  customer_name\n");
  printf("----------  ----------  ----------  -----------  -------\n");
  while (local ($uid, $nam) = &ora_fetch($user_crs))

  {
    $outp = sprintf ("%-10s %-30s\n", $uid, $nam);     printf("$outp");
  }
  &ora_close($user_crs);

  &ora_logoff($lda);

---

HtH
eugen Received on Thu Jun 25 1998 - 03:12:59 CDT