Solved

SQLPLUS from command line

Posted on 2013-01-21
21
532 Views
Last Modified: 2013-11-25
I would like to get data from my Oracle in a bat-file and I need the format of getting this with SQLPLUS. Like this:

SQLPLUS login/password@database "select part_no, description from Inventory where part_no = %1"

Echo Part_No
Echo Desciption

I don't know the format....
0
Comment
Question by:Vinum
[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
  • 7
  • 6
  • 3
  • +4
21 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38801030
you would put the sql into a file (say  my_sql.sql), and run it like this:

SQLPLUS login/password@database @my_sql
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38801069
>I don't know the format....

presumably as a text file ?
and more specific as a CSV (comma separated values) file ?

you really need the format for very specific help
0
 

Author Comment

by:Vinum
ID: 38801098
when executing SQLPLUS login/password@database @my_sql it stops with "2". When I press enter, it is in SQLPLUS ( SQL> )

How can I use the result which is returned by my Select ?
0
Technology Partners: 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 38

Expert Comment

by:Geert Gruwez
ID: 38801101
we need way more information than what you are providing
what are you trying to accomplish ?
0
 

Author Comment

by:Vinum
ID: 38801105
From a bat-file, I need to call into my Oracle. In the above example, I would call in with an Item Number and from Oracle, it would return the Description field.

I need to use this Description field in my bat-file
0
 

Author Comment

by:Vinum
ID: 38801124
>>when executing SQLPLUS login/password@database @my_sql it stops with "2". When I press enter, it is in SQLPLUS ( SQL> )<<

Sorry - I needed ";" after SQL-statemend and an Exit;

Now it display the value from my SQL. How can I use this as a variable in my BAT-file?
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38801189
by generating an intermediate file with sqlplus
and then again executing the intermediate file with sqlplus

sample cmd file for starting a backup
call as
take_backup.cmd c:\oracle\ora1120\bin ORCL sys/syspwd

Open in new window


batch file (all in one):
set orabin=%1
set oracle_sid=%2
set conn=%3

echo start backup mode on source 
set sqlfile=startbackup_%ORACLE_SID%.sql
set outfile=begin_backup_%ORACLE_SID%.sql
call:backup_mode begin 

%orabin%\sqlplus -L -S "%conn% as sysdba" @%sqlfile%
%orabin%\sqlplus -L -S "%conn% as sysdba" @%outfile%

exit

:backup_mode
echo.--%1 backup mode on %ORACLE_SID%>%sqlfile%
echo.set linesize 9999>>%sqlfile%
echo.set pagesize ^0>>%sqlfile%
echo.set feedback off>>%sqlfile%
echo.set echo off>>%sqlfile%
echo.set wrap off>>%sqlfile%
echo.set trimspool on>>%sqlfile%
echo.set verify off>>%sqlfile%
echo.spool %outfile%>>%sqlfile%
echo.select 'alter tablespace '^|^|tablespace_name^|^|' %1 backup;' >>%sqlfile%
echo.from dba_tablespaces where contents not in ('TEMPORARY');>>%sqlfile%
echo.select 'exit' from dual;>>%sqlfile%
echo.spool off>>%sqlfile%
echo.exit>>%sqlfile%
goto :eof

Open in new window

0
 

Author Comment

by:Vinum
ID: 38801525
2 things:

Can't I avoid using the inputfile and setup the SQL select statement directly?
I still need to use the returned variable in my BAT-file. In the example from Geert_Gruwez he uses parameters on input - that is also okay, but I need the output!
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38801583
what do you want to do with the descr in the bat file ?
you can only select it using sqlplus into an interim file
and then you'll have to run the interim file

you can also generate a cmd file with sqlplus

sample:
set param=%1
set conn=%2
if not defined conn set conn=user/pass@db_alias

echo extract descr to desc variable
set sqlfile=extract_descr_%ORACLE_SID%.sql
set cmdfile=show_descr_%ORACLE_SID%.cmd
call:extract %param% 

%orabin%\sqlplus -L -S "%conn%" @%sqlfile%
%cmdfile%

exit

:extract
echo.--extract descr for %1 from db %conn%>%sqlfile%
echo.set linesize 9999>>%sqlfile%
echo.set pagesize ^0>>%sqlfile%
echo.set feedback off>>%sqlfile%
echo.set echo off>>%sqlfile%
echo.set wrap off>>%sqlfile%
echo.set trimspool on>>%sqlfile%
echo.set verify off>>%sqlfile%
echo.spool %cmdfile%>>%sqlfile%
echo.select 'set descr='||descr from table where param='%1'; >>%sqlfile%
echo.select 'echo For "%1" this is the description: "^%descr^%"' from dual;>>%sqlfile%
echo.select 'pause' from dual;>>%sqlfile%
echo.spool off>>%sqlfile%
echo.exit>>%sqlfile%
goto :eof

Open in new window

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38801594
how to call the sample for example descr of "GLASS":

sample.cmd GLASS user/pass@db_alias
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 38802080
This is somewhat "leaner":
@echo off
set part=%1
set db=%2
(
 echo set linesize 9999 pagesize 0 wrap off;
 echo select part_no, description from Inventory where part_no = '%part%';
) | sqlplus -s %db% > %temp%\sqlplus.out

for /F "tokens=*" %%A in ('find /v /i "rows" %temp%\sqlplus.out') do set "desc=%%A"
del %temp%\sqlplus.out

echo %part% is %desc%

Open in new window

The usage is the same as with Geert_Gruwez's code.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38805152
>QLemo
set "desc=%%A" ?

not
set desc="%%A" ?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38805260
The former is "special syntax", and should not work if we read the syntax description. However, it does. And it allows for making sure there are no trailing blanks in the value.

In     set desc="%%A"   the quotes are part of the value, that is %desc% will always show double quotes. This is usually not intended.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38805404
As you can see, BAT isn't great when it comes to this sort of thing.

Can you possibly use Powershell?  If so, you don't even need sqlplus.  You can connect directly to the database.

There are many links out there with examples of this.

This one seems pretty straight forward:
http://letitknow.wordpress.com/2012/08/01/retrieve-data-from-oracle-database-using-powershell/
0
 

Author Comment

by:Vinum
ID: 38954571
I have not got an answer yet that I could use. As Slightwv also says, BAT file is not so good for Oracle calls. I cannot use Powershell, at the commands are called from my users...
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38954773
What is wrong about the batch code we provided?
0
 

Author Comment

by:Vinum
ID: 39010742
I will get back to this later...
0
 

Author Comment

by:Vinum
ID: 39303473
I will get back to this later...
0
 
LVL 23

Expert Comment

by:David
ID: 39328071
Seems as through the author is shirking his/her responsibility to respond in a timely manner:  two extensions...

dvz
CV
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39674167
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platformsā€¦
I recently purchased a Bluetooth headset called the Music Jogger (model BSH10). The control buttons on it look like this: One of my goals is to use it as the microphone and speakers for Skype calls. In that respect, it works well. However, I ā€¦
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

630 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