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

-----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_at_orcl815 <<!
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 sys.dba_users a, v$session, v$sqlarea where parsing_user_id=user_id AND address=sql_address(+) and sid=15
!

I get nothing, like I get this :-

SQL*Plus: Release 8.1.5.0.0 - Production on Tue Dec 4 17:31:01 2001  

(c) Copyright 1999 Oracle Corporation. All rights reserved.    

Connected to:

Oracle8i Release 8.1.5.0.0 - Production

With the Java option

PL/SQL Release 8.1.5.0.0 - Production  

SQL> 2 3 4 5 6 7 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> 2 3 4 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 <<EOF

>select * from dual;

>EOF

>

>Jared

>

>

>On Monday 03 December 2001 21:10, 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 [<option>] [<user>[/<password>] [@<host>]]

>>                [@<startfile> [<parm1>] [<parm2>] ...]

>> where <option> ::= { -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: viraj999_at_lycos.com

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: ListGuru_at_fatcity.com (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: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

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: ListGuru_at_fatcity.com (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).