Ошибка ora 00972 identifier is too long

ORA-00972: «identifier is too long» when using >30 characters / bytes for a user PASSWORD (Doc ID 2689690.1)

Last updated on JULY 10, 2020

Applies to:

Symptoms

When using a password that is > 30 Bytes / Characters, an error is thrown:
ORA-00972: identifier is too long

This despite the COMPATIBLE [initialization parameter] being set to a value of 12.2 (or higher).
Ref —>
SQL Language Reference document (section «Database Object Naming Rules»).

Changes

You may be trying to set a longer password value.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

After 19c Database Upgrade Concurrent Programs Fail With Error: ORA-00972: identifier is too long (Doc ID 2797871.1)

Last updated on APRIL 28, 2022

Applies to:

Symptoms

On: 12.2.6 version, Database for Application Technology

After upgrading the Oracle E-Business Suite (EBS) database to the 19.10 version, concurrent programs are failing, and the concurrent request logs show the following error:

The error also occurs when the following piece of PL/SQL code is executed.

STEPS TO REPRODUCE THE ISSUE
——————————————————-
The following steps show how to run the PL/SQL code that uses the fnd_file package, which is also used by Oracle EBS concurrent programs to create output and log files.

1. Connect as the APPS user

2. Get the folders where temp files can be created. Below is just an example of the output you might get, your values will be different.

SQL> select value from v$parameter2 where name=’utl_file_dir’;

3. Run the following piece of code to confirm you are also getting the error: ORA-00972: identifier is too long

a. Set DIR_TEMP with one of the above values:

SQL> create or replace directory DIR_TEMP as ‘/u01/db/temp/

b. Run the piece of PL/SQL code:

SET SERVEROUTPUT ON
declare
x varchar2(20);
Begin
select ‘test’
into x
from dual;
fnd_file.put_line(FND_FILE.LOG,’In the log file’);
fnd_file.put_line(FND_FILE.OUTPUT,’In the output file’);
end;

You should get an error such as:

declare
*
ERROR at line 1:
ORA-20100: ORA-20100: ORA-00972: identifier is too long
ORA-06512: at «APPS.FND_FILE», line 319
ORA-06512: at «APPS.FND_FILE», line 364
ORA-06512: at «APPS.FND_FILE», line 421
ORA-06512: at line 7

Changes

Database upgrade to 19c release.

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

SQL Error: ORA-00972: identifier is too long

I want to insert a blob into my create script with this insert query. But i get the error
Error at Command Line:3 Column:1
Error report:
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.

insert into image_templates(image_template_id,name,description,min_cm,template_blob,template_name
,template_mime,max_images) values (’12’,’test’,’test’,’10’,
FFD8FFE000104A46494600010101006000600000FFE1001645786966000049492A0008000000000000000000FFDB00
4300080606070605080707070909080A0C140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C283
7292C30313434341F27393D38323C2E333432FFDB0043010909090C0B0C180D0D1832211C2132323232323232323232
32323232323232323232323232323232323232323232323232323232323232323232323232323232FFC000110801800
20003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400
B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C1155
2D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A63646566
6768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC
2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F010003010101
0101010101010000000000000102030405060708090A0BFFC400B511000201020404030407050404000102770001020
31104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A26272829
2A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898
A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3
E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F7FAF3FF00F85DBF0F7FE861FF00C92B8FF
E375E815F1F7C27F0669DE3AF14DCE99A9CD750C31593DC2B5B32AB160E8B83B958630E7B7A5007D03FF0BB7E1EFF00
D0C3FF0092571FFC6E8FF85DBF0F7FE861FF00C92B8FFE375CFF00FC339783FF00E825AE7FDFF87FF8D51FF0CE5E0FF
F00A096B9FF007FE1FF00E35401D07FC2EDF87BFF00430FFE495C7FF1BA3FE176FC3DFF00A187FF0024AE3FF8DD73FF
00F0CE5E0FFF00A096B9FF007FE1FF00E3547FC339783FFE825AE7FDFF0087FF008D500741FF000BB7E1EFFD0C3FF92
571FF00C6E8FF0085DBF0F7FE861FFC92B8FF00E375CFFF00C339783FFE825AE7FDFF0087FF008D51FF000CE5E0FF00
FA096B9FF7FE1FFE35401D07FC2EDF87BFF430FF00E495C7FF001BA3FE176FC3DFFA187FF24AE3FF008DD73FFF000CE
5E0FF00FA096B9FF7FE1FFE3547FC339783FF00E825AE7FDFF87FF8D500741FF0BB7E1EFF00D0C3FF0092571FFC6E8F
F85DBF0F7FE861FF00C92B8FFE375CFF00FC339783FF00E825AE7FDFF87FF8D51FF0CE5E0FFF00A096B9FF007FE1FF0
0E35401D07FC2EDF87BFF00430FFE495C7FF1BA3FE176FC3DFF00A187FF0024AE3FF8DD73FF00F0CE5E0FFF00A096B9
FF007FE1FF00E3547FC339783FFE825AE7FDFF0087FF008D500741FF000BB7E1EFFD0C3FF92571FF00C6E8FF0085DBF
0F7FE861FFC92B8FF00E375CFFF00C339783FFE825AE7FDFF0087FF008D51FF000CE5E0FF00FA096B9FF7FE1FFE3540
,’test’,’test’,10)

Источник

ORA-00972: identifier is too long

All names of table are below 30 s

