Home » SQL & PL/SQL » SQL & PL/SQL » CREATE TABLE throws ORA-00972 Identifier is too long (10G)
CREATE TABLE throws ORA-00972 Identifier is too long [message #291939] Mon, 07 January 2008 05:12 Go to next message
stevo
Messages: 3
Registered: January 2008
Junior Member
following SQL throwing: ORA-00972 "identifier is too long"

Executing (Error)
Messages
Error 0xc002f210: Preparation SQL Task: Executing the query "CREATE TABLE "PSR003" (
"PSR_number" NVARCHAR2(255) NOT NULL,
"Date_Opened" DATE NOT NULL,
"Project_Title" NVARCHAR2(255),
"Project_Lead" NVARCHAR2(255),
"Project_Lead_Alternate" NVARCHAR2(255),
"PSR_Assignee" NVARCHAR2(255),
"Status" NVARCHAR2(255),
"FlowStatus" NVARCHAR2(255) NOT NULL,
"Date_sent_for_signature" DATE,
"Date_completed" DATE,
"Review_Days_Open_ACU" FLOAT,
"Days_waiting_for_signature_Project_Lead" FLOAT,
"Total_Days_Open" FLOAT,
"Requester_Department" NVARCHAR2(255),
"Notes" NVARCHAR2(255),
"Days_With_ACU" NUMBER(18, 0),
"Days_to_complete" NUMBER(18, 0),
"Days_to_sign" NUMBER(18, 0)
)"

failed with the following error: "ORA-00972: identifier is too long". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

Tried this but failed with the same error:

"CREATE TABLE 'PSR003' (
'PSR_number' NVARCHAR2(255) NOT NULL,
'Date_Opened' DATE NOT NULL,
'Project_Title' NVARCHAR2(255),
'Project_Lead' NVARCHAR2(255),
'Project_Lead_Alternate' NVARCHAR2(255),
'PSR_Assignee' NVARCHAR2(255),
'Status' NVARCHAR2(255),
'FlowStatus' NVARCHAR2(255) NOT NULL,
'Date_sent_for_signature' DATE,
'Date_completed' DATE,
'Review_Days_Open_ACU' FLOAT,
'Days_waiting_for_signature_Project_Lead' FLOAT,
'Total_Days_Open' FLOAT,
'Requester_Department' NVARCHAR2(255),
'Notes' NVARCHAR2(255),
'Days_With_ACU' NUMBER(18, 0),
'Days_to_complete' NUMBER(18, 0),
'Days_to_sign' NUMBER(18, 0)
)"
Re: CREATE TABLE throws ORA-00972 Identifier is too long [message #291943 is a reply to message #291939] Mon, 07 January 2008 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Identifier can have at most 30 characters.
SQL> select length('Days_waiting_for_signature_Project_Lead') from dual;
LENGTH('DAYS_WAITING_FOR_SIGNATURE_PROJECT_LEAD')
-------------------------------------------------
                                               39

1 row selected.

Regards
Michel
Re: CREATE TABLE throws ORA-00972 Identifier is too long [message #291945 is a reply to message #291939] Mon, 07 January 2008 05:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Oracle identifiers can only be up to 30 chars long.
Days_waiting_for_signature_Project_Lead is 39 characters!!!

00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 30 characters was specified.
// *Action:  Specify at most 30 characters.
Re: CREATE TABLE throws ORA-00972 Identifier is too long [message #291949 is a reply to message #291939] Mon, 07 January 2008 05:25 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

select length('Days_waiting_for_signature_Project_Lead') from dual;
39

Have a look at this.

By
Vamsi
Re: CREATE TABLE throws ORA-00972 Identifier is too long [message #291970 is a reply to message #291949] Mon, 07 January 2008 05:52 Go to previous messageGo to next message
stevo
Messages: 3
Registered: January 2008
Junior Member
Busting my way through an excel spread sheet and will whittle done the "Identifier" names...

Thanks everyone!
Re: CREATE TABLE throws ORA-00972 Identifier is too long [message #291980 is a reply to message #291939] Mon, 07 January 2008 06:07 Go to previous message
stevo
Messages: 3
Registered: January 2008
Junior Member
Thanks again.
The execution was successful

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Saving (Success)

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Success)

- Copying to "PSR003$" (Success)
761 rows transferred

- Post-execute (Success)

- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - PSR003$" (70)" wrote 761 rows.
(SQL Server Import and Export Wizard)



Previous Topic: index
Next Topic: how to insert data from one table to another table
Goto Forum:
  


Current Time: Sat Dec 10 22:19:44 CST 2016

Total time taken to generate the page: 0.20576 seconds