Home » SQL & PL/SQL » SQL & PL/SQL » ORA - 00972 : identifier too long (oracle 10 g)
ORA - 00972 : identifier too long [message #577209] Wed, 13 February 2013 07:56 Go to next message
mohammed123
Messages: 1
Registered: February 2013
Location: chennai
Junior Member
Hi,

I am getting error (ORA - 00972 : identifier too long) while creating the view.

Please find the view statement.

*****************************************

create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,Settlement_Date,TRACE,REF ERENCE_NUMBER,Switch_Log_ID,AUTHORIZATION_NUMBER,RESPONSE_CODE,Host_Error_Code,TERMINAL_ID,Acceptor_ID,ACQUIRING_INSTITUTION_CODE,REV _FLAG,Original_Trace_Number,From_Account,To_Account) as
select * from (select 'FT','count','DT',rownum,msg_type,pan,proc_code,txn_amt,txn_ccy_code,setl_amt,setl_ccy_code,bill_amt,bill_ccy_code,trans_dt_time,setl _date,stan,trn_ref_no,'iso field 60',
'isofield 38',resp_code,error_code,term_id,'ISOmessage42',acq_ins_id,decode(substr(msg_type,1,1),4,reverse(msg_type),msg_type),'ISOfield90',fro m_acc,to_acc from swtb_txn_log)

***************************************************************************
It seems error occurs at the decode function. if i comment it. its working fine. Please find the below code


***************************************************************************

create or replace view ELVW_ATM_REC_HANDOFF_1(Rectype,Recseq,Record_Type,Record_Sequence,MESSAGE_TYPE,PAN,PROCESSING_CODE,TRANSACTION_AMOUNT,TRANSACTION_CUR RENCY_CODE,SETTLEMENT_AMOUNT,SETTLEMENT_CURRENCY_CODE,Billing_AMOUNT,Billing_CURRENCY_CODE,Transaction_DATE,/*Transaction_TIME,*/Sett lement_Date,TRACE,REFERENCE_NUMBER,Switch_Log_ID,AUTHORIZATION_NUMBER,RESPONSE_CODE,Host_Error_Code,TERMINAL_ID,Acceptor_ID,ACQUIRING _INSTITUTION_CODE,/*REV_FLAG,*/Original_Trace_Number,From_Account,To_Account) as
select * from (select 'FT','count','DT',rownum,msg_type,pan,proc_code,txn_amt,txn_ccy_code,setl_amt,setl_ccy_code,bill_amt,bill_ccy_code,trans_dt_time,/*tr ans_dt_time,*/setl_date,stan,trn_ref_no,'iso field 60',
'isofield 38',resp_code,error_code,term_id,'ISOmessage42',acq_ins_id,/*decode(substr(msg_type,1,1),4,reverse(msg_type),msg_type),*/'ISOfield90' ,from_acc,to_acc from swtb_txn_log)

***************************************************************************

i have checked the length of the column name. Its less than 30 characters. Looking forward for your help.

Thanks in advance,
Mohammed

Re: ORA - 00972 : identifier too long [message #577212 is a reply to message #577209] Wed, 13 February 2013 08:07 Go to previous messageGo to next message
BlackSwan
Messages: 23160
Registered: January 2009
Senior Member
CREATE OR replace VIEW elvw_atm_rec_handoff_1 
(rectype, recseq, record_type, record_sequence, message_type, pan, 
processing_code, transaction_amount, transaction_currency_code, 
settlement_amount, settlement_currency_code, billing_amount, 
billing_currency_code, transaction_date, settlement_date, trace, 
reference_number, switch_log_id, authorization_number, response_code, 
host_error_code, terminal_id, acceptor_id, acquiring_institution_code, rev_flag, 
original_trace_number, from_account, to_account) 
AS 
  SELECT * 
  FROM   (SELECT 'FT', 
                 'count', 
                 'DT', 
                 ROWNUM, 
                 msg_type, 
                 pan, 
                 proc_code, 
                 txn_amt, 
                 txn_ccy_code, 
                 setl_amt, 
                 setl_ccy_code, 
                 bill_amt, 
                 bill_ccy_code, 
                 trans_dt_time, 
                 setl_date, 
                 stan, 
                 trn_ref_no, 
                 'iso field 60', 
                 'isofield 38', 
                 resp_code, 
                 error_code, 
                 term_id, 
                 'ISOmessage42', 
                 acq_ins_id, 
                 Decode(Substr(msg_type, 1, 1), 4, Reverse(msg_type), 
                                                msg_type), 
                 'ISOfield90', 
                 from_acc, 
                 to_acc 
          FROM   swtb_txn_log) 


seems OK to me.

Please provide CREATE TABLE SWTB_TXN_LOG statement

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: ORA - 00972 : identifier too long [message #577216 is a reply to message #577209] Wed, 13 February 2013 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have checked the length of the column name. Its less than 30 characters


Use SQL*Plus and copy and paste your session.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

If you don't know how to format a query, use SQL Formatter.

Regards
Michel

[Updated on: Wed, 13 February 2013 08:26]

Report message to a moderator

Re: ORA - 00972 : identifier too long [message #577223 is a reply to message #577216] Wed, 13 February 2013 09:34 Go to previous messageGo to next message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
NEVERMIND

[Updated on: Wed, 13 February 2013 09:34]

Report message to a moderator

Re: ORA - 00972 : identifier too long [message #577261 is a reply to message #577223] Wed, 13 February 2013 15:55 Go to previous message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If code you produce looks like the one you posted, boy, I see ./fa/1659/0/ your life as a developer is going to be miserable.

Perhaps you'd want to avoid using undocumented functions in your code (such as REVERSE). Might work, but some day might not. What happens if you - just for testing purposes - remove it from the DECODE statement?
Previous Topic: How to commit for every 10000 records in execute immediate.
Next Topic: SEQUENCE VALUE
Goto Forum:
  


Current Time: Mon Dec 22 09:42:02 CST 2014

Total time taken to generate the page: 0.12234 seconds