Auto-complete table and field names uppercase

Hi,

It's clear enough, how to automatically set the keywords to upper-case, if needed.

But is there a way to generate tables and fields names to uppercase? So that, if I type in a schema name and a '.', it will pop up and paste UPPERCASE table names, into UltraEdit or PlSql Developer?

I would imagine if you changed the object + typed synonym query under db options->db queries and find the right one (oracle in your case?) and use the UPPER() function to return o.object_name in uppercase might do it.

When I do this on the object+typed synonym query for MSSQL it does what you want (uppercase table names).

Likewise you'd have to modify the queries that return columns. (columns query)

I see you're becoming advanced user, Mindflux. Nice :)

	gemisigo wrote:
	I see you're becoming advanced user, Mindflux. Nice :)

I like to think I catch on quick.

Been searching around through the Options, and cannot find out where to type in an Upper function. If I select Db Options/DB Queries/Query "Synonyms", it says

Synonyms (Oracle)

, and doesn't allow me to change this string.

I'm probably not understanding precisely where the UPPER entry should be made.

Mindflux wrote:

I would imagine if you changed the object + typed synonym query under db options->db queries and find the right one (oracle in your case?) and use the UPPER() function to return o.object_name in uppercase might do it.

When I do this on the object+typed synonym query for MSSQL it does what you want (uppercase table names).

Likewise you'd have to modify the queries that return columns. (columns query)

Mindflux was referring to DB Options > DB Queries > Objects (Oracle) + Typed Synonyms, which is

Code:

select
o.object_name,
decode(o.object_type, 'SYNONYM', decode(o.owner,'PUBLIC', '|', '^') ||
   decode(o2.object_type, 'PACKAGE', 'C', 'SEQUENCE', 'I', 'TYPE', 'K', substr(o2.object_type,1,1)),
   substr(o.object_type,1,2)) object_type,
o.created,
o.last_ddl_time
from
all_objects o
left join all_synonyms s ON s.owner = o.owner AND s.synonym_name = o.object_name
left join all_objects o2 ON o2.owner = s.table_owner AND o2.object_name = s.table_name
where
   o.owner in (:SCHEMA_NAME,'PUBLIC')
and o.object_type in (
'TABLE','VIEW',
'PROCEDURE','FUNCTION','PACKAGE','TYPE',
'MATERIALIZED VIEW','MATERIALIZED VIEW LOG',
'SEQUENCE', 'SYNONYM'
)
and (o2.object_type is null or o2.object_type in (
'TABLE','VIEW',
'PROCEDURE','FUNCTION','PACKAGE','TYPE',
'MATERIALIZED VIEW','MATERIALIZED VIEW LOG',
'SEQUENCE', 'SYNONYM'))
and o.object_name not like 'bin$%'
and o.object_name not like 'BIN$%'
and o.object_name not like '%/%'
order by o.object_name

for Oracle on my machine. And you don't want to change the string (which is a plain name for the query) but the Query Text.

You should modify it to convert retrieved object name to uppercase, that is, to:

Code:

select
upper(o.object_name),
decode(o.object_type, 'SYNONYM', decode(o.owner,'PUBLIC', '|', '^') ||
   decode(o2.object_type, 'PACKAGE', 'C', 'SEQUENCE', 'I', 'TYPE', 'K', substr(o2.object_type,1,1)),
   substr(o.object_type,1,2)) object_type,
o.created,
o.last_ddl_time
from
all_objects o
left join all_synonyms s ON s.owner = o.owner AND s.synonym_name = o.object_name
left join all_objects o2 ON o2.owner = s.table_owner AND o2.object_name = s.table_name
where
   o.owner in (:SCHEMA_NAME,'PUBLIC')
and o.object_type in (
'TABLE','VIEW',
'PROCEDURE','FUNCTION','PACKAGE','TYPE',
'MATERIALIZED VIEW','MATERIALIZED VIEW LOG',
'SEQUENCE', 'SYNONYM'
)
and (o2.object_type is null or o2.object_type in (
'TABLE','VIEW',
'PROCEDURE','FUNCTION','PACKAGE','TYPE',
'MATERIALIZED VIEW','MATERIALIZED VIEW LOG',
'SEQUENCE', 'SYNONYM'))
and o.object_name not like 'bin$%'
and o.object_name not like 'BIN$%'
and o.object_name not like '%/%'
order by o.object_name

