Привет. Знаю что эта ошибка говорит о наличии идентификаторов более 30 символов.
Но вот запрос. Здесь нет таких длинных имен. Однако oracle ругается. Подскажите, почему?
select Distinct
CS.DG_CODETOFK as "Код ТОФК",
CS.DG_CODEGRBSPPP as "Код ГРБС",
CS.DG_NUMBERACCOUNT as "Номер лицевого счета",
CS.IB_NUMBERBO as "Номер БО",
CS.UN_REESNUM as "Номер РеесЗапис",
CS.DA_ROOMACT as "Номер договора",
CS.DA_DATECONTRACT as "Дата соглаш",
PL.inn as "ИНН",
PL.kpp as "КПП",
PL.CODECOMPOSITE as "Код СвР",
CS.DA_SUMSUBSRUB as "Сумма Руб",
CS.DA_CODECONTRACT as "Код вида согл МФ",
IB_DATEREGISTRYBO as "Дата постановки",
SBO.APRI_CREATIONDATE as "Дата формирования Сведений БО",
PROT.PI_NUMPROT as "Номер протокола",
PROT.INF_CREATIONDATE as "Дата протокола",
PROT.DI_NOTE as "Причина отказа в протоколе",
IZV.PI_NUMPROT as "Номер извещения",
IZV.APRI_CREATIONDATE as "Дата извещения",
AI.INFO_DATEEPBS as "Дата публикации на ЕПБС"
from dc_EXP_SubsidyCurrState CS
inner join dc_EXP_SubsidyAgrmtInfo AI on CS.INFO_GUIDPARENTALDOC = AI.INFO_GUID
inner join DC_EXP_INFBO SBO on AI.INFO_GUID = SBO.SI_GUID_SS
inner join DC_MSC_PROTOCOL PROT on AI.GUID_PROTOKOL = PROT.INF_GUID
inner join DC_EXP_NOTICE_BO IZV on AI.GUIDNOTICE = IZV.APRI_GUID
inner join FS_TABLSUBSID_LIST PL on CS.DOCID = PL.docid
inner join doc d on cs.docid = d.docid
inner join docstate ds on ds.docstateid = d.docstateid
where
CS.IB_NUMBERBO not like '%TS%' and CS.DG_CODEGRBSPPP not like '501' and
ds.systemname = 'REGISTRED'
and CS.DA_DATECONTRACT >= to_date('01.01.2017','dd.mm.yyyy')
order by 1;
ORA-00972: слишком длинный идентификатор
00972. 00000 – “identifier is too long”
*Cause: An identifier with more than 30 characters was specified.
*Action: Specify at most 30 characters.
Error at Line: 4 Column: 38
Have you gotten an “ORA-00972: identifier is too long” error? Learn what caused it and how to resolve it in this article.
ORA-00972 Cause
If you’re running an SQL statement that refers to another database object (table, view, sequence, synonym, and so on), you might get this error:
ORA-00972: identifier is too long
This error occurs because the object’s name is longer than 30 characters.
The maximum value for a name of an object in Oracle is 30 characters.
For example, the error could be caused by this statement:
CREATE TABLE tbl_employee_ids_to_check_for_issues (
ID NUMBER
);SQL Error: ORA-00972: identifier is too long 00972. 00000 - "identifier is too long" *Cause: An identifier with more than 30 characters was specified. *Action: Specify at most 30 characters.
To resolve the ORA-00972 error, ensure the name of the object you’re working with is less than 30 characters.
This could be by creating the object with a shorter name, or altering it to ensure the new name has a shorter name.
If you shorten the name of the object (e.g. table) to less than or equal to 30 characters, it should work:
CREATE TABLE tbl_check_emp_ids (
ID NUMBER
);Table TBL_CHECK_EMP_IDS created.
Can You Change the Maximum Length of Object Names?
No, this cannot be changed. In Oracle 11g, and even 12cR1, the maximum length of an object name is 30 characters.
In 12cR2, the maximum value is 128 characters, if the COMPATIBLE value is set to 12.2 or higher.
So, that’s how you can resolve the ORA-00972 error.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
Learn what is Oracle Error ORA-00972: identifier is too long and what is the cause. How to fix ORA-00972: identifier is too long. Solution is identifier length should be equal to 30 or less.
Introduction:
In this tutorial, We will learn about error “ORA-00972: identifier is too long” in oracle. When this will happen and how to resolve it.
Error:
When you encounter error code ORA-00972, a message saying appears “identifier is too long.” This error is self explanative that is some problem with length of identifier.
ORA-00972: identifier is too long
Reason:
An identifier with more than 30 characters was specified.
Example:
See the below SQL to create the table with name “JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE“.
Creating table:
CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE(
ID NUMBER
);
When we run this SQL, immediately will throw the error.
SQL> CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE(
2 ID NUMBER
3 );
ERROR at line 1:
ORA-00972: identifier is too long
Saying identifier at line number 1 is causing for this error. There identifier is table name which is JAVA_W3SCHOOLS_BLOG_ORACLE_TABLE in our case and length is 32 characters causing for ORA-00972 error code.
Many users find themselves encountering ORA-00972 when attempting to access a table that has a large table name or column name length is more than expected.
Solution:
To resolve this error, All identifiers length should not exceed 30 characters. Refer the below modified table Creation Script where table name length is changed to 26 characters.
SQL> CREATE TABLE JAVA_W3SCHOOLS_BLOG_ORACLE(
2 ID NUMBER
3 );
Table created.
Special Note:
All the below are part of identifiers in oracle. Any one of these identifier length exceeds 30 characters then will see “ORA-00972” error saying “identifier is too long”
Table name
Column name
Constraint name
Partition name
Cluster name
View name
Index name
Synonym name
Table-space
Username
Etc
Conclusion:
We learnt how to resolve the oracle error ORA-00972 which is faced by many oracle developers. We must make all identifiers length should be below or equal to 30 characters.
Oracle 12.1 was still limited to 30 characters. Starting with Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.
Learn the cause and how to resolve the ORA-00972 error message in Oracle.
Description
When you encounter an ORA-00972 error, the following error message will appear:
- ORA-00972: identifier is too long
Cause
You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that was longer than 30 characters.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be 30 characters or less. You must shorten the name to no more than 30 characters for these objects.
|
mvolosevitch |
|
|
1 |
|
|
24.02.2010, 15:54. Показов 10108. Ответов 7
Использую Oracel9i + asp(OleDB) Код str = ' select id,title,NVL(date_created,(select date_created from home where id=' & id & ')) as date_created,' & _ ' description,parentid,languageid ' & _ ' from home where languageid=' & lid & ' and (id=' & id & ' or parentid=' & id & ')' в результате получется: Код select id,title,NVL(date_created,(select date_created from home where id=1)) as date_created, description,parentid,languageid from home where languageid=2 and (id=1 or parentid=1) если запустить этот запрос в Sql+,то проходит без ошибок |
|
asus 89 / 89 / 20 Регистрация: 19.02.2010 Сообщений: 966 |
||||
|
14.10.2015, 10:47 |
2 |
|||
|
Здравствуйте, форумчане!
тут у меня получается 31 символов.
0 |
|
Модератор 4201 / 3041 / 581 Регистрация: 21.01.2011 Сообщений: 13,165 |
|
|
14.10.2015, 11:15 |
3 |
|
тут у меня получается 31 символов Строковые литералы заключаются в одинарные кавычки. Двойные кавычки – для “нестандартных” имен объектов (таблиц и колонок).
1 |
|
89 / 89 / 20 Регистрация: 19.02.2010 Сообщений: 966 |
|
|
14.10.2015, 11:36 |
4 |
|
Grossmeister, спасибо, решил проблему! проблема была в JAVA бинах…
0 |
|
51 / 24 / 12 Регистрация: 10.10.2015 Сообщений: 107 |
|
|
15.10.2015, 19:39 |
5 |
|
ORA-00972 Cause: An identifier with more than 30 characters was specified. Action: Specify at most 30 characters.
0 |
|
89 / 89 / 20 Регистрация: 19.02.2010 Сообщений: 966 |
|
|
15.10.2015, 20:32 |
6 |
|
Как запускаете из-под asp? Можно ли вывести запрос, который получился перед запуском? прошу прошения но что имеете ввиду под вывести запрос?
0 |
|
51 / 24 / 12 Регистрация: 10.10.2015 Сообщений: 107 |
|
|
15.10.2015, 20:38 |
7 |
|
В отладочный файл, например. Просто текст, который передается на исполнение. Добавлено через 3 минуты
0 |
|
89 / 89 / 20 Регистрация: 19.02.2010 Сообщений: 966 |
|
|
15.10.2015, 22:10 |
8 |
|
эту ошибку получал в простом апдейте, когда проверял сам запрос… а так дело было в java бине не совсем правильно написал этот бин как только там исправил все исчезло… Добавлено через 20 секунд
0 |
