Home » SQL & PL/SQL » SQL & PL/SQL » Using DBMS_REDEFINITION.START_REDEF_TABLE I get SP2-0027: Input is too long (Oracle 9.2.0.8 on Windows)
Using DBMS_REDEFINITION.START_REDEF_TABLE I get SP2-0027: Input is too long [message #300532] Fri, 15 February 2008 15:17 Go to next message
dkranes
Messages: 25
Registered: February 2008
Location: Upstate New York
Junior Member
Hello, I am using the DBMS_REDEFINITION.START_REDEF_TABLE to migrate a large table to unicore data types. When I attempt this the list of columns is too long and it generates:

SP2-0027: Input is too long (> 2499 characters) - line ignored

Below is the procedure call. Is there a way to over come this?

EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE( - 'FOCUS', 'PREFERRED_CUSTOMER','INT_PREFERRED_CUSTOMER', -'PREFERRED_CUST_KEY PREFERRED_CUST_KEY,SIGNUP_STORE_KEY SIGNUP_STORE_KEY,SIGNUP_ASSOC_EMPKEY SIGNUP_ASSOC_EMPKEY,PREFERRED_CUST_NUM PREFERRED_CUST_NUM, -ALT_PREFERRED_CUST_NUM ALT_PREFERRED_CUST_NUM,TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER,EMPLOYEE_KEY EMPLOYEE_KEY,SIGNUP_DATE SIGNUP_DATE,ALLOW_CONTACT_IND ALLOW_CONTACT_IND, -ALLOW_DISTRIB_IND ALLOW_DISTRIB_IND, ALLOW_EMAIL_IND ALLOW_EMAIL_IND, ALLOW_EMAIL_IND_EFF_DATE ALLOW_EMAIL_IND_EFF_DATE, ALLOW_MAIL_IND ALLOW_MAIL_IND, -ALLOW_MAIL_IND_EFF_DATE ALLOW_MAIL_IND_EFF_DATE, ALLOW_TELEMARKET_IND ALLOW_TELEMARKET_IND, ALLOW_DIRECT_MAIL_IND ALLOW_DIRECT_MAIL_IND, ALLOW_STMT_INSERT_IND ALLOW_STMT_INSERT_IND, -CUST_SOURCE_CD CUST_SOURCE_CD, NAME_ADDR_SOURCE_CD NAME_ADDR_SOURCE_CD, NAME_ADDR_EFF_DATE NAME_ADDR_EFF_DATE, TO_NCHAR(LAST_NAME) LAST_NAME, TO_NCHAR(FIRST_NAME) FIRST_NAME, - TO_NCHAR(MIDDLE_INIT) MIDDLE_INIT, TO_NCHAR(NAME_PREFIX) NAME_PREFIX, TO_NCHAR(NAME_SUFFIX) NAME_SUFFIX, TO_NCHAR(ADDRESS1) ADDRESS1, TO_NCHAR(ADDRESS2) ADDRESS2, -TO_NCHAR(ADDRESS3) ADDRESS3, TO_NCHAR(CITY) CITY, TO_NCHAR(STATE) STATE, COUNTRY COUNTRY, ZIP ZIP, ZIP ZIP, ZIP5 ZIP5, HOME_PHONE HOME_PHONE, AREA_CODE AREA_CODE, -AREA_CODE_EXCHNG AREA_CODE_EXCHNG, WORK_PHONE WORK_PHONE, DATE_OF_BIRTH DATE_OF_BIRTH, GENDER_CD GENDER_CD, INSTORE_CONTACT_EMPKEY INSTORE_CONTACT_EMPKEY, - LAST_TRANSACTION_TIME LAST_TRANSACTION_TIME, LAST_TRANS_AMT LAST_TRANS_AMT, LAST_RETAIL_TRANS_TIME LAST_RETAIL_TRANS_TIME, LAST_CATALOG_TRANS_TIME LAST_CATALOG_TRANS_TIME, -LAST_INTERNET_TRANS_TIME LAST_INTERNET_TRANS_TIME, LIFETIME_AMT LIFETIME_AMT, LIFETIME_RETAIL_AMT LIFETIME_RETAIL_AMT, LIFETIME_CATALOG_AMT LIFETIME_CATALOG_AMT, -LIFETIME_INTERNET_AMT LIFETIME_INTERNET_AMT, LIFETIME_UNIT_CNT LIFETIME_UNIT_CNT, LIFETIME_RETAIL_UNIT_CNT LIFETIME_RETAIL_UNIT_CNT, LIFETIME_CATALOG_UNIT_CNT LIFETIME_CATALOG_UNIT_CNT, -LIFETIME_INTERNET_UNIT_CNT LIFETIME_INTERNET_UNIT_CNT, LIFETIME_RETURN_AMT LIFETIME_RETURN_AMT, LIFETIME_RETAIL_RETURN_AMT LIFETIME_RETAIL_RETURN_AMT, -LIFETIME_CATALOG_RETURN_AMT LIFETIME_CATALOG_RETURN_AMT, LIFETIME_INTERNET_RETURN_AMT LIFETIME_INTERNET_RETURN_AMT, LIFETIME_RETURN_UNIT_CNT LIFETIME_RETURN_UNIT_CNT, -LIFETIME_RETAIL_RTRN_UNIT_CNT LIFETIME_RETAIL_RTRN_UNIT_CNT, LIFETIME_CATALOG_RTRN_UNIT_CNT LIFETIME_CATALOG_RTRN_UNIT_CNT, LIFETIME_INTERNT_RTRN_UNIT_CNT LIFETIME_INTERNT_RTRN_UNIT_CNT, -LIFETIME_VISIT_CNT LIFETIME_VISIT_CNT, LIFETIME_RETAIL_VISIT_CNT LIFETIME_RETAIL_VISIT_CNT, LIFETIME_CATALOG_VISIT_CNT LIFETIME_CATALOG_VISIT_CNT, -LIFETIME_INTERNET_VISIT_CNT LIFETIME_INTERNET_VISIT_CNT, LIFETIME_RTRN_VISIT_CNT LIFETIME_RTRN_VISIT_CNT, LIFETIME_RETAIL_RTRN_VISIT_CNT LIFETIME_RETAIL_RTRN_VISIT_CNT, -LIFETIME_CATALG_RTRN_VISIT_CNT LIFETIME_CATALG_RTRN_VISIT_CNT, LIFETIME_INTRNT_RTRN_VISIT_CNT LIFETIME_INTRNT_RTRN_VISIT_CNT, LAST_CONTACT_DATE LAST_CONTACT_DATE, -LAST_CONTACT_TYPE LAST_CONTACT_TYPE, LAST_CATALOG_SENT_DATE LAST_CATALOG_SENT_DATE, LIFETIME_CATALOG_SENT_CNT LIFETIME_CATALOG_SENT_CNT, PRIVATE_CC_ACCT_NUM PRIVATE_CC_ACCT_NUM, -PRIVATE_CC_FIRST_USE_DATE PRIVATE_CC_FIRST_USE_DATE, PRIVATE_CC_ACCT_BALANCE PRIVATE_CC_ACCT_BALANCE, PRIVATE_CC_LIMIT_AMT PRIVATE_CC_LIMIT_AMT, PRIVATE_CC_LIMIT_SET_DATE PRIVATE_CC_LIMIT_SET_DATE, -PRIVATE_CC_LIMIT_EXPIRES_DATE PRIVATE_CC_LIMIT_EXPIRES_DATE, PRIVATE_CC_NUM_CARDS_ISSUED PRIVATE_CC_NUM_CARDS_ISSUED, PRIVATE_CC_JC_FIRST_NAME PRIVATE_CC_JC_FIRST_NAME, -PRIVATE_CC_JC_MIDDLE_INIT PRIVATE_CC_JC_MIDDLE_INIT, PRIVATE_CC_JC_LAST_NAME PRIVATE_CC_JC_LAST_NAME, PRIVATE_CC_APP_STORE_KEY PRIVATE_CC_APP_STORE_KEY, - ALT_ACCT_NUMBER ALT_ACCT_NUMBER, SOCIAL_SEC SOCIAL_SEC, COMPANY_NAME COMPANY_NAME, BUSINESS_TITLE BUSINESS_TITLE, HOW_DID_YOU_HEAR_CD HOW_DID_YOU_HEAR_CD, - HOW_DID_YOU_HEAR_EFF_DATE HOW_DID_YOU_HEAR_EFF_DATE, PREFERRED_PROMO_METHOD_CD PREFERRED_PROMO_METHOD_CD, PREF_CUST_STATUS1_CD PREF_CUST_STATUS1_CD, PREF_CUST_STATUS1_EFF_DATE PREF_CUST_STATUS1_EFF_DATE, -PREF_CUST_STATUS2_CD PREF_CUST_STATUS2_CD, PREF_CUST_STATUS2_EFF_DATE PREF_CUST_STATUS2_EFF_DATE, PREF_CUST_STATUS3_CD PREF_CUST_STATUS3_CD, PREF_CUST_STATUS3_EFF_DATE PREF_CUST_STATUS3_EFF_DATE, -EMAIL_ADDR EMAIL_ADDR, COUNTRY_CD COUNTRY_CD');

--

Don, if you happen to read this it is Dave from Reuters. I appreciate any help you can provide.

Thank you,

David
Re: Using DBMS_REDEFINITION.START_REDEF_TABLE I get SP2-0027: Input is too long [message #300548 is a reply to message #300532] Fri, 15 February 2008 17:03 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://blog.econtentpark.com/2007/01/08/oracle-error-sp2-0027-input-is-too-long-2499-characters-line-ignored

Welcome to the forum. Could you please take some time to read the forum guidelines.

Regards

Raj
Previous Topic: Need an average by year of an average by month
Next Topic: Decode in the where clause
Goto Forum:
  


Current Time: Fri Dec 09 17:05:40 CST 2016

Total time taken to generate the page: 0.12060 seconds