OraFAQ Forum: Unix » can I execute SQL commands in the unix command line

Home » Infrastructure » Unix » can I execute SQL commands in the unix command line

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

can I execute SQL commands in the unix command line [message #258520]

Sun, 12 August 2007 23:31

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.

Report message to a moderator

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

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Use , !/host command in SQL*plus to access unix commands.
Please provide working example

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Arju,
why did you completely REWRITE your erroneous response after being called to provide working example of your original/incorrect post?

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Michel Cadot
Messages: 68641
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

Report message to a moderator

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

kveaswaran
Messages: 4
Registered: August 2007

Junior Member

Thanks,

Will try the same...

Report message to a moderator

Previous Topic:	Display 256 problem !!!
Next Topic:	Urgent! - Set permission for user in Unix

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 05:09:33 CDT 2024