Retrieve case sensitive tables

#1

Hello,

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Thank-you.
Abbeyro

#2

On Jan 7, 12:27*pm, abbe... (AT) gmail (DOT) com wrote:

Quote:

Hello,

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Thank-you.
Abbeyro

I'm pretty sure your table names are all upper case. If the table
names were mixed case all_tables would have the names in mixed case.
You can use the command: CREATE TABLE Tab_1A but unless you put double
quotes around the table name the table will be created with the name
all upper case (TAB_1A). If you want the table name to be mixed case
then you must use double quotes around the table name: CREATE TABLE
"Tab_1A"

SQL> create table Tab_1A (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
TAB_1A

SQL> create table "Tab_1A" (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
Tab_1A
TAB_1A

#3

On Jan 7, 12:27*pm, abbe... (AT) gmail (DOT) com wrote:

Quote:

Hello,

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Thank-you.
Abbeyro

I'm pretty sure your table names are all upper case. If the table
names were mixed case all_tables would have the names in mixed case.
You can use the command: CREATE TABLE Tab_1A but unless you put double
quotes around the table name the table will be created with the name
all upper case (TAB_1A). If you want the table name to be mixed case
then you must use double quotes around the table name: CREATE TABLE
"Tab_1A"

SQL> create table Tab_1A (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
TAB_1A

SQL> create table "Tab_1A" (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
Tab_1A
TAB_1A

#4

On Jan 7, 12:27*pm, abbe... (AT) gmail (DOT) com wrote:

Quote:

Hello,

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Thank-you.
Abbeyro

I'm pretty sure your table names are all upper case. If the table
names were mixed case all_tables would have the names in mixed case.
You can use the command: CREATE TABLE Tab_1A but unless you put double
quotes around the table name the table will be created with the name
all upper case (TAB_1A). If you want the table name to be mixed case
then you must use double quotes around the table name: CREATE TABLE
"Tab_1A"

SQL> create table Tab_1A (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
TAB_1A

SQL> create table "Tab_1A" (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
Tab_1A
TAB_1A

#5

On Jan 7, 12:27*pm, abbe... (AT) gmail (DOT) com wrote:

Quote:

Hello,

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Thank-you.
Abbeyro

I'm pretty sure your table names are all upper case. If the table
names were mixed case all_tables would have the names in mixed case.
You can use the command: CREATE TABLE Tab_1A but unless you put double
quotes around the table name the table will be created with the name
all upper case (TAB_1A). If you want the table name to be mixed case
then you must use double quotes around the table name: CREATE TABLE
"Tab_1A"

SQL> create table Tab_1A (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
TAB_1A

SQL> create table "Tab_1A" (x varchar2(1));

Table created.

SQL> select table_name from all_tables where table_name like '%1A';

TABLE_NAME
------------------------------
Tab_1A
TAB_1A

#6

On 07.01.2008 18:27, abbeyro (AT) gmail (DOT) com wrote:

Quote:

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Adding to Ken's reply: there is also a slight chance that your .NET
provider changes case, e.g. if you use some metadata facility. You
could try to directly query USER_TABLES to see the table names.

Kind regards

robert

#7

On 07.01.2008 18:27, abbeyro (AT) gmail (DOT) com wrote:

Quote:

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Adding to Ken's reply: there is also a slight chance that your .NET
provider changes case, e.g. if you use some metadata facility. You
could try to directly query USER_TABLES to see the table names.

Kind regards

robert

#8

On 07.01.2008 18:27, abbeyro (AT) gmail (DOT) com wrote:

Quote:

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Adding to Ken's reply: there is also a slight chance that your .NET
provider changes case, e.g. if you use some metadata facility. You
could try to directly query USER_TABLES to see the table names.

Kind regards

robert

#9

On 07.01.2008 18:27, abbeyro (AT) gmail (DOT) com wrote:

Quote:

I'm trying to retrieve a list of tables from all_tables in a given
Oracle db. I'm using the .NET provider (by Microsoft) and I always
obtain the results in uppercase. The problem is that some tables where
defined in "Mixed/Case" so when I retrieve the list I need to obtain
the exact case used when they were created. Do you know how can I
achieve this?

Adding to Ken's reply: there is also a slight chance that your .NET
provider changes case, e.g. if you use some metadata facility. You
could try to directly query USER_TABLES to see the table names.

Kind regards

robert

#10

On Jan 7, 1:26 pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

Adding to Ken's reply: there is also a slight chance that your .NET
provider changes case, e.g. if you use some metadata facility. You
could try to directly query USER_TABLES to see the table names.

Hi Robert, I'm using ALL_TABLES but the .NET provider still changes
the case.

Created as: "my/table"
From Oracle's shell: my/table
From .NET: MY/TABLE

Is there any way to specify that this shouldn't be the correct
behavior?

Thanks.