How can create case-insensitive database in oracle?

[Madjid Nasiri]

Hi,
I am basic in oracle. My Old programs write with Delphi and Databases:
Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can used
case-insensitive in oracle.

Thanks
Madjid Nasiri

[Guido Konsolke]

Re: How can create case-insensitive database in oracle?

"Madjid Nasiri" wrote...

Hi,
I am basic in oracle. My Old programs write with Delphi and

Databases:

Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can

used

case-insensitive in oracle.
>
Thanks
Madjid Nasiri

Hi Madjid,

I hope you are going to use a version >= 8.1.7 (9.2.0.4 would be
best).
Since you don't provide much infos to play around with, here are 2 two
possible meanings of your post:
1. You want to store data case insensitive: do an 'insert...
values(upper('A bcd'))...'.
This will put your data in upper case into the tables.
2. Your data will be mixed-case and you want to select them case
insensitive:
create 'function based indexes' on every column you want to use in
your where-clauses.
Then 'select ... where <column_name= 'ABCD'.

If my assumptions about your intention are wrong, please explain it a
little.

hth,
Guido

[Walt]

Re: How can create case-insensitive database in oracle?

Guido Konsolke wrote:

>
"Madjid Nasiri" wrote...

Hi,
I am basic in oracle. My Old programs write with Delphi and

Databases:

Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can

used

case-insensitive in oracle.

Thanks
Madjid Nasiri

>
Hi Madjid,
>
I hope you are going to use a version >= 8.1.7 (9.2.0.4 would be
best).
Since you don't provide much infos to play around with, here are 2 two
possible meanings of your post:
1. You want to store data case insensitive: do an 'insert...
values(upper('A bcd'))...'.
This will put your data in upper case into the tables.
2. Your data will be mixed-case and you want to select them case
insensitive:
create 'function based indexes' on every column you want to use in
your where-clauses.
Then 'select ... where <column_name= 'ABCD'.
>
If my assumptions about your intention are wrong, please explain it a
little.

Sounds to me like a third possibility:

3. He has a body of SQL that is case-insensitive and mistakenly
believes that Oracle SQL is case sensitive.

Majid,

Oracle SQL is case insensitive. The character data stored in Oracle is
case sensitive.

i.e. the following statements are identical:
SELECT * from FOO where COL = 'BAR';
select * From Foo WHERE col = 'BAR';

These two are not:
SELECT * from FOO where COL = 'BAR';
SELECT * from FOO where COL = 'Bar';

Oracle provides string functions UPPER() and LOWER() to deal with
case-sensitivity issues. There's also INITCAP() but it doesn't always
give you what you want.


--
//-Walt
//
// http://tinyurl.com/3xqyq

[Madjid Nasiri]

Re: How can create case-insensitive database in oracle?

Walt <walt@boatnerd. com.invalidwrot e in message news:<401A8A37. D644F684@boatne rd.com.invalid> ...

Guido Konsolke wrote:

"Madjid Nasiri" wrote...

Hi,
I am basic in oracle. My Old programs write with Delphi and

Databases:

Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can

used

case-insensitive in oracle.
>
Thanks
Madjid Nasiri

Hi Madjid,

I hope you are going to use a version >= 8.1.7 (9.2.0.4 would be
best).
Since you don't provide much infos to play around with, here are 2 two
possible meanings of your post:
1. You want to store data case insensitive: do an 'insert...
values(upper('A bcd'))...'.
This will put your data in upper case into the tables.
2. Your data will be mixed-case and you want to select them case
insensitive:
create 'function based indexes' on every column you want to use in
your where-clauses.
Then 'select ... where <column_name= 'ABCD'.

If my assumptions about your intention are wrong, please explain it a
little.

>
Sounds to me like a third possibility:
>
3. He has a body of SQL that is case-insensitive and mistakenly
believes that Oracle SQL is case sensitive.
>
Majid,
>
Oracle SQL is case insensitive. The character data stored in Oracle is
case sensitive.
>
i.e. the following statements are identical:
SELECT * from FOO where COL = 'BAR';
select * From Foo WHERE col = 'BAR';
>
These two are not:
SELECT * from FOO where COL = 'BAR';
SELECT * from FOO where COL = 'Bar';
>
Oracle provides string functions UPPER() and LOWER() to deal with
case-sensitivity issues. There's also INITCAP() but it doesn't always
give you what you want.

Hi All, and Thanks from Guido and Walt
I didn't problam with Data case-sensitive. My problam is in "Table
Name" and "Field Name" for example I like
select Table1.Code from Table1 ======== select TABLE1.CODE from TABLE1

Thanks for answers,
Madjid

[Jim Kennedy]

Re: How can create case-insensitive database in oracle?


"Madjid Nasiri" <nasiri@kawacom puter.comwrote in message
news:251d9fce.0 401310019.44c81 569@posting.goo gle.com...

Walt <walt@boatnerd. com.invalidwrot e in message

news:<401A8A37. D644F684@boatne rd.com.invalid> ...

