Solved

SQLPLUS from command line

Posted on 2013-01-21
21
531 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 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
Are You Ransomware's Next Victim?

Worried about ransomware attacks hitting your organization?  The good news is that these attacks are predicable and therefore preventable. Learn more about how you can  stop a ransomware attacks before encryption takes place with WatchGuard Total Security!

 
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 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 37

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

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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

739 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