Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00972: identifier is too long (client is using developer 6i and 10g db)
ORA-00972: identifier is too long [message #560542] Sat, 14 July 2012 02:56 Go to next message
sam524
Messages: 71
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
Re: ORA-00972: identifier is too long [message #560543 is a reply to message #560542] Sat, 14 July 2012 03:31 Go to previous messageGo to next message
John Watson
Messages: 4863
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.
Re: ORA-00972: identifier is too long [message #560548 is a reply to message #560543] Sat, 14 July 2012 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60014
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
sam524
Messages: 71
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....
Re: ORA-00972: identifier is too long [message #560566 is a reply to message #560563] Sat, 14 July 2012 10:26 Go to previous messageGo to next message
John Watson
Messages: 4863
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.
Re: ORA-00972: identifier is too long [message #560569 is a reply to message #560566] Sat, 14 July 2012 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60014
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-00972: identifier is too long [message #560570 is a reply to message #560542] Sat, 14 July 2012 13:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2079
Registered: January 2010
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.
Re: ORA-00972: identifier is too long [message #560571 is a reply to message #560542] Sat, 14 July 2012 13:31 Go to previous messageGo to next message
sam524
Messages: 71
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....

Re: ORA-00972: identifier is too long [message #560572 is a reply to message #560571] Sat, 14 July 2012 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60014
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But why do you (think you) need to set this parameter?

Regards
Michel
Re: ORA-00972: identifier is too long [message #560578 is a reply to message #560572] Sun, 15 July 2012 02:34 Go to previous messageGo to next message
sam524
Messages: 71
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...
Re: ORA-00972: identifier is too long [message #560579 is a reply to message #560578] Sun, 15 July 2012 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 19892
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!)?
Re: ORA-00972: identifier is too long [message #560580 is a reply to message #560578] Sun, 15 July 2012 03:15 Go to previous message
Michel Cadot
Messages: 60014
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Mon Dec 22 01:06:51 CST 2014

Total time taken to generate the page: 0.04272 seconds