Home » Infrastructure » Unix » can I execute SQL commands in the unix command line
icon5.gif  can I execute SQL commands in the unix command line [message #258520] Sun, 12 August 2007 23:31 Go to next message
kveaswaran
Messages: 4
Registered: August 2007
Junior Member
Hi,

Is there a way I can execute SQL commands in the unix command line using some kind of prefix.

For example, the following works in DB2.

$ db2 select * from test_table;

In oracle I have to login to get the "SQL>" prompt and then execute my statements. This is fine, but I am not able to use any of unix filters like grep , wc etc. This is possible in DB2 as we can do statements in the unix cmd line itself..

Thanks,

Easwaran.
Re: can I execute SQL commands in the unix command line [message #258522 is a reply to message #258520] Sun, 12 August 2007 23:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

but I am not able to use any of unix filters like grep , wc etc


Use ,
!
/

host
command in SQL*plus to access unix commands unless you log on to sql*plus in restrict mode
(
sqlplus -R 3 
.)

[Updated on: Mon, 13 August 2007 00:03]

Report message to a moderator

Re: can I execute SQL commands in the unix command line [message #258523 is a reply to message #258520] Sun, 12 August 2007 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
>Use , !/host command in SQL*plus to access unix commands.
Please provide working example
Re: can I execute SQL commands in the unix command line [message #258524 is a reply to message #258520] Sun, 12 August 2007 23:54 Go to previous messageGo to next message
kveaswaran
Messages: 4
Registered: August 2007
Junior Member
I meant piping the output of the statements to filters.

For example

select TABLE_NAME from all_tables where TABLESPACE_NAME ='test_tbl'; | grep -v TABLE_NAME

so that I can trim off the heading. This is just an example, there are n number of filters which we can use constructively...
Re: can I execute SQL commands in the unix command line [message #258525 is a reply to message #258520] Sun, 12 August 2007 23:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

-bash-3.00$ sqlplus apps/apps

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 13 00:50:09 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> spool a.out
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$Nyy6Lbg0TpfgRAAROwpDsw==$0 TABLE
DEPT                           TABLE
EMP                            TABLE
C_LOB                          TABLE
BLOB_TEST                      TABLE

SQL> select * from c_lob;

TEXT
--------------------------------------------------------------------------------
I am ok

SQL> spool off;

SQL> !grep -c o a.out
4
Re: can I execute SQL commands in the unix command line [message #258526 is a reply to message #258520] Mon, 13 August 2007 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
Arju,
why did you completely REWRITE your erroneous response after being called to provide working example of your original/incorrect post?
Re: can I execute SQL commands in the unix command line [message #258529 is a reply to message #258520] Mon, 13 August 2007 00:09 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Anacedent, I have not told that you can use sql command unless you log on into it. I response him about his word,
 I am not able to use any of unix filters like grep , wc etc


But you intentionally made it in different way.
If you can provide example about "Using SQL command without log on it " then you can provide.No problem of me.
Re: can I execute SQL commands in the unix command line [message #258534 is a reply to message #258520] Mon, 13 August 2007 00:23 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
My post 21:49 Accurately reported your original post which you re-wrote to erase your incorrect answer.
Which is why I requested that you provide supporting evidence that you could provide the claimed solution.
After being caught with an unsupportable post, your rewrote it to CYA & now you refuse to admit you were wrong.
Re: can I execute SQL commands in the unix command line [message #258536 is a reply to message #258520] Mon, 13 August 2007 00:27 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

you were wrong.

Who is free from wrong?
while making wrong learn something.
Anyway what I update is I just coded his quote so that you can see which one I highlighted.
Re: can I execute SQL commands in the unix command line [message #258539 is a reply to message #258520] Mon, 13 August 2007 00:39 Go to previous messageGo to next message
BlackSwan
Messages: 22489
Registered: January 2009
Senior Member
>Who is free from wrong?
Nobody, but true professionals admit their mistakes rather than trying to cover them up/erase/overwrite them like an emabrassed child.
You could have left your original post intact & posted a correction like an honest professional.

[Updated on: Mon, 13 August 2007 00:53] by Moderator

Report message to a moderator

Re: can I execute SQL commands in the unix command line [message #258545 is a reply to message #258520] Mon, 13 August 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no such command but it is easy to create a shell script.
Something like:
req=""
for p
do
  req="${req} ${p}"
done
req="${req};"
sqlplus -s / <<MCA
whenever sqlerror exit 1
set pagesize 10000
set linesize 1000
set feedback off
set trimout  on
$req
exit
MCA

Regards
Michel
Re: can I execute SQL commands in the unix command line [message #258962 is a reply to message #258520] Mon, 13 August 2007 22:50 Go to previous message
kveaswaran
Messages: 4
Registered: August 2007
Junior Member
Thanks,


Will try the same...
Previous Topic: Display 256 problem !!!
Next Topic: Urgent! - Set permission for user in Unix
Goto Forum:
  


Current Time: Thu Jul 24 06:23:14 CDT 2014

Total time taken to generate the page: 0.09751 seconds