Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • 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 GruwezOracle 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
Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

 
Geert GruwezOracle 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 GruwezOracle 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 GruwezOracle 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 GruwezOracle dbaCommented:
how to call the sample for example descr of "GLASS":

sample.cmd GLASS user/pass@db_alias
0
 
QlemoC++ DeveloperCommented:
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 GruwezOracle dbaCommented:
>QLemo
set "desc=%%A" ?

not
set desc="%%A" ?
0
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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