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

A field is missing in my table.....

I have a table CUST. It has ten fields and CUST_FLAG is one of those fields. The table has 3 lac records. For one particular record, the field CUST_FLAG is missing! .... It doesn't make sense to me because every other record has that field . when I try - SELECT CUST_FLAG FROM CUST WHERE CUST_ID='1234'
it just shows a blank screen instead of the CUST_FLAG field.. can someone explain me why this is happening?
Thanks in advance. 
are you sure ? one of these hard disk worms might have eaten the field !
but on the other hand the value of the field in this record can be null or some white space
in sqlplus you can do
SQL> set NULL '<null>'
then retry your query. 
it just shows a blank screen instead of the CUST_FLAG field.. can someone explain me why this is happening?NULL value ?
Can you paste here the two differents result ?
Nicolas. 
This is the query and the output:
SELECT FEE_REN_DATE, CUST_FLAG, REN_STATUS FROM CUST WHERE CUST_ID='1234'
Record No. 1 :
FEE_REN_DATE : 060131
REN_STATUS : NULL
Please note that even the field name "CUST_FLAG" is not being displayed in the output... If CUST_FLAG contains a NULL value, then that should be displayed in the output.... 
what is your value of the cursor_sharing parameter (show parameter cursor) 
exact is the value for cursor_sharing parameter. 
Please verify if you have a value for CUST_ID='1234'.
See the following example :
SCOTT#demo102> set feed off
SCOTT#demo102> select ename from emp where empno=7900;
ENAME
----------
JAMES
SCOTT#demo102> select ename from emp where empno=1234;
SCOTT#demo102> set feed on
SCOTT#demo102> /
no rows selected
SCOTT#demo102> Nicolas. 
if it is true that you do not see the column name when you start the upper query only for this one record
AND your session did for sure not step into a known 9.2.0.? bug on some OS with cursor_sharing = force (in this latter case explicitely do: alter session set cursor_sharing = exact;)
AND
this happens when you start a new sqlplus without some col settings THEN
what happens if you say
update <table> set <col> = <col> where <col> = ...; 
I did exactly as you instructed ...
SQL> set feed off;
SQL> select cust_flag from cust where cust_id='1234';
C
-
SQL> set feed on;
SQL> /
C
-
1 row selected. 
Please tell me if I understood you :
1. It is true that I do not see the column name for this one record.
2. If cursor_sharing=force , there is some bug on OS. Now I should alter the session so that cursor_sharing is set to exact.
3. This problem happens when a new sqlplus is started without some column settings. May I ask what those col settings are?
Please correct me if Im wrong ... 
Well, definitely, null value into your column.
Try :
select nvl(cust_flag,0) from cust where cust_id='1234';Nicolas. 
select nvl(cust_flag,0) from cust where cust_id='1234';
Record No. 1 :
This is what Im getting....I agree the column cust_flag is having NULL, but the output should be like :
Record No. 1 :
CUST_FLAG : NULL
right? Please tell me why this NULL value is not getting displayed... 
What tools did you use ?
Nicolas. 
pls ignore my last reply...
select nvl(cust_flag,0) from cust where cust_id='1234'; --should give
Record No. 1 :
CUST_FLAG : 0
but im getting,
Record No. 1 :
Kindly clarify why the column name 'CUST_FLAG' is not getting displayed.. 
Right now Im accessing the Production database using "TECH10"

one colum did not show me the required value

when i use the below update command it will show me the garbidge value..... before it will show me the correct value.
SELECT * FROM ID_TICKET_DETAILS WHERE TICKET_NUMBER='IX05247542'
UPDATE ID_TICKET_DETAILS
SET TICKET_CURRENCY='AED'
WHERE TICKET_NUMBER='IX05247542' AND TICKET_AIRLINE_RELEASE='AIX'
when i select it will show me garbidge value insted of AED ...
any one can help me in this regards;
thanks
lala 
Is it perhaps that your where clause on the select statement is different to that of the update statement.
AND     TICKET_AIRLINE_RELEASE    = 'AIX' -- missing from select 
Hi,
user530764 wrote:
when i use the below update command it will show me the garbidge value..... before it will show me the correct value.What ido you mean by "the garbage value"?
SELECT * FROM ID_TICKET_DETAILS WHERE TICKET_NUMBER='IX05247542'
UPDATE ID_TICKET_DETAILS
SET TICKET_CURRENCY='AED'
WHERE TICKET_NUMBER='IX05247542' AND TICKET_AIRLINE_RELEASE='AIX'
when i select it will show me garbidge value insted of AED ...
any one can help me in this regards;That depends on your data and what you want the SELECT statement to show. I don't know either of those things; you'll have to explain them to me first.
Post CREATE TABLE and INSERT statements for the ticket_details table as it exists before the UPDATE.
Also, post what you would like to see from a query (such as the one you posted) if you ran that query before the UPDATE, and the results you want from the same query after the UPDATE. 
Hi lala,
1) Which column you are retreiving / looking for in your select list?
2) If your select query is
SELECT TICKET_CURRENCY FROM ID_TICKET_DETAILS WHERE TICKET_NUMBER='IX05247542' ;
after your update statement , then what garbage value it is returning ?
3) Are you using different sessions ? one for update and another for select ? if so are you committed the transactions after the update command ? 
while the develper is using sql navegator
when i am tring to update the above command using sql
i will take time and in the final it will through an erro end of communication channel like that
SQL> conn etttms2/etttms2
Connected.
SQL> update id_ticket_details
2 set ticket_currency='AED'
3 where ticket_number='IX05247542' and ticket_airline_release='AIX';
update id_ticket_details
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 
Connected.
SQL> SELECT TICKET_CURRENCY FROM ID_TICKET_DETAILS WHERE TICKET_NUMBER='IX05247542' ;
TICKET_CUR
----------
?+, +?1?08
when i try this query it will show me the above result
any one help me in this regards please
regards
lala 
pleae any one help me in the above queries
thanks
lala 
what is the data you have for the ticket_currency in the table? can you please post it? 
Can you post the results of the query:
SELECT     *
FROM     ID_TICKET_DETAILS
WHERE     TICKET_NUMBER     = 'IX05247542'
;or even just the results of:
SELECT     TICKET_NUMBER
,     TICKET_CURRENCY
,     TICKET_AIRLINE_RELEASE
FROM     ID_TICKET_DETAILS
WHERE     TICKET_NUMBER     = 'IX05247542'
;Edited by: user11033437 on May 26, 2010 11:22 AM: fixed typo 
SQL> SELECT TICKET_NUMBER
2 , TICKET_CURRENCY
3 , TICKET_AIRLINE_RELEASE
4 FROM etttms2.ID_TICKET_DETAILS
5 WHERE TICKET_NUMBER = 'IX05247542';
TICKET_NUMBER TICKET_CUR
-------------------- ----------
TICKET_AIR
----------
IX05247542 ?+, +?1?08
AIX
while the other is hange
SELECT     *
FROM     ID_TICKET_DETAILS
WHERE     TICKET_NUMBER     = 'IX05247542'
hang
no result no output
regards
lala 
ticket_currency DATA IS 'AED' 
>
while the other is hange
SELECT     *
FROM     ID_TICKET_DETAILS
WHERE     TICKET_NUMBER     = 'IX05247542'
hang
no result no output
>
That's beyond my abilities... maybe one of the more experienced members of the forum can help you with that one...?

