we are using TOAD 13.0.0.80 for our development. In some or our packages we use N-quoted literals. TOAD however, thinks it’s cute to replace them with its U-quoted counterpart. We have a mechanism in place that generates code scripts and that doesn’t really like this, escpecially the double backslash that appears in some code lines. But the real issue for me personally is that TOAD single-handedly decides to change code I wrote. It should not do that.
Example.
Original code:
CREATE OR REPLACE PROCEDURE mhere2_test
IS
myvar1 NVARCHAR2 (50);
myvar2 NVARCHAR2 (50);
BEGIN
myvar1 := N'\n';
myvar2 := N'Délégé';
END;
/
TOAD’s code:
CREATE OR REPLACE PROCEDURE mhere2_test
IS
myvar1 NVARCHAR2 (50);
myvar2 NVARCHAR2 (50);
BEGIN
myvar1 := U'\\n';
myvar2 := U'D\00E9l\00E9g\00E9';
END;
/
Is this a setting somewhere, or how can we avoid this?
Database NLS_LANG = american_america.al32utf8
Client NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
Since I don’t have privileges on my pc (I have to go through a request procedure) I rather see as little changes on my client as possible.
the Spooled SQL is before it ends up in the database, right? I don't know how TOAD sends the command. If it is OCI, it might be the cause.
From The Oracle Database Globalization Support Guide:
When NCHAR literal replacement is enabled, OCIStmtPrepare and OCIStmtPrepare2 transform N' literals with U' literals in the SQL text and store the resulting SQL text in the statement handle. Thus, if an application uses OCI_ATTR_STATEMENT to retrieve the SQL text from the OCI statement handle, the SQL text returns U' instead of N' as specified in the original text.
Yes Spool SQL is before the command goes to the database. And Yes Toad uses the OCI.
It’s not something we intentionally changed - the OCI connectivitity stuff is a 3rd party component - but it doesn’t translate in Toad 13.1 or 13.2 (beta).
This rings a bell and I was able to locate email from the support staff for the component John references. They introduced support for that OCI option, defaulting it to True. This is how Toad 13 has it in place. We’ve set the option for the nchar replacement to False in 13.1.
Thanks for the details Mike. Maarten, as a workaround, if you can’t get a newer version of Toad right away, use the “execute via SQL Plus” option in the editor to create/replace your procedures and then they’ll keep the N.
Thanks for the clarification Michael. It is a bit what I was suspecting but I didn’t think that Toad would be using third party components for its connections.
John,
The “execute via SQL*Plus” appears to be working. Thanks for that!
Unfortunately, we have a rather painstaking and lengthy process when it comes to installing new software that is not in our software catalog. I think I wait for 13.2 to have that version approved. And since TOAD doesn’t offer an upgrade option in its installer I have to “sell” it as a replacement instead of an upgrade.
But again: thanks both of you (@JohnDorlon and @mstaszew) for looking into this. I really appreciate this.
This is actually a very common practice in the software industry. it frees us up to work on features that Toad users want rather than get bogged down in the details of the OCI. It's a reputable company that provides this, but they make mistakes just like any of us. We purchase the source code from them, so we aren't just using their code blindly.
Don’t get me wrong, I didn’t mean that this was bad or wrong in any way. I completely understand and I agree with you: there’s no need to reinvent the wheel. I was just surprised, given that TOAD is around for such a long time. I just guessed that it was a piece of TOAD’s internal code. It does makes sense indeed. We do the same thing.
