Solved

SQLPLUS from command line

Posted on 2013-01-21
21
527 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
  • 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 37

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
Industry Leaders: 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 37

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 37

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 37

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 37

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 69

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 37

Expert Comment

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

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

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 69

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
DECT technology has become a popular standard for wireless voice communication. DECT devices are not likely to be affected by other electronic devices and signals because they operate in a separate frequency-band.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

679 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