queryyyyyyyyiiii?????///

hai,
plz give me plsql code regarding this issue
when ever i am giving table name in sql prompt then it executed and shows that select * from table query.
how u write the cursor.
sql>emp;
expected output was select * from emp query
regards 
at the SQL prompt, try typing the following
select * from emp; 
First have meaningful and proper subject lines.
May be this is what you want?
SQL> create table abc as select rownum rn from dual connect by rownum<=10;
Table created.
SQL> select count(*) from &table_name;
Enter value for table_name: abc
old   1: select count(*) from &table_name
new   1: select count(*) from abc
  COUNT(*)
----------
        10 
Hai,
if u give table name in sql promt then i want expected out put as complte data in that table to show
sql> xxxxxxx_tab_ggggggg_emp: { table name only)[/b]
we want to get o/p as select * from that table:
if u have any idea using cursor in plsql we will do
regards 
how to give dynamically  input as a Table name 
>Hai,
I think you may need oracle's LOLCODE virtual machine installed,
once you've got that, execute this
I IZ STORED PROC RUNFROMCURSOR
HAI
CAN HAS STDIO?
CURSOR IZ SELECT * FROM :TABLE
IM IN YR LOOP OPENIN YR CURSOR UNTIL IZ NO DATA FOUND
   DBMS_OUTPUT.VISIBLE(CURSOR.ROW)
IM OUTTA YR LOOP
KTHXBYE 
That was so good I'm going to bookmark this thread.
Tom 
... and don't forget to
SET SERVEROUTPUT ON;-) 
For the LOLCODE virtual machine, that would be:
SET SVROP nJohn 
not to be too nit picky but wouldn't that be
YO SVROP n:)

I want to remove the DOC> line from the SQL+ window, how to do?

Hi there,
Every time i execute the .sql file from he SQL window it displays all the documentation part of the file. This displays with the prompt tag DOC>.
I dont want this documentation to display on to the window. How do i do this? 
Remove those lines from the script file ?
Nicolas. 
I want to keep those comments / notes. So that the notes for that script file stays in one place. But i dont want those notes displayed to the screeen.
eg:
select * from A;
/*
Note:
this is select statement, it retreives the data from table A
*/
I dont want that note to be displayed to screen. 
[url http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apc.htm#i656750]set doc off
Regards,
Rob. 
select * from A;
/*
Note:
this is select statement, it retreives the data from table
A
*/
I dont want that note to be displayed to screen. SQL> #e:\scripts\sql\test_doc
SQL> select * from dual;
D
-
X
SQL>
SQL> /*
SQL> Note:
SQL> this is select statement, it retreives the data from table DUAL
SQL> */
SQL>
SQL> set echo off
SQL> #e:\scripts\sql\test_doc
D
-
X
SQL> Nicolas.
Message was edited by:
N. Gasparotto

need help regarding updated rows

Dear sir,
need help on this query
how many rows got updated when i execute an update statement 
right after executing the UPDATE, you can use SQL%ROWCOUNT to see the number of affected rows. 
Hi,
if there is no WHERE conditions then all the rows in the table, it depends upon the WHERE clause....
tahnks 
If you are executing update statement from sqlplus session.
Make sure "feedback" is turned on. It'll show numbers of rows updated just after the command;
SQL> update tt1 set col1 = 1;
0 rows updated.
SQL> show feedback
FEEDBACK ON for 1 or more rows
SQL> set feedback off
SQL> update tt1 set col1 = 1;
SQL>Details about this sqlplus system setting:
FEED[BACK] {6|n|OFF|ON}
Display the number of records returned (when rows >= n )
OFF (or n=0) will turn the display off
ON will set n=1
Regards,
Ullhas

Categories

Resources