0

I successfuly migrate mysql to oracle. But the only problem is case sensitive on table name and fieldname.Some pages in web said go to tools and option in sql developer and tick the ansi but i cannot find it. On oracle website forum said it part of migration . Anybody had new version of sql developer and migrate from mysql ?

E.g

calendarColor become CALENDARCOLOR

Hafizan Aziz
  • 269
  • 1
  • 3
  • 13
  • Please show us the **real** SQL statements that you are running. Ideally show use the CREATE TABLE statement as well (just copy that CREATE table from SQL Developer it is displayed there) –  Sep 07 '11 at 06:44
  • `PROMPT Creating Table calendarcolor ... CREATE TABLE calendarcolor ( calendarColorId NUMBER(10,0) NOT NULL ); PROMPT Creating Primary Key Constraint PRIMARY_3 on table calendarcolor ... ALTER TABLE calendarcolor ADD CONSTRAINT PRIMARY_3 PRIMARY KEY ( calendarColorId ) ENABLE ;` – Hafizan Aziz Sep 07 '11 at 07:15
  • seem generate code is not uppercase weird.. have to check back on the generated code – Hafizan Aziz Sep 07 '11 at 07:16
  • If no quotes are used in the CREATE TABLE, then the table is stored in uppercase internally and you don't need to use quotes anywhere –  Sep 07 '11 at 08:06
  • hope there a way to escape it or quote.. Is the best pratice.. since mysql using ` and mssql using [] and oracle using " for those weird or special name – Hafizan Aziz Sep 07 '11 at 08:19
  • 1
    Simply don't use any quoting ever. –  Sep 07 '11 at 08:28

2 Answers2

1

I really don't see how this is a problem. Since Oracle's objects are case-insensitive by default, you can continue to query them using SELECT * FROM calendarColor.

If you need them to be case sensitive, use quotes, like:

CREATE TABLE "calendarColor" ( ... );

SELECT * FROM TABLE "calendarColor";

See also: Schema Object Names and Qualifiers

NullUserException
  • 83,810
  • 28
  • 209
  • 234
  • [quote]Error starting at line 1 in command: select * from "calendarColor" Error at Command Line:1 Column:14 Error report: SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause: *Action:[/quote] – Hafizan Aziz Sep 07 '11 at 06:10
  • If you created the table without using quotes (ie: case-insensitive), you *have* to refer to it without quotes (or if you do use quotes, use all uppercase letters). Also please don't use [BBCode](http://stackoverflow.com/questions/3788959/regex-to-split-bbcode-into-pieces/3792262#3792262) here. It's fugly and SO doesn't support it. – NullUserException Sep 07 '11 at 06:16
  • hmm.. i not generate the code. it generate by sql developer.. Hmm.. seem you don't used it..better not answer... – Hafizan Aziz Sep 07 '11 at 06:23
1

If the table was created using

CREATE TABLE calendarcolor ( calendarColorId NUMBER(10,0) NOT NULL );

then the table name is stored in uppercase internally. When you run a statement like this:

select * from "calendarColor"

then you are telling Oracle: The table name should be treated case-sensitive but as there is not table named calenderColor, only one named CALENDARCOLOR your statement fails.

The fix is very easy: remove the quotes and change the select to

select * from calendarColor