different output with sqlplus 2

[7280]

Hi,
I'm running this query from sqlplus on the same server but on different instances with sqlplus.
The result is the same but the output is different.

Query:
SELECT 'n' AS cn1
, '02' AS cn2
, LENGTH(1) AS length1
, INSTR(1,' ') AS space1
, LENGTH(2) AS length2
, INSTR(2,' ') AS space2
FROM dual;

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Mar 15 15:36:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production


1° execution:
INSTANCE_NAME
----------------
TEST


C CN LENGTH1 SPACE1 LENGTH2 SPACE2
- -- ---------- ---------- ---------- ----------
n 02 1 0 1 0

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Mar 15 15:36:32 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production


INSTANCE_NAME
----------------
TESTTEST


CN1 CN2 LENGTH1 SPACE1 LENGTH2 SPACE2
-------------------------------- -------------------------------- ---------- ---------- ---------- ----------
n 02 1 0 1 0

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production


Why the columns are formatted in different ways?
Is there something like glogin.sql that customizes the instances?
Thanks!

 

[SantaMufasa]

7280,

The difference in formatting results from your SQL*Plus environments. Your column aliases (CN1, CN2, LENGTH1, SPACE1, LENGTH2, SPACE2) have both default behaviours/definitions and/or you/a script can override the column-definition defaults via SQL*Plus's "COL" commands.

If you/a script consciously (/inadvertently) invoke a column (COL) definition, that definition stays "in place" during the rest of the SQL*Plus session, or until your issue the command "CLEAR COL", or until you explicitly override the previous column definition with a new column definition.

You can confirm column definitions with these SQL*Plus commands:

col

or

col <name>

the "col" command lists all currently defined column definitions, while "col <name>" (where you replace "<name>" with a specific column name), lists the column defintion for that single column.

Therefore, in your case, I would surmise that (unbeknownst to you) explicit column definitions are a occurring in one case, but not the other.

To try to isolate the behaviours, issue the command "clear col" prior to running each invocation of your query. Provided your invocation is identical, your outputs should then, also, be identical.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[7280]

Hi,
my query is the same, I run it from a shell script.

cat run_q.sh

export ORACLE_SID=TEST
sqlplus '/ as sysdba' @q_test

export ORACLE_SID=TESTTEST
sqlplus '/ as sysdba' @q_test

No col is set inside the script q_test.sql
However I added at the beginning of the script clear col but nothing changed.

What I have noticed is that in the instance where Oracle XDK for Java component is installed, the output is:
C CN LENGTH1 SPACE1 LENGTH2 SPACE2
- -- ---------- ---------- ---------- ----------
n 02 1 0 1 0

Where XDK is not installed, output is:
CN1 CN2 LENGTH1 SPACE1 LENGTH2 SPACE2
-------------------------------- -------------------------------- ---------- ---------- ---------- ----------
n 02 1 0 1 0

So maybe XDK 9.2.0.9 version changes something in sqlplus.
Is it possible?

Thanks,
Tarek

 

[SantaMufasa]

Actually, Tarek, the second set of query results does not exhibit typical Oracle default behaviour...If you have no explicit, default-overriding column definitions, the querying of character literal (as in your case) defaults to the length of the literal. Your first result set exhibits such behaviour; your second set is doing something atypical and inexplicable at this point, given the intelligence you provide.

Anyone else have troubleshooting ideas?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[7280]

On one instance I installed XDK component but that wasn't the problem.
Another thing is:
when I run query:
select comp_name, status from dba_registry;
where i have this output:
C CN LENGTH1 SPACE1 LENGTH2 SPACE2
- -- ---------- ---------- ---------- ----------
n 02 1 0 1 0
I get also the number of rows selected:
ex: 10 rows selected.

Where the output is:
CN1 CN2 LENGTH1 SPACE1 LENGTH2 SPACE2
-------------------------------- -------------------------------- ---------- ---------- ---------- ----------
n 02 1 0 1 0
and I run query:
select comp_name, status from dba_registry;
I don't have the number of the selected rows.

On this server I have about 10 intances and on some the query is correct and on the others no.
At the beginning I thought the difference was related to XDK but when I installed it on one of those instance nothing changed.
But maybe the rows selected output can help.
Thanks again!

 

