0

Our schema was built by my predecessor with most column names and some table names having "" around them. I suppose it was an artifact of an import process. I rename the columns when I'm working on parts of the database, and plan to rename all the rest eventually. Meanwhile, I can see the column names in Toad, on the Script tab, where I can see the CREATE TABLE... code. But I don't see them when using a simple DESC command in SQL Plus. Is there a way to show the double-quotes around the column names and some table names in SQL Plus?

It matters because my colleague is old-school and doesn't have access to Toad. I had access to Toad, but lost it a while back and don't know when I will have it again. There's bureaucracy in the way. But that's another story. Thank you.

mempie
  • 11
  • 2
  • double quotes are to escape the column names, you don't need it, but if you have restrocted words or spaces in column names it will not give you an error. so kep the double quotes and use them around every column, it help others too – nbk Aug 24 '22 at 22:44
  • 1
    Tough! The double-quotes are a syntactical element, they are not part of the column names. You can use double-quotes to tell the parser that it's looking at an identifier (such as column name), so it won't misinterpret it; but there is nothing in the data catalog to specifically show the identifier was defined with double-quotes. If the identifier is a reserved keyword, or begins with a digit, or has spaces in it, or has lower-case letters, etc., you know it was; but you would have to "see" that for yourself. So - how much effort do you need to put into this? –  Aug 25 '22 at 05:33
  • 1
    Not related to your problem, just saying: if you don't have TOAD any more, what do you use, then? SQL Developer (https://www.oracle.com/database/sqldeveloper/technologies/download/) is a free GUI tool so - maybe you could use it, instead. – Littlefoot Aug 25 '22 at 05:52
  • mathguy explains it well. Please see this accepted answer: https://stackoverflow.com/a/48090970/2270762. You can loop through your user_tab_cols with PL/SQL and check with `rec.column_name <> UPPER(rec.column_name)`, `DBMS_ASSERT.SIMPLE_SQL_NAME` and `V$RESERVED_WORDS` whether the column is a column that needs quotes.. – Thorsten Kettner Aug 25 '22 at 06:54
  • These column names actually have double-quotes in the names. I can see them in Toad, in the Script tab, but not in the Fields tab. I can also get rid of them with this command: ALTER TABLE myschema.mytable RENAME COLUMN "My_Column_Name" TO My_Column_Name;. I have done that for many columns, and need to see the others. I hope and expect to get back my use of Toad eventually. The government security rules that are interfering with my access also got in the way when they tried to set up SQL Developer. – mempie Aug 29 '22 at 16:59
  • Part of me thinks that Toad is a crutch, and that I would have learned more Oracle by now if I had been forced to do everything in SQLPlus. – mempie Aug 29 '22 at 16:59
  • Most of my work is in the Access front end. I can use that to examine the linked tables. The ODBC driver seems to remove these "" so that Access doesn't see them. In Access I only deal with them if I'm writing a pass-through query. – mempie Aug 29 '22 at 16:59

0 Answers0