select
SWMET_RESULTSMONITORING.EURBDCODE C1_EURBDCODE_,
SWMET_RESULTSMONITORING.ALTERNATERBD C2_ALTERNATERBD,
SWMET_RESULTSMONITORING.ECOLOGICALSTATUSDESCRIPTION C3_ECOLOGICALSTATUSDESCRIPTION,
SWMET_RESULTSMONITORING.CHEMICALSTATUSDESCRIPTION C4_CHEMICALSTATUSDESCRIPTION,
SWMET_RESULTSMONITORING.PROTECTEDAREASTATUSDESCRIPTION C5_PROTECTEDAREASTATUSDESCRIPTION,
SWMET_RESULTSMONITORING.SUBUNITDIFFERENCES C6_SUBUNITDIFFERENCES
from RAPORTYUE.SWMET_RESULTSMONITORING SWMET_RESULTSMONITORING
where (1=1)

insert into «C_0SWMET_ResultsMonitoring*»

ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection RAP_UE_SRC.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:94)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:745)

Answers

Hi,
The problem is the Cx_ prefix the LKM is applying to the column names in the select on source, this is giveing a column alias longer than 30 chars.

Couple of things to try :

In Topology — Physical , find your technology you are using for your your source table model, edit , go to SQL -> Advanced tab and reduce the Maximum Column Name Length down (from 30?) , ODI should start to trim the colum aliases for you.

If that doesnt work .

Can you put a view on top of the source table and alias the columns to have a shorter name?

pleaase check data type and column size in both source and target tables, and again reverse engineer you source and target table.

Once it is done in model, open your interface and while mapping to target columns, please apply trim function.

Hope this may helps you

additoional fragment of log Internal GLOBAL jar:file:/C:/oracle/product/ODI_11.1.1g/oracledi/client/jdev/extensions/oracle.odi.navigator.jar!/com/sunopsis/graphical/gif/state/f_error.gif 2014-09-30 09:16:31.0 2014-09-30 09:16:31.0 0 972 ODI-1226: Step SWMET_ResultsMonitoring* fails after 1 attempt(s).

ODI-1240: Flow SWMET_ResultsMonitoring* fails while performing a Loading operation. This flow loads target table SWMET_ResultsMonitoring*.

ODI-1227: Task SrcSet0 (Loading) fails on the source ORACLE connection RAP_UE_SRC.

Name Length I try 25-35

Im not sure what your pictures are telling me ,

@phanikanth — Your suggesting checking/changing the column datatype, we can clearly see the error in the OP’s first post is on the command on source (not the target) — so it matters not how wide that column is or what the datatype is, the select statement is failing with ORA-00972: identifier is too long.

The name of the table in the From clause is fine, its columns like this :

causing the ORA-00972

So ODI is taking the source column name and adding prefix C5_ , you cant control the C5_ as thats part of the LKM getcollist method , all you can do is tell ODI to not use 30 Chars for the SQL Generation.

You say : «Name Length I try 25-35» . What does this mean? What Name? Table Name ? (As your pictures denote) , its the SQL Generation you want to try to change, not the datastore name.

I said check Topology -> Physical Architechture -> Oracle , go to the Advanced tab and look for drop it down, suggest 25 to see what the generated code looks like, the screen looks like this :

Once you have done that, let us know if it works or not, if it doesnt work — show me the new SQL being generated on the step thats failing.

The pictures you have posted up dont tell me anything, and the error from the log doesnt give me the SQL its generated, please provide this so we can help you.

Источник

Sql error ora 00972

ORA-00972 is related to a simple syntax convention that concerns identifiers within Oracle. While fixing the error is rather simple, the following information about identifiers is important to know in order to understand how to name and use identifiers throughout your code.

Each database object is represented by an identifier. This can either be a quoted identifier or nonquoted identifier. If it is a quoted identifier, it must begin and end with double quotation marks. Whenever you refer to this object anywhere else in the code, you must include it within the double quotation marks to ensure that the code runs correctly. A nonquoted identifier does not require double quotation marks, but is written as is. In either case, the names must be 30 characters or less. The only exceptions to this rule are that the names of databases can only be 8 characters long and the names of database links can only be 128 characters long.

Reserved words in Oracle cannot be used with nonquoted identifiers while quoted identifers can be reserved words. However, the latter is not a good practice and should be avoided. Reserved words have a special meaning in Oracle and therefore should not be utilized in this manner as it may cause confusion for the user and prompt errors when running the code. To ensure that you do not face difficulties with these words, it is best not to use reserved words. There is a list that details all of Oracle’s reserved words for reference.

When faced with ORA-00972, you will see the following error message:

ORA-00972: identifier is too long

You have tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that is longer than 30 characters. As previously stated, identifiers must be no longer than 30 characters. Since you did not follow this naming guideline, it prompted ORA-00972.

To resolve the error, simply rename the value to ensure that it is 30 characters or less. This should solve the problem and get rid of the error message.

Fortunately, errors concerning Oracle syntax like ORA-00972 are rather easy to fix. In order to avoid seeing such errors in the future, be sure to become familiar with the rules related to naming schemas in Oracle. While these types of errors may be simple fixes, it is best to avoid them altogether. Knowing and following the syntactical rules of Oracle and reading through official Oracle documentation concerning this topic can reduce the possibility of seeing ORA-00972.

If you continue to have problems with this or any other error, you may want to contact your database administrator or a licensed Oracle consultant to help resolve the issue. Check your consultant’s licensing and experience to be sure that your Oracle needs will be met properly.

Источник