Sql*Plus- simple question - SQL & PL/SQL

Hi ,
Which Sql*Plus environment option I have to set so that I will
not get " X Rows Selected" at the end of an Sql Select Statement.
eg,
sql> select * from emp;
.....
.....
.....
sql> 14 rows selected --- how to suppress this.
Thanks 

set feedback off 

set feedback off

Related

Send OP of one sql statement to Excell sheet

Hi ,
How can I send the OP of a sql statement to a excell sheet like
select invoice_no,invoice_date,invoice_amount
from invoice_tab
where some_conditions...
I like the the OP to go to excell like this...
I001     1-Jan-08     1200
I002     2-Jan-08     1300
Each value should be in different cells
rgds
Suman 
Pretty vague but here's one method using SQLPLUS
set colsep ,
spool d:\temp\somecsv.csv
select * from all_objects where rownum < 10;
spool off 
Hi thank for your reply
the Op comes like this...
SQL> select * from all_objects where rownum < 10;
OWNER ,OBJECT_NAME
------------------------------,------------------------------
SUBOBJECT_NAME , OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE
------------------------------,----------,--------------,-------------------
CREATED ,LAST_DDL_,TIMESTAMP ,STATUS ,T,G,S
---------,---------,-------------------,-------,-,-,-
SYS ,ICOL$
, 20, 2,TABLE
09-MAR-04,10-MAR-04,2004-03-09:23:57:57,VALID ,N,N,N
SYS ,I_USER1
, 44, 44,INDEX
09-MAR-04,09-MAR-04,2004-03-09:23:57:58,VALID ,N,N,N
But I like like the OP not in this way, but each column in each cell of excell file 
Use the method specified by michaels Re: data transfer from oracle table to exl file:
SQL> set markup html on;
SQL> spool d:\test.xls
<br>
SQL> select * from emp;
13 rows selected.
SQL> spool off;
SQL> set markup html off;Jith 
Hi,
Thanks it worked fine. I got the output exactly the way I wanted. 
Hi,
SQL> set markup html on
SQL>
How can I give SQL> after this statement pls advise me.
Thanks,
Ya I got it..
Message was edited by:
user594309

spooling a file

hi
i want to spool some fields from the table.
eg.
spool one.txt
select id, name from table_abc
spool off
now i want the output in the file to be like
id,name
1,oracle
i mean i dont want this select statement in the file and it should be comma seperated. 
Try this:
set feedback off;
select id||','||name as "id,name" from table_abc;
and have a look at the SQL plus users guide to see what other parameters you may want to use:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm
Rene 
set tab off
set feedback off echo off verify off 
spool one.txt
select id||','||name "id,name" from table_abc
spool off
-MS 
SORRY
IT didnt work
i am getting outut as
SQL> select id ||','||status as "id, status" from abc_table where rownum<10;
id, status
--------------------------------------------------------------------------------
3649,CURRENT
30520,CURRENT
30521,CURRENT
323,CURRENT
22,
334,CURRENT
3335,CURRENT
3336,CURRENT
337,CURRENT
SQL> spool off
i dont want SQL> select id ||','||status as "id, status" from abc_table where rownum<10;
and
SQL> spool off 
Did you do
set feedback off

problem in spool file

Hi,
Iam new to oracle. When I give the below query at SQL prompt.
SQL> select col1||'`ß`'||col2 from tablename where rownum<2;
1-J7WGX*`ß`*1-7OKC-23
Iam getting ß within appostropies...... If I remove appostropies and give the query it is throwing an error.
If I give the same query in spool as below.
SqlExport=`sqlplus -s username/password#databasename <<EOF
spool test1.sh;
set linesize 1000;
set PAGESIZE 0 space 0 feedback off echo off verify off heading off;
set termout off trimspool on;
select col1||'`ß`'||col2 from tablename where rownum<2;
exit sql.sqlcode;
spool off;
EXIT;
EOF`
test1.sh[11]: ||col2 from tablename where rownum<2;^Jexit sql.sqlcode;^Jspool off;^J EXIT;^J EOF: not found
SqlExport=ß
Please let me know any reason behind this.
I have set language parameters in .profile as
#EXTENDED CHARACTER SET SUPPORT
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set NLS_LIST_SEPARATOR=ß
export NLS_LIST_SEPARATOR 
May be this will help. I don't have linux installed on my system
In the script first define one variable with value. then you that variable in the query like:
SQL> var b nvarchar2(10);
SQL> begin :b:='`ß`'; end;
  2  /