if I recall correctly. You should apply similar modifications to DB Options > Columns (Oracle) and/or Columns (Oracle) + Keys. This one is for Oracle, you'll have to modify the DB Query that you need, of course, but the principle will be the same.

Ok, thanks gem and Mindflux.

Never did get this to work, by the way.

For example, in Columns (Oracle), this

select
upper(column_name),
data_type....

still produces lower case column names, in UltraEdit/UEStudio.

To test that the changes are being read, this

select
'what the',
upper(column_name),
data_type....

generates the expected 'what the' in the picklist. But the column list is all lower case.

Ultraedit edit has it's own 'convert highlighted string to uppercase', so that can be used in the meantime.

Notepad, by the way, doesn't seem to get supported at all, even though it's checked off in Options. No picklist, no sql assistance support at all, even if a hot key is pressed.

Notepad++ does work. But, same problem as with Ultraedit. No uppercase field names are possible. Similarly, for table names.

I'm using Sql Assistant 6.5.278 pro, Windows 7, Oracle 11g.

Please change "Columns (Oracle) + Keys" query, "Columns (Oracle)" is a spare query, which is used only if you configure SQL Assistant not to use the Columns+Key query, in case you experience significant performance issues with querying Oracle system catalog tables and want to minimize the number of catalog tables involved.

Hope this helps.

Sorry, forgot the notepad reference. IMost likely it doesn't work because you start 64-bit version of notepad. SQL Assistant doesn't currently support 64-bit applications. Almost all development tools on the market are 32-bit tools because of the need to support different Windows flavors and versions. 32-bit apps run just fine on 64-bit systems, but not the other way around.

	SysOp wrote:
	Please change "Columns (Oracle) + Keys" query, "Columns (Oracle)" is a spare query, which is used only if you configure SQL Assistant not to use the Columns+Key query, in case you experience significant performance issues with querying Oracle system catalog tables and want to minimize the number of catalog tables involved. Hope this helps.

They are both modified, the keys version and the bare version. However, it is clear that the bare version is being used, even though the check box is unchecked.

If I throw 'WHAT THE' into the + keys query, and 'what the' into the spare query, it's 'what the' that is returned.

Where do you select which type of query should be used?

	Quote:
	Where do you select which type of query should be used?

"DB Options" tab -> select "Oracle" as the SQL Assistance type -> Expand "DB Queries" section on the right side -> See or change Query "Columns" option.

SysOp wrote:

	Quote:
	Where do you select which type of query should be used?

"DB Options" tab -> select "Oracle" as the SQL Assistance type -> Expand "DB Queries" section on the right side -> See or change Query "Columns" option.

I think I'm asking the question wrong.

I've already been in DB/Options/DB Queries, and changed both Columns (Oracle) (I call it the bare version), and Columns (Oracle)+Keys.

Right now, the Query Text in

Columns (Oracle)

is unchecked. It has no extra 'what the' string, just starts with

select
upper(column_name),
data_type
|| decode(data_type,...

The Query Text in

Columns (Oracle) + Keys

is checked. It starts with

select
'WHAT THE',
UPPER(column_name),
data_type
|| decode(data_type,...

- but, when UltraEdit is open, and a SELECT takes place, the columns show up in lowercase, without any 'what the', which seems to indicate that it's using the bare Columns (Oracle), even though it's unchecked.

If I change the Columns Oracle to

select
'WHO DAT?',
upper(column_name),
data_type

, even though it's not checked, a LOWERCASE

'who dat'

is what appears. NOT the uppercase version which is defined in Columns Oracle; and NOT the 'WHAT THE' version that's defined in Columns Oracle + keys.

I don't know how else to describe this, without print screens. Why is SQL Assistant using a choice, which is unchecked? And why does it keep converting field references to lowercase?

Is it possible for someone, who has access to Ultra Edit or UE Studio and Oracle 11g, to actually try and replicate this?

Instead of check boxes, please the drop down lists tor changing which query to use. The check boxes there are for a different purpose.

	SysOp wrote:
	Instead of check boxes, please the drop down lists tor changing which query to use. The check boxes there are for a different purpose.

Sysop,

Yes, that makes sense, that's what I was originally looking for. But I"m completely lost here. What drop down list, are you talking about?

Are you talking about the Query Type drop down

DB Type
Minimum Version

Query Type <----> THIS ONE?

Query Text

Sorry to be so dense, and yes I feel like an idiot, but honestly can't locate any place where you can specifically select the DB Query option that should be used.

[/img]