RE: simple sql problem
After testing prompted by Jared's reply, you can use 1 of the 2 alternatives from Windows. That is, under windows the following works: echo select * from dual; | sqlplus scott/tiger@abcd I often use this type of syntax under NT to (say) log the time as below, but had never thought to try it with sqlplus. eg time example C:\>echo.|time|find "current" /i The current time is: 10:54:24.94 Hope this helps someone, Bruce Reardon -Original Message- Sent: Wednesday, 5 December 2001 3:25 As Scott said, in Unix you can use Input Redirection. If you are in windows, you could always build the command as a .sql file and use the sqlplus -s scott/tiger @myfile.sql Command. -Original Message- Sent: Tuesday, December 04, 2001 10:01 AM >From the unix command line you can run: sqlplus -s scott/tiger << EOF select * from emp; EOF Viraj Luthra wrote: > > Hello all, > > Why cannot I do :- > > sqlplus -s scott/tiger select * from emp; > > or sqlplus -s scott/tiger "select * from emp;" > > Both the cases I get :- > > Usage: SQLPLUS [] [[/] [@]] >[@ [] [] ...] > where ::= { -s | -? } > -s for silent mode and -? to obtain version number > > What is wrong above? I need to run the query from command line. How do I modify this :- > > sqlplus -s scott/tiger select * from emp; > > Rgds, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
As Scott said, in Unix you can use Input Redirection. If you are in windows, you could always build the command as a .sql file and use the sqlplus -s scott/tiger @myfile.sql Command. -Original Message- Sent: Tuesday, December 04, 2001 10:01 AM To: Multiple recipients of list ORACLE-L >From the unix command line you can run: sqlplus -s scott/tiger << EOF select * from emp; EOF Viraj Luthra wrote: > > Hello all, > > Why cannot I do :- > > sqlplus -s scott/tiger select * from emp; > > or sqlplus -s scott/tiger "select * from emp;" > > Both the cases I get :- > > Usage: SQLPLUS [] [[/] [@]] >[@ [] [] ...] > where ::= { -s | -? } > -s for silent mode and -? to obtain version number > > What is wrong above? I need to run the query from command line. How do I modify this :- > > sqlplus -s scott/tiger select * from emp; > > Rgds, > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple sql problem
>From the unix command line you can run: sqlplus -s scott/tiger << EOF select * from emp; EOF Viraj Luthra wrote: > > Hello all, > > Why cannot I do :- > > sqlplus -s scott/tiger select * from emp; > > or sqlplus -s scott/tiger "select * from emp;" > > Both the cases I get :- > > Usage: SQLPLUS [] [[/] [@]] >[@ [] [] ...] > where ::= { -s | -? } > -s for silent mode and -? to obtain version number > > What is wrong above? I need to run the query from command line. How do I modify this >:- > > sqlplus -s scott/tiger select * from emp; > > Rgds, > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Viraj - Your SQL isn't terminated. You need to end your SQL with either a semicolon or a "/" on the next line. Jared - Thanks for passing along this technique. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, December 04, 2001 12:35 AM To: Multiple recipients of list ORACLE-L Jared, Using the 2nd option:- sqlplus system/manager@orcl815 < 234567 Disconnected from Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production But if I use the 1st option :- eg echo "SELECT a.username, substr(sql_text,1,100), substr(sql_text,101,250),substr(sql_text,601,250),substr(sql_text,851,250), substr(sql_text,1101,250) FROM dba_users a, v$session, v$sqlarea where parsing_user_id=user_id AND address=sql_address(+) and sid=15;"|sqlplus system/manager I get this error:- SQL> 234 FROM dba_users a, v, v where parsing_user_id=user_id AND address=sql_address(+) * ERROR at line 3: ORA-00942: table or view does not exist But if I execute the query on its own, I get proper results. So in both cases I am not able to get results. What am I doing wrong? Rgds, -- On Mon, 3 Dec 2001 21:38:36 Jared Still wrote: > >The way you're doing it sqlplus is trying to parse the SQL >as a command line argument, which will not work. > >Try these: > >1. > >echo "select * from dual;" | sqlplus scott/tiger > >2. > >sqlplus scott/tiger
RE: simple sql problem
b'coz you have to type select * from v\$instance;" you are passing arguments from shell. You have to escape $ like \$ HTH -Original Message- Sent: Tuesday, December 04, 2001 3:50 AM To: Multiple recipients of list ORACLE-L Even using like this:- echo "select * from v$instance;" | sqlplus system/manager gives me the error :- SQL> select * from v * ERROR at line 1: ORA-00942: table or view does not exist Though I can do the same sql statement from inside sqlplus with ease, so why does it give table or view does not exist? I am confused .. rgds, -- On Mon, 03 Dec 2001 21:50:17 Ross Collado wrote: >I don't believe SQLPlus will let you run a SQL statement in the command line >itself. As you can see in Usage:, it doesn't have an option for this. >However, if you insist on running SQL statements in the command line itself, >you can probably do something like (only in Unix): > >sqlplus -s scott/tiger << ! >select * from emp; >! > >or > >save your select * from emp; to a sql file and do : >sqlplus -s scott/tiger @myfile.sql > >hth >Ross > >> -Original Message- >> From: Viraj Luthra [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, 4 December 2001 16:11 >> To: Multiple recipients of list ORACLE-L >> Subject: simple sql problem >> >> >> Hello all, >> >> Why cannot I do :- >> >> sqlplus -s scott/tiger select * from emp; >> >> or sqlplus -s scott/tiger "select * from emp;" >> >> Both the cases I get :- >> >> Usage: SQLPLUS [] [[/] [@]] >>[@ [] [] ...] >> where ::= { -s | -? } >> -s for silent mode and -? to obtain version number >> >> >> What is wrong above? I need to run the query from command >> line. How do I modify this :- >> >> sqlplus -s scott/tiger select * from emp; >> >> >> Rgds, >> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- >> Author: Viraj Luthra >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >> San Diego, California-- Public Internet access / Mailing Lists >> >> To REMOVE yourself from this mailing list, send an E-Mail message >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >> the message BODY, include a line containing: UNSUB ORACLE-L >> (or the name of mailing list you want to be removed from). You may >> also send the HELP command for other information (like subscribing). >> >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ross Collado > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
If you are running it on Unix, you must "escape" the "$" in "v$instance". thus, you would do echo "select * from v\$instance;" | sqlplus system/manager Note the "\" key before the "$". Hemant "Viraj Luthra" <[EMAIL PROTECTED]> 04/12/2001 02:50 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: RE: simple sql problem Even using like this:- echo "select * from v$instance;" | sqlplus system/manager gives me the error :- SQL> select * from v * ERROR at line 1: ORA-00942: table or view does not exist Though I can do the same sql statement from inside sqlplus with ease, so why does it give table or view does not exist? I am confused .. rgds, -- On Mon, 03 Dec 2001 21:50:17 Ross Collado wrote: >I don't believe SQLPlus will let you run a SQL statement in the command line >itself. As you can see in Usage:, it doesn't have an option for this. >However, if you insist on running SQL statements in the command line itself, >you can probably do something like (only in Unix): > >sqlplus -s scott/tiger << ! >select * from emp; >! > >or > >save your select * from emp; to a sql file and do : >sqlplus -s scott/tiger @myfile.sql > >hth >Ross > >> -Original Message- >> From: Viraj Luthra [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, 4 December 2001 16:11 >> To: Multiple recipients of list ORACLE-L >> Subject: simple sql problem >> >> >> Hello all, >> >> Why cannot I do :- >> >> sqlplus -s scott/tiger select * from emp; >> >> or sqlplus -s scott/tiger "select * from emp;" >> >> Both the cases I get :- >> >> Usage: SQLPLUS [] [[/] [@]] >>[@ [] [] ...] >> where ::= { -s | -? } >> -s for silent mode and -? to obtain version number >> >> >> What is wrong above? I need to run the query from command >> line. How do I modify this :- >> >> sqlplus -s scott/tiger select * from emp; >> >> >> Rgds, >> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- >> Author: Viraj Luthra >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >> San Diego, California-- Public Internet access / Mailing Lists >> >> To REMOVE yourself from this mailing list, send an E-Mail message >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >> the message BODY, include a line containing: UNSUB ORACLE-L >> (or the name of mailing list you want to be removed from). You may >> also send the HELP command for other information (like subscribing). >> >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ross Collado > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the mes
RE: simple sql problem
Even using like this:- echo "select * from v$instance;" | sqlplus system/manager gives me the error :- SQL> select * from v * ERROR at line 1: ORA-00942: table or view does not exist Though I can do the same sql statement from inside sqlplus with ease, so why does it give table or view does not exist? I am confused .. rgds, -- On Mon, 03 Dec 2001 21:50:17 Ross Collado wrote: >I don't believe SQLPlus will let you run a SQL statement in the command line >itself. As you can see in Usage:, it doesn't have an option for this. >However, if you insist on running SQL statements in the command line itself, >you can probably do something like (only in Unix): > >sqlplus -s scott/tiger << ! >select * from emp; >! > >or > >save your select * from emp; to a sql file and do : >sqlplus -s scott/tiger @myfile.sql > >hth >Ross > >> -Original Message- >> From: Viraj Luthra [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, 4 December 2001 16:11 >> To: Multiple recipients of list ORACLE-L >> Subject: simple sql problem >> >> >> Hello all, >> >> Why cannot I do :- >> >> sqlplus -s scott/tiger select * from emp; >> >> or sqlplus -s scott/tiger "select * from emp;" >> >> Both the cases I get :- >> >> Usage: SQLPLUS [] [[/] [@]] >>[@ [] [] ...] >> where ::= { -s | -? } >> -s for silent mode and -? to obtain version number >> >> >> What is wrong above? I need to run the query from command >> line. How do I modify this :- >> >> sqlplus -s scott/tiger select * from emp; >> >> >> Rgds, >> >> >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.com >> -- >> Author: Viraj Luthra >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >> San Diego, California-- Public Internet access / Mailing Lists >> >> To REMOVE yourself from this mailing list, send an E-Mail message >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >> the message BODY, include a line containing: UNSUB ORACLE-L >> (or the name of mailing list you want to be removed from). You may >> also send the HELP command for other information (like subscribing). >> >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ross Collado > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem Hi... You cann't run sql statement in command line directly However you can in case of script file. Make the query as a script file. Then run it on SQLPLUS. -- c:\test.sql - select * from emp / exit / -- c:\> sqlplus -s scott/tiger@oranir @c:\test This will work. HTH. Rgds, Nirmal, -Original Message- From: Viraj Luthra [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, December 04, 2001 8:11 AM To: Multiple recipients of list ORACLE-L Subject: simple sql problem Hello all, Why cannot I do :- sqlplus -s scott/tiger select * from emp; or sqlplus -s scott/tiger "select * from emp;" Both the cases I get :- Usage: SQLPLUS [] [[/] [@]] [@ [] [] ...] where ::= { -s | -? } -s for silent mode and -? to obtain version number What is wrong above? I need to run the query from command line. How do I modify this :- sqlplus -s scott/tiger select * from emp; Rgds, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
try this $ echo "select * from emp;" | sqlplus scott/tiger srinivas -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 04, 2001 2:31 AM To: Multiple recipients of list ORACLE-L You can't have the SQL statements on the command line. You must put them in a script file (eg myscript.sql) and execute it with the @ sign. Thus, sqlplus -s scott/tiger @myscript[note the SPACE between the "tiger" and the "@"]. Hemant "Viraj Luthra" <[EMAIL PROTECTED]> 04/12/2001 01:10 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: simple sql problem Hello all, Why cannot I do :- sqlplus -s scott/tiger select * from emp; or sqlplus -s scott/tiger "select * from emp;" Both the cases I get :- Usage: SQLPLUS [] [[/] [@]] [@ [] [] ...] where ::= { -s | -? } -s for silent mode and -? to obtain version number What is wrong above? I need to run the query from command line. How do I modify this :- sqlplus -s scott/tiger select * from emp; Rgds, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). [This e-mail is confidential and may also be privileged. If you are not the intended recipient, please delete it and notify us immediately; you should not copy or use it for any purpose, nor disclose its contents to any other person. Thank you.] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple sql problem
Jared, Using the 2nd option:- sqlplus system/manager@orcl815 < 234567 Disconnected from Oracle8i Release 8.1.5.0.0 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production But if I use the 1st option :- eg echo "SELECT a.username, substr(sql_text,1,100), substr(sql_text,101,250),substr(sql_text,601,250),substr(sql_text,851,250), substr(sql_text,1101,250) FROM dba_users a, v$session, v$sqlarea where parsing_user_id=user_id AND address=sql_address(+) and sid=15;"|sqlplus system/manager I get this error:- SQL> 234 FROM dba_users a, v, v where parsing_user_id=user_id AND address=sql_address(+) * ERROR at line 3: ORA-00942: table or view does not exist But if I execute the query on its own, I get proper results. So in both cases I am not able to get results. What am I doing wrong? Rgds, -- On Mon, 3 Dec 2001 21:38:36 Jared Still wrote: > >The way you're doing it sqlplus is trying to parse the SQL >as a command line argument, which will not work. > >Try these: > >1. > >echo "select * from dual;" | sqlplus scott/tiger > >2. > >sqlplus scott/tiger
Re: simple sql problem
The way you're doing it sqlplus is trying to parse the SQL as a command line argument, which will not work. Try these: 1. echo "select * from dual;" | sqlplus scott/tiger 2. sqlplus scott/tiger < Hello all, > > Why cannot I do :- > > sqlplus -s scott/tiger select * from emp; > > or sqlplus -s scott/tiger "select * from emp;" > > Both the cases I get :- > > Usage: SQLPLUS [] [[/] [@]] >[@ [] [] ...] > where ::= { -s | -? } > -s for silent mode and -? to obtain version number > > > What is wrong above? I need to run the query from command line. How do I > modify this :- > > sqlplus -s scott/tiger select * from emp; > > > Rgds, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
I don't believe SQLPlus will let you run a SQL statement in the command line itself. As you can see in Usage:, it doesn't have an option for this. However, if you insist on running SQL statements in the command line itself, you can probably do something like (only in Unix): sqlplus -s scott/tiger << ! select * from emp; ! or save your select * from emp; to a sql file and do : sqlplus -s scott/tiger @myfile.sql hth Ross > -Original Message- > From: Viraj Luthra [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, 4 December 2001 16:11 > To: Multiple recipients of list ORACLE-L > Subject: simple sql problem > > > Hello all, > > Why cannot I do :- > > sqlplus -s scott/tiger select * from emp; > > or sqlplus -s scott/tiger "select * from emp;" > > Both the cases I get :- > > Usage: SQLPLUS [] [[/] [@]] >[@ [] [] ...] > where ::= { -s | -? } > -s for silent mode and -? to obtain version number > > > What is wrong above? I need to run the query from command > line. How do I modify this :- > > sqlplus -s scott/tiger select * from emp; > > > Rgds, > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ross Collado INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple sql problem
You can't have the SQL statements on the command line. You must put them in a script file (eg myscript.sql) and execute it with the @ sign. Thus, sqlplus -s scott/tiger @myscript[note the SPACE between the "tiger" and the "@"]. Hemant "Viraj Luthra" <[EMAIL PROTECTED]> 04/12/2001 01:10 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group) Subject: simple sql problem Hello all, Why cannot I do :- sqlplus -s scott/tiger select * from emp; or sqlplus -s scott/tiger "select * from emp;" Both the cases I get :- Usage: SQLPLUS [] [[/] [@]] [@ [] [] ...] where ::= { -s | -? } -s for silent mode and -? to obtain version number What is wrong above? I need to run the query from command line. How do I modify this :- sqlplus -s scott/tiger select * from emp; Rgds, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). [This e-mail is confidential and may also be privileged. If you are not the intended recipient, please delete it and notify us immediately; you should not copy or use it for any purpose, nor disclose its contents to any other person. Thank you.] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Hi This will work out SELECT DECODE(SIGN(x+y-1) , 1,LPAD(TO_CHAR(x+y),LENGTH(x+y),'0'), LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0')) FROM blah Pulikkol Nitheesh Kumar Software Engineer Mannai Corporation C O S - PB # 76 Doha - Qatar Phone : 4412-555 extn -363 http//www.mannai.com.qa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pulikkol Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem but this will append 0 to numbers >1 (sum (a+b) >1) which is not the requirement -Original Message- From: Pulikkol Kumar [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 4:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: simple sql problem OR SELECT LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0') FROM BLAH -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pulikkol Kumar INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem > -Original Message- > From: Viraj Luthra [mailto:[EMAIL PROTECTED]] > > No, I mean, if (x+y) < 1, that is a value of .92 which it > prints out, but I want it to print out like, 0.92, that is a > "0." is concatenateed to the result. > > therefore, when i have, > > select x+y from blah > > I should get 90 when the value is really 90 > and I should get 0.92 when the value is really .92 Here is a starting point to help you. a) Read the manual on the following functions: sign decode to_char abs b) Look at the example below SQL> select 2 x, y, x + y as x_plus_y, 3 decode (sign (abs (x + y) - 1), 4 -1, to_char (x + y, '0.99'), 5 to_char (x + y, '99') 6 ) as formatted_x_plus_y 7 from t ; X Y X_PLUS_Y FORMA -- -- -- - .7 .3 1 1 -.4 1.8 1.4 1 .2 .16 .36 0.36 13 65 78 78 -20 -16.2 -36.2 -36 1 -1.4 -.4 -0.40 34 -34 0 0.00 7 rows selected. -- Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
RE: simple sql problem
Use Decode function Ramon Estevez *809-565-3121 x 225 * [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Viraj Luthra Enviado el: Tuesday, 31 July, 2001 12:10 AM Para: Multiple recipients of list ORACLE-L Asunto: simple sql problem Hello all, If I have a situation, where I have the following:- select x+y from blah and if x+y > 1, eg 5 then the output can be 5, but if x+y < 1, eg, .92, then I need the output as 0.92. How do I do this? rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: simple sql problem
Raja: Let me put my two cents into the idea bin. How about: select decode(sign(num-1),-1,'0' || to_char(num), to_char(num)) as num from t1; Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 31, 2001 1:10 AM > Hello all, > > If I have a situation, where I have the following:- > > select x+y from blah > > and if x+y > 1, eg 5 then the output can be 5, > but if x+y < 1, eg, .92, then I need the output as 0.92. > > How do I do this? > > rgds, > > raja > > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
OR SELECT LPAD(TO_CHAR(x+y),LENGTH(x+y)+1,'0') FROM BLAH -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pulikkol Kumar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem or, set numformat '0.99' (in sql*plus) -Original Message-From: Swapna_Chinnagangannagari [mailto:[EMAIL PROTECTED]]Sent: Tuesday, July 31, 2001 12:26 PMTo: Multiple recipients of list ORACLE-LSubject: RE: simple sql problem Hello Raja, You can try this way 1)select x+y sum1 from blah 2)col sum1 format 09.99 3)select x+y sum1 from blah rgds swapna -Original Message- From: Viraj Luthra [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 12:31 PM To: Multiple recipients of list ORACLE-L Subject: RE: simple sql problem No, I mean, if (x+y) < 1, that is a value of .92 which it prints out, but I want it to print out like, 0.92, that is a "0." is concatenateed to the result. therefore, when i have, select x+y from blah I should get 90 when the value is really 90 and I should get 0.92 when the value is really .92 Any comments rgds, raja -- On Mon, 30 Jul 2001 22:00:23 Amar Kumar Padhi wrote: >I believe what you meant is if it is less than one then the output should be >1. >if so: > >select (case when x + y > 1 then x + Y > else 1 > end) from blah; > > >-Original Message- >Sent: Tuesday, July 31, 2001 9:10 AM >To: Multiple recipients of list ORACLE-L > > >Hello all, > >If I have a situation, where I have the following:- > >select x+y from blah > >and if x+y > 1, eg 5 then the output can be 5, >but if x+y < 1, eg, .92, then I need the output as 0.92. > >How do I do this? > >rgds, > >raja > > >Get 250 color business cards for FREE! >http://businesscards.lycos.com/vp/fastpath/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Viraj Luthra > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem Hello Raja, You can try this way 1)select x+y sum1 from blah 2)col sum1 format 09.99 3)select x+y sum1 from blah rgds swapna -Original Message- From: Viraj Luthra [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 12:31 PM To: Multiple recipients of list ORACLE-L Subject: RE: simple sql problem No, I mean, if (x+y) < 1, that is a value of .92 which it prints out, but I want it to print out like, 0.92, that is a "0." is concatenateed to the result. therefore, when i have, select x+y from blah I should get 90 when the value is really 90 and I should get 0.92 when the value is really .92 Any comments rgds, raja -- On Mon, 30 Jul 2001 22:00:23 Amar Kumar Padhi wrote: >I believe what you meant is if it is less than one then the output should be >1. >if so: > >select (case when x + y > 1 then x + Y > else 1 > end) from blah; > > >-Original Message- >Sent: Tuesday, July 31, 2001 9:10 AM >To: Multiple recipients of list ORACLE-L > > >Hello all, > >If I have a situation, where I have the following:- > >select x+y from blah > >and if x+y > 1, eg 5 then the output can be 5, >but if x+y < 1, eg, .92, then I need the output as 0.92. > >How do I do this? > >rgds, > >raja > > >Get 250 color business cards for FREE! >http://businesscards.lycos.com/vp/fastpath/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Viraj Luthra > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
No, I mean, if (x+y) < 1, that is a value of .92 which it prints out, but I want it to print out like, 0.92, that is a "0." is concatenateed to the result. therefore, when i have, select x+y from blah I should get 90 when the value is really 90 and I should get 0.92 when the value is really .92 Any comments rgds, raja -- On Mon, 30 Jul 2001 22:00:23 Amar Kumar Padhi wrote: >I believe what you meant is if it is less than one then the output should be >1. >if so: > >select (case when x + y > 1 then x + Y > else 1 >end) from blah; > > >-Original Message- >Sent: Tuesday, July 31, 2001 9:10 AM >To: Multiple recipients of list ORACLE-L > > >Hello all, > >If I have a situation, where I have the following:- > >select x+y from blah > >and if x+y > 1, eg 5 then the output can be 5, >but if x+y < 1, eg, .92, then I need the output as 0.92. > >How do I do this? > >rgds, > >raja > > >Get 250 color business cards for FREE! >http://businesscards.lycos.com/vp/fastpath/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Viraj Luthra > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: simple sql problem
Title: RE: simple sql problem I believe what you meant is if it is less than one then the output should be 1. if so: select (case when x + y > 1 then x + Y else 1 end) from blah; -Original Message- From: Viraj Luthra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 9:10 AM To: Multiple recipients of list ORACLE-L Subject: simple sql problem Hello all, If I have a situation, where I have the following:- select x+y from blah and if x+y > 1, eg 5 then the output can be 5, but if x+y < 1, eg, .92, then I need the output as 0.92. How do I do this? rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).