[SantaMufasa]

7280,

Could you please post the results of:

select comp_id,version,status from dba_registry;

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[7280]

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Mar 15 16:55:24 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL> col version for a10
SQL> col comp_name for a40
SQL> col schema for a15
SQL> col status for a15
SQL> set lines 130
SQL> select comp_name, version, status, modified, schema from dba_registry;

COMP_NAME VERSION STATUS MODIFIED SCHEMA
---------------------------------------- ---------- --------------- -------------------- -----------
Oracle9i Catalog Views 9.2.0.7.0 VALID 31-JAN-2006 18:54:00 SYS
Oracle9i Packages and Types 9.2.0.7.0 VALID 31-JAN-2006 18:54:00 SYS
JServer JAVA Virtual Machine 9.2.0.7.0 VALID 29-NOV-2005 18:51:57 SYS
Oracle XDK for Java 9.2.0.9.0 VALID 31-JAN-2006 18:54:01 SYS
Oracle9i Java Packages 9.2.0.7.0 VALID 31-JAN-2006 18:54:01 SYS
Oracle Text 9.2.0.7.0 VALID 29-NOV-2005 18:55:27 SYS

6 rows selected.

SQL> select 'TEST' as T, length('TEST') from dual;

T LENGTH('TEST')
---- --------------
TEST 4

SQL> conn tarek@testtest
Enter password: ********
Connected.
SQL> col version for a10
SQL> col comp_name for a40
SQL> col schema for a15
SQL> col status for a15
SQL> set lines 130
SQL> select comp_name, version, status, modified, schema from dba_registry;

COMP_NAME VERSION STATUS MODIFIED SCHEMA
---------------------------------------- ---------- --------------- -------------------- -----------
Oracle9i Catalog Views 9.2.0.7.0 VALID 31-JAN-2006 18:55:14 SYS
Oracle9i Packages and Types 9.2.0.7.0 VALID 31-JAN-2006 18:55:14 SYS
Oracle Text 9.2.0.7.0 VALID 29-NOV-2005 19:22:45 SYS
JServer JAVA Virtual Machine 9.2.0.7.0 VALID 29-NOV-2005 19:20:52 SYS
Oracle9i Java Packages 9.2.0.7.0 VALID 31-JAN-2006 18:55:14 SYS

SQL> select 'TEST' as T, length('TEST') from dual;

T LENGTH('TEST')
-------------------------------- --------------
TEST 4

 

[SantaMufasa]

This just gets "curiouser and curiouser", Tarek. If you have access to Oracle's Metalink, then I would log a Service Request with them. I'm stumped.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[7280]

I'm opening an oracle TAR.
Thanks Mufasa for your help!

Ciao,
Tarek

 

[SantaMufasa]

BTW, Tarek, I'm certain you would have anyway, but once you and Oracle resolve the issue, be sure to post the resolution here so that we can all learn from your knowledge.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[7280]

of course!!
I'm waiting oracle assistance but I'm afraid it's a bug.

I already encountered a bug related with cpu jan 2006, patch nr. 4751923.

If you try to use Change Data Capture on 9.2.0.7 with this patch you'll face a bug.

 

[7280]

Hi,
found the problem, it's a bug.
Following is the solution.
Refer also to metalink noteid: 241464.1

CAUSE DETERMINATION
====================
CURSOR_SHARING = SIMILAR or CURSOR_SHARING = FORCE parameter setting is
causing this sqlplus formatting problem. Some code changes in 9203 patchset causes
this parameter to mess up SQL*Plus formatting.

CAUSE JUSTIFICATION
====================
Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

POTENTIAL SOLUTION(S)
======================

alter session set optimizer_mode=choose;
alter session set cursor_sharing=exact;

POTENTIAL SOLUTION JUSTIFICATION(S)
====================================

Note 241464.1 - TO_CHAR() , SUBSTR() AND RPAD() in SQL*Plus Display Length Too Long After Upgrading Database Version

 

[SantaMufasa]

Interesting, 7280. Thanks for posting the resolution. Because you provided such a "valuable post", you deserve a Purple Star...here it is.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.