Case Used By My Query In Sql*Plus….

There was an interesting question which me and two other delegates were discussing in the last week’s session. One delegate asked that how can we make the queries fired use a particular case? I couldn’t recall any parameter which does so(any guesses?) . One solution which I proposed was to put all the queries in the stored programs. If they would be there in the stored procedures, it would be not possible to play around with their case-sensitivity. This also serves the mantra that all the things related to the database must remain within the database only. Another solution proposed by one delegate was to use triggers to make this kind of thing possible. I am not sure that how using triggers it would be possible but anyways, that’s not the point of this post.

So in this discussion, since we were using good’ol, terminal prompt, I just recalled that there is an option in this client tool to set the case to whatever we want! The default setting of this is set to “mixed” . The delegates immediately checked the parameter and changed it to UPPER case and then also to LOWER. Well, nothing changed 🙂 . See for yourself,

SQL> set sqlcase upper 
  
SQL> show sqlcase 

  
sqlcase UPPER 

  
SQL> select * from scott.dept; 

    DEPTNO DNAME          LOC 
  
---------- -------------- ------------- 

  
        10 ACCOUNTING     NEW YORK 

  
        20 RESEARCH       DALLAS 

  
        30 SALES          CHICAGO 

  
        40 OPERATIONS     BOSTON 

SQL> set sqlcase lower 
  
SQL> select * from scott.dept; 

    DEPTNO DNAME          LOC 
  
---------- -------------- ------------- 

  
        10 ACCOUNTING     NEW YORK 

  
        20 RESEARCH       DALLAS 

  
        30 SALES          CHICAGO 

  
        40 OPERATIONS     BOSTON

So what does this setting did actually? Well since its a client tool, the change of the case send the query , including everything with it, like literals and all, in the proposed case. But how do we go and check and confirm it?

Since sql*plus is a client tool, the best way to check such thing was to enable client side network tracing. For this, we entered the following entries into the sqlnet.ora

TRACE_LEVEL_CLIENT= support : This parameter would enable the tracing for the OCI client connections and the level is set to gather the maximum information. This is not something which should be used in a normal environment/condition because the information collected would be huge!

TRACE_DIRECTORY_CLIENT=/tmp:  This parameter would set the directory where the trace file should go.

DIAG_ADR_ENABLED=OFF : This parameter would not let the tracing information go to the 11g specific Automatic Diagnostic Repository(ADR) .

So after setting all these parameters, this is how the sqlnet.ora would look.

TRACE_LEVEL_CLIENT=support 

TRACE_DIRECTORY_CLIENT=/tmp 

DIAG_ADR_ENABLED=OFF 

 

After that, we connected from sql*plus with the user Scott again and issued the same command, select * from dept , first in lower case and then in upper case. Doing this would create immediately a file in the /tmp with a name like cli_25111.trc. Since we had used the tracing level to support, the file is very huge. So I am going to chop off lots of content from it. Here are the sections which actually we were looking for in the file,

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 
  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 12 73 65  |......se| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 6C 65 63 74 20 2A 20 66  |lect.*.f| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 72 6F 6D 20 64 65 70 74  |rom.dept| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 01 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 01 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00              |....    | 

  
[29-JUN-2010 17:40:57:613] nsbasic_bsd: exit (0) 

  
[29-JUN-2010 17:40:57:613] nsbasic_brc: entry: oln/tot=0 

  
[29-JUN-2010 17:40:57:624] nsbasic_brc: type=6, plen=436 

  
[29-JUN-2010 17:40:57:624] nsbasic_brc: what=1, tot =436

<output trimmed>

[29-JUN-2010 17:41:06:731] nsbasic_bsd: FE FF FF FF FE FF FF FF  |........| 
  
[29-JUN-2010 17:41:06:731] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 12 53 45 4C  |.....SEL| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 45 43 54 20 2A 20 46 52  |ECT.*.FR| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 4F 4D 20 44 45 50 54 01  |OM.DEPT.| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 01 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00                 |...     | 

  
[29-JUN-2010 17:41:06:732] nsbasic_bsd: exit (0) 

  
[29-JUN-2010 17:41:06:732] nsbasic_brc: entry: oln/tot=0 

  
[29-JUN-2010 17:41:06:742] nsbasic_brc: type=6, plen=436 

  
[29-JUN-2010 17:41:06:742] nsbasic_brc: what=1, tot =436

So we can see that the case used by both the queries from the sql*plus tool is changed. Nothing really great? Well did I say that it would be 😉 ? It was a just something which came across and was interesting so thought to share it!