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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<p>SQL> set sqlcase upper <br />SQL> show sqlcase <br />sqlcase UPPER <br />SQL> select * from scott.dept; </p> <p>    DEPTNO DNAME          LOC <br />---------- -------------- ------------- <br />        10 ACCOUNTING     NEW YORK <br />        20 RESEARCH       DALLAS <br />        30 SALES          CHICAGO <br />        40 OPERATIONS     BOSTON </p> <p>SQL> set sqlcase lower <br />SQL> select * from scott.dept; </p> <p>    DEPTNO DNAME          LOC <br />---------- -------------- ------------- <br />        10 ACCOUNTING     NEW YORK <br />        20 RESEARCH       DALLAS <br />        30 SALES          CHICAGO <br />        40 OPERATIONS     BOSTON</p> |
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.
|
1 2 3 4 5 6 7 |
<p>TRACE_LEVEL_CLIENT=support </p> <p>TRACE_DIRECTORY_CLIENT=/tmp </p> <p>DIAG_ADR_ENABLED=OFF </p> <p> </p> |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
<p>[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 12 73 65  |......se| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 6C 65 63 74 20 2A 20 66  |lect.*.f| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 72 6F 6D 20 64 65 70 74  |rom.dept| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 01 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 01 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00              |....    | <br />[29-JUN-2010 17:40:57:613] nsbasic_bsd: exit (0) <br />[29-JUN-2010 17:40:57:613] nsbasic_brc: entry: oln/tot=0 <br />[29-JUN-2010 17:40:57:624] nsbasic_brc: type=6, plen=436 <br />[29-JUN-2010 17:40:57:624] nsbasic_brc: what=1, tot =436</p> <p><output trimmed></p> <p>[29-JUN-2010 17:41:06:731] nsbasic_bsd: FE FF FF FF FE FF FF FF  |........| <br />[29-JUN-2010 17:41:06:731] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 12 53 45 4C  |.....SEL| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 45 43 54 20 2A 20 46 52  |ECT.*.FR| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 4F 4D 20 44 45 50 54 01  |OM.DEPT.| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 01 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00                 |...     | <br />[29-JUN-2010 17:41:06:732] nsbasic_bsd: exit (0) <br />[29-JUN-2010 17:41:06:732] nsbasic_brc: entry: oln/tot=0 <br />[29-JUN-2010 17:41:06:742] nsbasic_brc: type=6, plen=436 <br />[29-JUN-2010 17:41:06:742] nsbasic_brc: what=1, tot =436</p> |
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!
It also affects litteral and the default value is MIXED.
SQL> sho sqlcase;
sqlcase MIXED
SQL> select empno from lsc_emp where ename='SCOTT';
EMPNO
----------
7788
SQL> set sqlcase upper;
SQL> select empno from lsc_emp where ename='SCOTT';
EMPNO
----------
7788
SQL> set sqlcase lower;
SQL> select empno from lsc_emp where ename='SCOTT';
no rows selected
its used for Where clause (where ename=’smith’)
treate literal as define by set sqlcase command
MIXED,UPPER,LOWER
Default is MIXED
Muhammad Adnan Rafi
+92-321-9201534
Orasoft Training Institute.
dbaadnanrafi.blogspot.com
facebook.com/m.adnanrafi