• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

SQLPLUS from command line

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
Vinum
Asked:
Vinum
  • 7
  • 6
  • 3
  • +4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would put the sql into a file (say  my_sql.sql), and run it like this:

SQLPLUS login/password@database @my_sql
0
 
Geert GOracle dbaCommented:
>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
 
VinumAuthor Commented:
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
Worried about phishing attacks?

90% of attacks start with a phish. It’s critical that IT admins and MSSPs have the right security in place to protect their end users from these phishing attacks. Check out our latest feature brief for tips and tricks to keep your employees off a hackers line!

 
Geert GOracle dbaCommented:
we need way more information than what you are providing
what are you trying to accomplish ?
0
 
VinumAuthor Commented:
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
 
VinumAuthor Commented:
>>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
 
Geert GOracle dbaCommented:
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
 
VinumAuthor Commented:
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
 
Geert GOracle dbaCommented:
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
 
Geert GOracle dbaCommented:
how to call the sample for example descr of "GLASS":

sample.cmd GLASS user/pass@db_alias
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Geert GOracle dbaCommented:
>QLemo
set "desc=%%A" ?

not
set desc="%%A" ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
VinumAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
What is wrong about the batch code we provided?
0
 
VinumAuthor Commented:
I will get back to this later...
0
 
VinumAuthor Commented:
I will get back to this later...
0
 
DavidSenior Oracle Database AdministratorCommented:
Seems as through the author is shirking his/her responsibility to respond in a timely manner:  two extensions...

dvz
CV
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 7
  • 6
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now