[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8719
  • Last Modified:

execute commands in sqlplus using perl

I would like to a write a simple perl script to login to Oracle sqlplus, execute certain commands and exit.
I am new to Perl ..
Can any one suggest easier way to do that..

$login = 'sqlplus user/password';
system($login);
logs me in to sqlplus
But i dont know how to execute commands in sqlplus from perl..

Thanks in advance

0
vijay_5455
Asked:
vijay_5455
  • 2
  • 2
  • 2
  • +1
1 Solution
 
holliCommented:
i dont know much about sql+ but i think you could try this:

$login = 'sqlcommands>sqlplus user/password';
system($login);

where "sqlcommands" is a textfile with your sql-statements in it.

just have try,

holli
0
 
blinkie23Commented:
i've not used oracle sqlplus before, but i've used many other SQL servers.  use should probably be using the standard perl DBI interface module.  you will need to make sure you have DBI installed (it's not always..), as well as the DBD driver for oracle.  below is some sample code.  in the connect, note that it explicitly states port and host and sid and lots of info.  you can exclude port and host, and it will assume localhost and the default known port for that db.  (in case this example doesn't work.)  i've also never used sid before, so this may or may not work.

reference source: http://thomas.eibner.dk/oracle/dbi/

#!/usr/bin/perl

use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=localhost;sid=ORCL;port=1521',
       'username', 'password', { RaiseError => 1, AutoCommit => 0 });

my $sth = $dbh->prepare("SELECT table_name FROM user_tables");
while (my ($table_name) = $sth->fetchrow_array()) {
    print $table_name, "\n";
}
$sth->finish();
$dbh->disconnect();

0
 
blinkie23Commented:
by the way, the example i included above is just a simple SELECT statement.  you can do pretty much any SQL command you'd like otherwise, and access the results in a variety of ways and data structures.  i'd advise reading the included link, or doing a Google search for "perl dbi oracle" or something to learn the possibilities.  good luck.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ItatsumakiCommented:
I totally agree with blinkie that you should be using the DBI interface (it's pretty intuitive) but if you're insisting on using SQL/Plus (i.e. for the non-SQL commands that aren't available via DBI like DESCRIBE) you can do this to get the command options:

####################################################
c:\> sqlplus -?
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Feb 24 13:31:04 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_string>] | /
Enter user-name:
C:\Documents and Settings\Administrator>sqlplus -?

SQL*Plus: Release 9.2.0.1.0 - Production

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode
####################################################
So, what you need is to add the parameters like this:
sqlplus -S SCOTT/tiger tester.sql

i.e.
### code start
$login = 'sqlplus -S user/password tester.sql';
system($login);
### code end

This works for me on SunOS, WinXP, Win9x.  Let me know if you have troubles with that.

Itats
0
 
jmcgOwnerCommented:
Nothing has happened on this question in over 8 months. It's time for cleanup!

My recommendation, which I will post in the Cleanup topic area, is to
accept answer by blinkie23.

Please post any comments here within the next seven days. Moderators check comments here before acting on the recommendation. Experts: silence will likely be taken as assent.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jmcg
EE Cleanup Volunteer
0
 
ItatsumakiCommented:
I guess i could be biased, but it seemed that I answered the question directly, so that the points should be split.
0
 
jmcgOwnerCommented:
Point splits cannot be finer than 20, so I had to choose just one answer. When deciding close calls like this one, I try to keep in mind who has lost out on other close calls, so I do not always choose the "first acceptable answer" if choosing another one would keep the overall point distribution more fair.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now