OraFAQ Forum: SQL & PL/SQL » ORA-00972: identifier is too long

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00972: identifier is too long (client is using developer 6i and 10g db)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00972: identifier is too long [message #560542]

Sat, 14 July 2012 02:56

sam524
Messages: 77
Registered: July 2012
Location: sydney

Member

SQL> alter system "_allow_level_without_connect_by"=true scope=spfile;
alter system "_allow_level_without_connect_by"=true scope=spfile
*
ERROR at line 1:
ORA-00972: identifier is too long

how to solve this query

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560543 is a reply to message #560542]

Sat, 14 July 2012 03:31

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

SET is missing.

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560548 is a reply to message #560543]

Sat, 14 July 2012 03:48

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition NEVER use hidden/underscore parameter unless Oracle support tells you to do so.
And I wonder why do you need this parameter? It seems something is wrong in your application.

Regards
Michel

[Updated on: Sat, 14 July 2012 03:49]

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560563 is a reply to message #560548]

Sat, 14 July 2012 10:11

sam524
Messages: 77
Registered: July 2012
Location: sydney

Member

after executing the previous commmand sucessfully...... the system got altered .....but the client is getting an error in generating report MSG-00002 ora-04062 time stamp of package xxx2000.PKG_GENERIC_REPORT

need help really appriciate....

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560566 is a reply to message #560563]

Sat, 14 July 2012 10:26

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Quote:

need help really appriciate....

Well, I would really appreciate it if you were to say "thank you" to the people who try to assist you. Also,
Quote:

Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

and lastly, we have no idea of what your code is doing, or why you want to set that hidden parameter. I see no way that I can assist further. Perhaps someone else can.

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560569 is a reply to message #560566]

Sat, 14 July 2012 12:45

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe me as soon as I get the answer to my question (and yours).
Requesting for help but ignoring the questions we post to help is, I think, quite rude.

Regards
Michel

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560570 is a reply to message #560542]

Sat, 14 July 2012 13:10

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

tansif wrote on Sat, 14 July 2012 03:56

how to solve this query

Well, you open Oracle docs and check ALTER SYSTEM syntax. Then you realize it is ALTER SYSTEM SET ...

SY.

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560571 is a reply to message #560542]

Sat, 14 July 2012 13:31

sam524
Messages: 77
Registered: July 2012
Location: sydney

Member

thank you very much,it was really a good help,but still the issue didnt solved.....iam getting an report error,i iwll take an oracle support for that.....

I think he has to restart the database services once the system got altered but i think he didnt ....

if still the error occurs ,i will take an oracle support..

thank you very much for your help guys really appriciate....

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560572 is a reply to message #560571]

Sat, 14 July 2012 15:03

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But why do you (think you) need to set this parameter?

Regards
Michel

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560578 is a reply to message #560572]

Sun, 15 July 2012 02:34

sam524
Messages: 77
Registered: July 2012
Location: sydney

Member

hi Michel,
The issue was like this

Client is using 10g and developer 6i to generate forms and reports, last night he got error connect by clause error which was ora -01788 when generating his reports in developer 6i,which is remotely connected to db link server...... so I told him to execute this command

SQL> alter system set "_allow_level_without_connect_by"=true scope=spfile;

System altered

And restart the database services once he restarted the database services...

And he is getting ORA-040602 timestamp package generic_pkg_rpt has been changed

really appriciate,if you can help me out to solve this error..

thanking you...

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560579 is a reply to message #560578]

Sun, 15 July 2012 02:47

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

he got error connect by clause error

Where exactly did he use CONNECT BY? Was it report's query? A formula column? Something else?

Is LEVEL necessary for what he's doing? Maybe it can be rewritten so that the result is just the same, only by using some other features. Usual, trivial workaround might be creating a view (it most probably won't complain about CONNECT BY) and use that view in the report.

Anyway: to get a more accurate suggestion, perhaps you should describe what he has done and how. Because, we are just shooting in the dark.

Finally: you never told us how come you chose to set that parameter with ALTER SYSTEM? Did Oracle Support told you so, or did you find that on Google (i.e. someone set it and all his problems have been solved!)?

Report message to a moderator

Re: ORA-00972: identifier is too long [message #560580 is a reply to message #560578]

Sun, 15 July 2012 03:15

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So either you wrongly use LEVEL pseudo-column in a non hierarchical query (CONNECT BY), either you called LEVEL a column.
In the former, fix the query, LEVEL is irrelevant.
In the latter, either change the name of the expression alias (if it is one), or change the name of the column (if it is a table column), or enclose it between ".

SQL> create table x ("LEVEL" integer);

Table created.

SQL> select level from x;
select level from x
                  *
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block


SQL> select "LEVEL" from x;

no rows selected

But the BEST solution is to change the name of the column and to ALWAYS check v$reserved_words when you create an object.
And the WORST solution is the one you took: use a hidden parameter.

Regards
Michel

[Updated on: Sun, 15 July 2012 03:15]

Report message to a moderator

Previous Topic:	Copying the Table
Next Topic:	Data manipulation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 00:56:17 CDT 2025