Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00972: identifier is too long while using DBMS_SQLTUNE
ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362511] Tue, 02 December 2008 09:15 Go to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
hello
i want to run dbms.sql.tune on this

DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := "SELECT
'RCLNT' , 'RLDNR' , 'KOKRS' , 'RYEAR' , 'RBUKRS' , 'RRCTY' ,
'RVERS' , 'POPER' , 'RTCUR' , 'RUNIT' , 'DRCRK' , 'DOCCT' , 'DOCNR' ,
'DOCLN' , 'RPRCTR' , 'RHOART' , 'RFAREA' , 'RACCT' , 'HRKFT' ,
'RASSC' , 'EPRCTR' , 'ACTIV' , 'AFABE' , 'SBUKRS' , 'SPRCTR' ,
'SHOART' , 'SFAREA' , TSL , HSL , KSL , MSL , 'CPUDT' ,
'CPUTM' , 'USNAM' , 'SGTXT' , 'AUTOM' , 'DOCTY' , 'BLDAT' , 'BUDAT' ,
'WSDAT' , 'REFDOCNR' , 'REFRYEAR' , 'REFDOCLN' , 'REFDOCCT' ,
'REFACTIV' , 'AWTYP' , 'AWORG' , 'AWSYS' , 'WERKS' , 'GSBER' ,
'KOSTL' , 'LSTAR' , 'AUFNR', 'AUFPL' , 'ANLN1' , 'ANLN2' , 'MATNR' ,
'BWKEY' , 'BWTAR' , 'ANBWA' , 'KUNNR' , 'LIFNR' , 'RMVCT' , 'EBELN' ,
'EBELP' , 'KSTRG' , 'ERKRS' , 'PS_PSP_PNR' , 'KDAUF' , 'KDPOS' ,
'FKART' , 'VKORG' , 'VTWEG' , 'AUBEL' , 'AUPOS' , 'SPART' , 'VBELN' ,
'POSNR' , 'VKGRP' , 'VKBUR' , 'LOGSYS' , 'VERSA' , 'STFLG' ,
'STOKZ' , 'STAGR' , 'GRTYP' , 'REP_MATNR' , 'CO_PRZNR' , 'IMKEY' ,
'DABRZ' , 'RSCOPE' , 'BWART' , 'BLART'
FROM
SAPR3.GLPCA
WHERE
RLDNR='8A' AND RRCTY='0' AND RVERS='000' AND KOKRS='1' AND RBUKRS='0038' AND RYEAR='2008' AND RACCT='0000822020' AND RPRCTR IN ('0000000453','0000000454','0000000455','0000000456','0000000457','0000000458','0000000459','0000000370','00000009895','0000000460',' 0000000880','0000000760','0000000754','0000000749','0000000748','0000000747','0000000462') AND POPER='009' AND RCLNT='400'";

task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => 'SAPR3',
scope => 'COMPREHENSIVE',
time_limit => 200,
task_name => 'sql_tuning_task_test1',
description => 'This is a test tuning task on GLPCA table');
END;
/


i get this error:
ERROR:
ORA-00972: identifier is too long

thanks

[EDITED by LF: changed topic title; the original was "erro in PLSQL"]

[Updated on: Wed, 03 December 2008 05:18] by Moderator

Report message to a moderator

