?
Solved

execute commands in sqlplus using perl

Posted on 2003-02-24
8
Medium Priority
?
8,609 Views
Last Modified: 2007-12-19
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
Comment
Question by:vijay_5455
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Expert Comment

by:holli
ID: 8008011
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
 
LVL 2

Accepted Solution

by:
blinkie23 earned 100 total points
ID: 8009238
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
 
LVL 2

Expert Comment

by:blinkie23
ID: 8009258
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8010356
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
 
LVL 20

Expert Comment

by:jmcg
ID: 9692037
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
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 9692344
I guess i could be biased, but it seemed that I answered the question directly, so that the points should be split.
0
 
LVL 20

Expert Comment

by:jmcg
ID: 9692620
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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question