Re: The reserved word "ACCESS" in a field list of a PL SQL Query

Lee wrote:

> We have a field in our tables call "ACCESS", but we are unable to
> reference this field in a query becuase it appears to be a reserved
> word in Oracle (we are running Oracle 10g on Windows 2k).

Use of terms such as 'field' when you mean 'column' imply that you have very limited experience with serious databases such as Oracle.

Before you get too far in the hole, please consider reading some manuals. All of Oracle manuals are available at http://docs.oracle.com, and the ones you need to look at start with the 'Concepts' manual.

Please take the time to skim the manual - in the long run it'll help you.

>
> A query like the following results in a "Ora-00936:Missing Expression"
> error message:
> SELECT Access FROM sys.MyTable.
>
> The Field "Access" is a valid field within the table "MyTable".

As long as you keep a column named 'Access', you will experience a variety of issues that will lead to frustration.

The short-term resolution is to put the term - in the proper case - inside double-quotes.

        SELECT "ACCESS" FROM sys.MyTable

You are violating every rule in the Oracle playbook by creatng that table under userid SYS. SYS is reserved for the internal structure of the database, and you risk damaging the structure every time you create or modify tables in SYS.

You should learn about SCHEMAs in the documentation and create a user, such as MYAPP, to own the table.

>
> Additionally, our application dynamically builds this query so any
> soluiton that includes "Aliasing" this field probably will not work.
>

This is also potentially a serious mistake. Unless crafted properly, dynamically built queries frequently lead to problems in stability and/or performance. To learn more about this, you would want to spend some time reading books like "Effective Oracle by Design"

> Is there anyway around quering this field in the table without
> re-naming the field in the table?
>
> Thanks for any info that can be provided.
> Lee

PLEASE - find a local Oracle User Group and ask them to help.

/Hans Received on Wed Sep 08 2004 - 21:58:14 CDT