Re: erro in PLSQL [message #362618 is a reply to message #362511] Wed, 03 December 2008 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I have never used DBMS_SQLTUNE package, but - as far as I can tell - you can't enclose a string into double quotes (can you?!? Does DBMS_SQLTUNE's syntax allows it?). Use single ones instead.

NO:
sqltext_var := "SELECT 'RCLNT', 'RLDNR', 'KOKRS' FROM some_table";
YES:
sqltext_var := 'SELECT ''RCLNT'', ''RLDNR'', ''KOKRS'' FROM some_table'; 
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362626 is a reply to message #362511] Wed, 03 December 2008 06:09 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
I don't know if you are selecting a table columns or character sting. In either cases, you should NOT use the double-quote at the beginning and the end.

Besides,
Quote:
task_name_var VARCHAR2(30);

I've never used this package too, but it seems that you are assigning what you are selecting (and which has a length > 30) to the above variable that is limited to 30.
Try to enlarge the size of this variable and see if it works.

Rajy
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362676 is a reply to message #362511] Wed, 03 December 2008 13:55 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Its clear that the sql string is too long

Instead of
task_name_var VARCHAR2(30);

define as CLOB;


Moreover,as Littlefoot pointed out you can't enclose a string into double quotes.Your sql string contains syntactical error. eg,
should be like this

SELECT EMP_NAME FROM EMP WHERE JOB_ID='ADMIN' should be like below:

[code]
sqltext_var := 'SELECT EMP_NAME FROM EMP WHERE JOB_ID=''ADMIN''';
[code]

Have a look at sql_text => sqltext_va.Should not it be
sql_text => 'sqltext_va'


Regards,
Oli
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362686 is a reply to message #362676] Wed, 03 December 2008 14:37 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Olivia,

Olivia wrote on Wed, 03 December 2008 13:55

[code]
sqltext_var := 'SELECT EMP_NAME FROM EMP WHERE JOB_ID=''ADMIN''';
[code]

Have a look at sql_text => sqltext_va.Should not it be
sql_text => 'sqltext_va'




By using the notation
sql_text => 'sqltext_var'

won't the parameter be passed with a string 'sqltext_var' rather than passing the value in the variable - sqltext_var.

Regards,
Jo
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362689 is a reply to message #362511] Wed, 03 December 2008 14:50 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
I think everyone is correct about the way the variable sqltext_var is assigned a value.

This compiles and runs:

DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := 'SELECT
''RCLNT'' , ''RLDNR'' , ''KOKRS'' , ''RYEAR'' , ''RBUKRS'' , ''RRCTY'' ,
''RVERS'' , ''POPER'' , ''RTCUR'' , ''RUNIT'' , ''DRCRK'' , ''DOCCT'' , ''DOCNR'' ,
''DOCLN'' , ''RPRCTR'' , ''RHOART'' , ''RFAREA'' , ''RACCT'' , ''HRKFT'' ,
''RASSC'' , ''EPRCTR'' , ''ACTIV'' , ''AFABE'' , ''SBUKRS'' , ''SPRCTR'' ,
''SHOART'' , ''SFAREA'' , TSL , HSL , KSL , MSL , ''CPUDT'' ,
''CPUTM'' , ''USNAM'' , ''SGTXT'' , ''AUTOM'' , ''DOCTY'' , ''BLDAT'' , ''BUDAT'' ,
''WSDAT'' , ''REFDOCNR'' , ''REFRYEAR'' , ''REFDOCLN'' , ''REFDOCCT'' ,
''REFACTIV'' , ''AWTYP'' , ''AWORG'' , ''AWSYS'' , ''WERKS'' , ''GSBER'' ,
''KOSTL'' , ''LSTAR'' , ''AUFNR'', ''AUFPL'' , ''ANLN1'' , ''ANLN2'' , ''MATNR'' ,
''BWKEY'' , ''BWTAR'' , ''ANBWA'' , ''KUNNR'' , ''LIFNR'' , ''RMVCT'' , ''EBELN'' ,
''EBELP'' , ''KSTRG'' , ''ERKRS'' , ''PS_PSP_PNR'' , ''KDAUF'' , ''KDPOS'' ,
''FKART'' , ''VKORG'' , ''VTWEG'' , ''AUBEL'' , ''AUPOS'' , ''SPART'' , ''VBELN'' ,
''POSNR'' , ''VKGRP'' , ''VKBUR'' , ''LOGSYS'' , ''VERSA'' , ''STFLG'' ,
''STOKZ'' , ''STAGR'' , ''GRTYP'' , ''REP_MATNR'' , ''CO_PRZNR'' , ''IMKEY'' ,
''DABRZ'' , ''RSCOPE'' , ''BWART'' , ''BLART''
FROM
SAPR3.GLPCA
WHERE
RLDNR=''8A'' AND RRCTY=''0'' AND RVERS=''000'' AND KOKRS=''1'' AND RBUKRS=''0038'' AND RYEAR=''2008'' AND RACCT=''0000822020'' AND RPRCTR IN (''0000000453'',''0000000454'',''0000000455'',''0000000456'',''0000000457'',''0000000458'',''0000000459'',''0000000370'',''0000000989 5'',''0000000460'','' 0000000880'',''0000000760'',''0000000754'',''0000000749'',''0000000748'',''0000000747'',''0000000462'') AND POPER=''009'' AND RCLNT=''400''';

task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => 'SAPR3',
scope => 'COMPREHENSIVE',
time_limit => 200,
task_name => 'sql_tuning_task_test1',
description => 'This is a test tuning task on GLPCA table');
END;

Thanks.
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362692 is a reply to message #362686] Wed, 03 December 2008 14:54 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes John, You are right.
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362772 is a reply to message #362689] Thu, 04 December 2008 02:02 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
thanks
its work
thank u very much
Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362778 is a reply to message #362511] Thu, 04 December 2008 02:11 Go to previous messageGo to next message
lioracle
Messages: 68
Registered: February 2008
Location: Israel
Member
after i run the tune. i didnnt get the

recommmedtion
why?

SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TAS
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task_test1
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 200
Completion Status : COMPLETED
Started at : 12/04/2008 10:03:30
Completed at : 12/04/2008 10:06:52
Number of Errors : 1

DBMS_SQLTUNE.REPORT_TUNING_TAS
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SAPR3
SQL ID : 9vyamuwcf5phz
SQL Text : SELECT
'RCLNT' , 'RLDNR' , 'KOKRS' , 'RYEAR' , 'RBUKRS' , 'RRCTY' ,
'RVERS' , 'POPER' , 'RTCUR' , 'RUNIT' , 'DRCRK' , 'DOCCT' ,
'DOCNR' ,
'DOCLN' , 'RPRCTR' , 'RHOART' , 'RFAREA' , 'RACCT' , 'HRKFT' ,
'RASSC' , 'EPRCTR' , 'ACTIV' , 'AFABE' , 'SBUKRS' , 'SPRCTR' ,
'SHOART' , 'SFAREA' , TSL


Re: ORA-00972: identifier is too long while using DBMS_SQLTUNE [message #362885 is a reply to message #362511] Thu, 04 December 2008 07:05 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Sorry. There were some missing single quotes...

DECLARE
  task_name_var VARCHAR2(30);
  sqltext_var CLOB;
BEGIN
  sqltext_var := 'SELECT ''RCLNT'', ''RLDNR'', ''KOKRS'', ''RYEAR'',
                         ''RBUKRS'', ''RRCTY'', ''RVERS'', ''POPER'',
                         ''RTCUR'', ''RUNIT'', ''DRCRK'', ''DOCCT'',
                         ''DOCNR'', ''DOCLN'', ''RPRCTR'', ''RHOART'',
                         ''RFAREA'', ''RACCT'', ''HRKFT'', ''RASSC'',
                         ''EPRCTR'', ''ACTIV'', ''AFABE'', ''SBUKRS'',
                         ''SPRCTR'', ''SHOART'', ''SFAREA'', ''TSL'',
                         ''HSL'', ''KSL'', ''MSL'', ''CPUDT'', ''CPUTM'',
                         ''USNAM'', ''SGTXT'', ''AUTOM'', ''DOCTY'',
                         ''BLDAT'', ''BUDAT'', ''WSDAT'', ''REFDOCNR'',
                         ''REFRYEAR'', ''REFDOCLN'', ''REFDOCCT'',
                         ''REFACTIV'', ''AWTYP'', ''AWORG'', ''AWSYS'',
                         ''WERKS'', ''GSBER'', ''KOSTL'', ''LSTAR'',
                         ''AUFNR'', ''AUFPL'', ''ANLN1'', ''ANLN2'',
                         ''MATNR'', ''BWKEY'', ''BWTAR'', ''ANBWA'',
                         ''KUNNR'', ''LIFNR'', ''RMVCT'', ''EBELN'',
                         ''EBELP'', ''KSTRG'', ''ERKRS'', ''PS_PSP_PNR'',
                         ''KDAUF'', ''KDPOS'', ''FKART'', ''VKORG'',
                         ''VTWEG'', ''AUBEL'', ''AUPOS'', ''SPART'',
                         ''VBELN'', ''POSNR'', ''VKGRP'', ''VKBUR'',
                         ''LOGSYS'', ''VERSA'', ''STFLG'', ''STOKZ'',
                         ''STAGR'', ''GRTYP'', ''REP_MATNR'', ''CO_PRZNR'',
                         ''IMKEY'', ''DABRZ'', ''RSCOPE'', ''BWART'',
                         ''BLART''
                    FROM SAPR3.GLPCA
                   WHERE RLDNR=''8A''
                     AND RRCTY=''0''
                     AND RVERS=''000''
                     AND KOKRS=''1''
                     AND RBUKRS=''0038''
                     AND RYEAR=''2008''
                     AND RACCT=''0000822020''
                     AND RPRCTR IN (''0000000453'', ''0000000454'',
                                    ''0000000455'', ''0000000456'',
                                    ''0000000457'', ''0000000458'',
                                    ''0000000459'', ''0000000370'',
                                    ''0000000989 5'', ''0000000460'',
                                    ''0000000880'', ''0000000760'',
                                    ''0000000754'', ''0000000749'',
                                    ''0000000748'', ''0000000747'',
                                    ''0000000462'')
                     AND POPER=''009''
                     AND RCLNT=''400''';

  task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => sqltext_var,
    user_name => 'SAPR3',
    scope => 'COMPREHENSIVE',
    time_limit => 200,
    task_name => 'sql_tuning_task_test1',
    description => 'This is a test tuning task on GLPCA table');
END;


Try that!

Thanks,
Jim
Previous Topic: Advance Constraints.
Next Topic: Data output formatted
Goto Forum:
  


Current Time: Sat Dec 03 17:58:53 CST 2016

Total time taken to generate the page: 0.05533 seconds