Guido Konsolke wrote:

>
"Madjid Nasiri" wrote...
Hi,
I am basic in oracle. My Old programs write with Delphi and

Databases:

Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can

used

case-insensitive in oracle.

Thanks
Madjid Nasiri
>
Hi Madjid,
>
I hope you are going to use a version >= 8.1.7 (9.2.0.4 would be
best).
Since you don't provide much infos to play around with, here are 2 two
possible meanings of your post:
1. You want to store data case insensitive: do an 'insert...
values(upper('A bcd'))...'.
This will put your data in upper case into the tables.
2. Your data will be mixed-case and you want to select them case
insensitive:
create 'function based indexes' on every column you want to use in
your where-clauses.
Then 'select ... where <column_name= 'ABCD'.
>
If my assumptions about your intention are wrong, please explain it a
little.

Sounds to me like a third possibility:

3. He has a body of SQL that is case-insensitive and mistakenly
believes that Oracle SQL is case sensitive.

Majid,

Oracle SQL is case insensitive. The character data stored in Oracle is
case sensitive.

i.e. the following statements are identical:
SELECT * from FOO where COL = 'BAR';
select * From Foo WHERE col = 'BAR';

These two are not:
SELECT * from FOO where COL = 'BAR';
SELECT * from FOO where COL = 'Bar';

Oracle provides string functions UPPER() and LOWER() to deal with
case-sensitivity issues. There's also INITCAP() but it doesn't always
give you what you want.

>
Hi All, and Thanks from Guido and Walt
I didn't problam with Data case-sensitive. My problam is in "Table
Name" and "Field Name" for example I like
select Table1.Code from Table1 ======== select TABLE1.CODE from TABLE1
>
Thanks for answers,
Madjid

Don't do that. It is going to drive you nuts. Usually people get mixed
case table and column names when they export an Acess database or another
database via ODBC to Oracle. The tool will quote all the table and column
names and thus make them mixed case. Which means you have to type select
"myColumnOne"," myColumnTwo" from "myTable" where "myColumnOne"=5 ;
Jim

[s.kapitza]

Re: How can create case-insensitive database in oracle?

nasiri@kawacomp uter.com (Madjid Nasiri) wrote in message news:<251d9fce. 0401310019.44c8 1569@posting.go ogle.com>...

Walt <walt@boatnerd. com.invalidwrot e in message news:<401A8A37. D644F684@boatne rd.com.invalid> ...

Guido Konsolke wrote:

>
"Madjid Nasiri" wrote...
Hi,
I am basic in oracle. My Old programs write with Delphi and

Databases:

Access, Paradox, MySQL, Microsoft SQL.
I write my code (SQL code) case-insensitivae, but now i need use
oracle database. The Oracle by default is case-sensitive, How can

used

case-insensitive in oracle.

Thanks
Madjid Nasiri
>
Hi Madjid,
>
I hope you are going to use a version >= 8.1.7 (9.2.0.4 would be
best).
Since you don't provide much infos to play around with, here are 2 two
possible meanings of your post:
1. You want to store data case insensitive: do an 'insert...
values(upper('A bcd'))...'.
This will put your data in upper case into the tables.
2. Your data will be mixed-case and you want to select them case
insensitive:
create 'function based indexes' on every column you want to use in
your where-clauses.
Then 'select ... where <column_name= 'ABCD'.
>
If my assumptions about your intention are wrong, please explain it a
little.

Sounds to me like a third possibility:

3. He has a body of SQL that is case-insensitive and mistakenly
believes that Oracle SQL is case sensitive.

Majid,

Oracle SQL is case insensitive. The character data stored in Oracle is
case sensitive.

i.e. the following statements are identical:
SELECT * from FOO where COL = 'BAR';
select * From Foo WHERE col = 'BAR';

These two are not:
SELECT * from FOO where COL = 'BAR';
SELECT * from FOO where COL = 'Bar';

Oracle provides string functions UPPER() and LOWER() to deal with
case-sensitivity issues. There's also INITCAP() but it doesn't always
give you what you want.

>
Hi All, and Thanks from Guido and Walt
I didn't problam with Data case-sensitive. My problam is in "Table
Name" and "Field Name" for example I like
select Table1.Code from Table1 ======== select TABLE1.CODE from TABLE1
>
Thanks for answers,

Hi Madjid,

as long as you enclose your db-objects not with "<object>" you will
have no problems. If you enclose your objects, you have to enclose
your querys-objects also. for example :

############### ############### ############### ############### #####

SQLcreate table foo (blah number);

Table created.

SQLselect * from foo;

no rows selected

SQLselect * from "foo";
select * from "foo"
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQLcreate table "foo" (blah number);

Table created.

SQLselect * from foo;

no rows selected

SQLinsert into "foo" values (8);

1 row created.

SQLselect * from foo;

no rows selected

SQLselect * from "foo";

BLAH
----------
8

SQL>
############### ############### ############### ##########

regards

s.kapitza