How to Create a Table in Oracle with name [email protected]
I tried doing CREATE TABLE [email protected](..)
but this gives me error so i was looking for some other way of doing it
+3
A:
I think is a not good way to place @. charactes in name of a table.
I think is a not good way to create each table per one email user. Propably you have big database design problem and you trying to find solution where it not exists.
Think about database emails, table users there each user have unique ID and username and domain in other column. On ID column you have create index, then you have some thing to find and add relationship of user instance in other tables.
Svisstack
2010-09-10 23:26:42
+1
A:
To use non standard characters in identifiers you can delimit with double quotes.
CREATE TABLE "[email protected]"(a int);
NB: Some of the discussion on this question reminded me of a simple talk article. I just tested and this is possible in Oracle as well.
CREATE TABLE "╚╦╩╗" ( "└┬┴┐" nvarchar2(10));
INSERT INTO "╚╦╩╗" VALUES ( '└┬┴┐' );
SELECT * FROM "╚╦╩╗";
Note I am not suggesting that anybody actually does this.
Martin Smith
2010-09-10 23:27:45
you actually are advising him to do this? you should be ashamed that you have 19K...
RobertPitt
2010-09-10 23:30:36
@Robert - I'm not advising him either way. We know nothing about the motivation for the question and the other answers already had the admonitions angle covered.
Martin Smith
2010-09-10 23:31:45
Robert, what difference does it make, he needs a solution, I'm tired of people here with the response of "Why would you do that". You don't know why he is doing this, or what he is trying to accomplish in the big picture, just give him a hand instead of being a nay-sayer.
Joseph Silvashy
2010-09-10 23:32:50
Thanks Martin really appreciate it.
Mohit Bansal
2010-09-10 23:37:25
@Joseph: Some people think that shooting themselves or others is a solution to their problems, but that doesn't mean it's wrong to advise against it. Sometimes you just need to be told when you're being a dumb.ss.
duffymo
2010-09-10 23:54:39
@Joseph As Martin said, we don't know the motivation for the question, and that means a possible motivation for naming a table '[email protected]' is "unaware of the consequences". I'm not presuming that's the case, but nay-saying serves an important purpose when it is. To a newbie, there is value in a chorus of *"I wouldn't do that if I were you!"* :)
djacobson
2010-09-10 23:57:48
@duffymo, @djacobson, Ok fair points, I agree with you guys. I do however remember working for a huge corporation and having to solve ridiculous problems that only exist because the bureaucracy around me. Nonetheless, you guys are right, but at the end of the day it wouldn't have bugged me if he had offer a solution along with the criticism.
Joseph Silvashy
2010-09-11 19:50:42
+4
A:
Either:
Put double-quotes around your table name:
CREATE TABLE "[email protected]" ...Don't put special characters in your table names.
Future users of your database will thank you profusely if you choose option 2.
CanSpice
2010-09-10 23:27:59
He asked the question. I answered the question. That's what Stack Overflow is for. I also gave him advice that doesn't really answer his question but makes for a better answer, as it speaks to best practices and avoids problems in the future.
CanSpice
2010-09-10 23:31:05
We believe finding the **right** answer to your programming questions should be as easy as falling into the pit of success... you may technically be correct but we all know that this is a bad idea.
RobertPitt
2010-09-10 23:35:12
@Robert, don't forget that SO is used by others with similar problems looking for solutions. I think there's a consensus here that this is a bad idea *in this case*. That said, perhaps someone else would have a legitimate need to create a table with a special character in it's name? These answers would provide the solution, along with providing the advice to ask why such a solution is needed.
DCookie
2010-09-12 13:21:39
+2
A:
From http://ora-903.ora-code.com/:
ORA-00903: invalid table name
Cause: A table or cluster name is invalid or does not exist. This message is also issued if an invalid cluster name or no cluster name is specified in an ALTER CLUSTER or DROP CLUSTER statement.
Action: Check spelling. A valid table name or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #. The name must be less than or equal to 30 characters and cannot be a reserved word.
Martin and CanSpice have pointed out that it's technically possible, but, yeah... you're asking for plenty of trouble with this approach. And why (on earth) would you name a table after an email address in the first place? I'd be fascinated to know.
djacobson
2010-09-10 23:31:26
A:
What an odd name for a table.
I don't have access to an Oracle instance to find out which error you are getting, but it will refer to the third point below:
- The table name must begin with a letter.
- The table name can not be longer than 30 characters.
- The table name must be made up of alphanumeric characters or the following special characters: $, _, and #.
- The table name can not be a reserved word.
That email address looks like it should be in the table, as data.
Have a think about what you are going to store in the table and that should give you a more appropriate table name.
Johnsyweb
2010-09-10 23:35:00
A:
If you must, you could always name the table "abc_at_gmail.com" and get on with it.
I believe the at-sign means "link to another database" in Oracle. You're out of luck in preserving the e-mail address.
If you really need multiple e-mail addresses, why wouldn't you create a users table and have multiple rows, one per e-mail address, and make e-mail address a unique index on the table? Your design idea must be breaking some normalization rule.
duffymo
2010-09-10 23:57:10