PL/SQL procedure successfully completed.
SQL> select empno||:b || ename from  emp;
EMPNO||:B||ENAME
---------------------------------------------------------------------------------
1`ß`gggg
7369`ß`SMITH
7499`ß`ALLEN
7521`ß`WARD
7566`ß`JONES
7654`ß`MARTIN
7698`ß`BLAKE
7782`ß`CLARK
7788`ß`SCOTT
7839`ß`KING
7844`ß`TURNER
EMPNO||:B||ENAME
---------------------------------------------------------------------------------
7876`ß`ADAMS
7900`ß`JAMES
7902`ß`FORD
7934`ß`MILLER
15 rows selected.
SQL> 
I can't declare those variables in spool file . If I declare them in either unix script or spool it is throwing an error.
gcccdwg101.sh[130]: ; end;^Jselect col1||:b ||col2||:b ||col3 not found
SqlExport=ß
Is there anyway I can get only beta in oracle queries....... When I give beta it is throwing an error..... ORA:911 Invalid Identifier 
Or you can also use ascii value of the character like
SQL> select ENAME|| CHR(96)||CHR(50081)|| CHR(96)||ENAME from EMP where rownum<5
SQL> /
ENAME||CHR(96)||CHR(5008
------------------------
gggg`ß`gggg
SMITH`ß`SMITH
ALLEN`ß`ALLEN
WARD`ß`WARD
SQL> 
I suspect you removed the quotation marks instead of the apostrophes.
select col1||`ß`||col2 from tablename where rownum<2;won't work, but
select col1||'ß'||col2 from tablename where rownum<2;willl. 
I gave that query also I got the below error.
SQL> select col1||'ß'||col2 from tablename where rownum<2;
ERROR:
ORA-01756: quoted string not properly terminated 
I have run the below query at SQL prompt.
SQL> select col1|| CHR(96)||CHR(50081)|| CHR(96)||col2 from tablename where rownum<2;
col1||CHR(96)||CHR(50081)||CHR(96)||col2
--------------------------------------------------------------------------------
1-J7WGX`á`1-7OKC-23

feedback for no. of rows returned

hi please any one can help
y the output for the query
select empno from emp where comm is not null
the output will generate with data
but y it will not show as 4 rows selected 
did you SET FEEDBACK OFFRegards
Marcus 
You mean from the SQL*Plus prompt?
Then just use 'SET FEEDBACK ON':
SQL> select * from dual;
D
-
X
SQL> set feedback on
SQL> select * from dual;
D
-
X
1 row selected. 
SQL> set feed 4 ..or set to some other appropriate number.

Strange behaviour with greatest function .

Hi,
could You please explain this:
SQL> set null ****
SQL> select greatest(1,NULL) from dual;
GREATEST(1,NULL)
----------------
****
SQL> select greatest(NULL,NULL) from dual; --4 rows
G
-
*
*
*
*
SQL> select greatest(NULL,1) from dual; --4 rows ?
G
-
*
*
*
*
SQL> select greatest('',1) from dual; --4 rows ?
G
-
*
*
*
*
SQL> select greatest(1,'') from dual;
GREATEST(1,'')
--------------
****DB 9.2.0.8 , and why there are 4 rows sometimes ?
Regards
GregG 
NULL is something undefined. So how can you compare 1 with something undefined :-)
'' is NULL. 
It's not 4 rows, it's 1 row that is wrapping because oracle has determined the column display size to be 1 character wide (as it doesn't know any better, what with it being a null).
The 4 "rows" are just the 4 characters of the output "****" that you set null to display as.
p.s. the column display size will be determined by datatype of the first parameter of the function. 
It is only one row that is broken in four lines.
The reason is that sqlplus asumes that the column is only one character long.
So the content of the row is shown in four lines.
Please try this:
SQL> set null ****
SQL> column mycol format a1
SQL> select greatest(null,null) mycol from dual;
M
_
*
*
*
*
SQL> column mycol format a5
SQL> select greatest(null,null) mycol from dual;
MYCOL
-----
**** 
What is your ´feedback´ setting in sqlplus?
SQL> show feedback
FEEDBACK ON for 1 or more rows
SQL> set null ****
SQL> select null from dual;
More...
N
-
*
*
*
*
1 row selected.
SQL> 
Ok, got this.
Thanks for responses.
Regards
GregG

Categories

Resources