Column names in views with capital letters

Toad for Oracle
1

Hi

I have noticed something strange regarding view column names when looking through script.

I have created this view

CREATE OR REPLACE VIEW test_view
AS
    SELECT SYSDATE AS "A" FROM DUAL;

The script then looks like this when viewed through the Script tab of the view. Notice column A is not surrounded by double quotation marks

image

If I create a view like this

CREATE OR REPLACE VIEW test_view
AS
    SELECT SYSDATE AS "a" FROM DUAL;

Then the column name has double quotation marks around it.

image

Toad 13.1

2

And that’s exactly how it must be in order to keep the case-sensitivity you’ve requested. That’s an Oracle syntax rule that Toad is simply following. You will see the same (but unformatted) output running this SQL:

SELECT DBMS_METADATA.get_ddl(‘VIEW’,‘TEST_VIEW’,‘YOUR_SCHEMA_NAME_HERE’)
FROM DUAL;

…obviously substituting your schema name in place of YOUR_SCHEMA_NAME_HERE.

Hope that helps!
Rich

1 Like
3

The first example shows that it doesn’t keep the case-sensitivity. When I describe the view and look under the Script tab it shows this. Column A is not surrounded by quotes as expected.

image

4

It actually does. Unquoted object names will become upper-case. Only quoted object names retain the case-sensitivity. See: Database Object Names and Qualifiers especially point number 9, where it says:

Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted identifiers are case sensitive.

Oracle's DBMS_METADATA.get_ddl function returns similar SQL to Toad, except that the former always quotes object names. Both sets of SQLs are functionally equivalent.

Does that help clear things up?

Rich

5

As luck would have it, there’s an option in Toad to always quote the object names! Under Options->Oracle->General, the lower left option of “Double Quote Object Names” will do just that for your example, when set to “All”.

Is this what you’re looking for?

Rich

6

What I find puzzling is that in one case the view with the uppercase A as quoted ie. “A”, the script shows it as not quoted. In the other case where the lowercase a is quoted ie. “a” the scripts it as quoted.

Why is there a difference? Shouldn’t the a’s in both cases be quoted?

7

And that option I mentioned does just that. Does that not meet your needs?

Rich

8

It’s because when you create an object in Oracle and don’t specify quotes, Oracle uppercases it. If you do specify quotes, Oracle preserves the case inside the quotes.

So A=“A”.
a becomes A
“a” stays as “a”.

This just just how Oracle works.

And since most people would rather not see the quotes when they are not needed, Toad leaves them off when they are not needed. But sometimes (in the case of view source code), Oracle has already stored it with quotes (needed or not). We don’t remove those because they are buried in source code.

Try “Select * from user_views”, and find a view in there that you created as “Select * from some_table”. You’ll see that Oracle has listed and quoted all of the column names, weather they need quotes or not. But the top part of the view definition, where the column names are listed inside the parens…Toad has to put that together, So we leave off the quotes if they are not needed. And if that bugs you, you can turn them on with the option that Rich mentioned.

image

9

What I tried to show in my examples is

“A” becomes A
“a” becomes “a”

I apologize if I fail to see the logic in it.

10

We don’t add quotes around “A” because they are not necessary and people get annoyed by seeing quotes all over the place when they are not necessary.

We always add quotes around “a” because they are necessary.

Sometimes Oracle adds them for us in source code even when they are not necessary.

1 Like
11

As I had mentioned, change the Double Quote Object Names option in Toad to All. Then:

“A” becomes “A”
“a” becomes “a”