Forum Stats

  • 3,770,132 Users
  • 2,253,073 Discussions
  • 7,875,337 Comments

Discussions

ORA-01489: result of string concatenation is too long for my insert statement.

User_UOWPF
User_UOWPF Member Posts: 2 Employee

hi all,

below is my insert statement:

insert into oalb2b.temp8(partner_name,translated_name,company_name,company_id,

tca_party_id,tca_party_number,tca_account_number,tca_account_id, orm_status,opn_status,opn_number,opn_membership_level,

membership_type,account_number,partner_registry_id,partner_type,created_by,created_date,updated_by,updated_date)

SELECT distinct HZ.PARTY_NAME Partner_Name,AP.PARTY_NAME Translated_Name,HZ.PARTY_NAME Company_Name,PP.COMPANY_NUMBER Company_Id,

HZ.PARTY_ID TCA_Party_Id,HZ.PARTY_NUMBER TCA_Party_Number,HZC.ACCOUNT_NUMBER TCA_Account_Number,

HZC.CUST_ACCOUNT_ID TCA_Account_Id,HZ.STATUS,PP.STATUS OPN_Status, PP.COMPANY_NUMBER OPN_Number,

PP.PARTNER_LEVEL OPN_Membership_Level,PP.EXTN_ATTRIBUTE_CHAR003 Membership_Type,HZC.ACCOUNT_NUMBER TCA_Account_Number,

HZ.PARTY_NUMBER Partner_Registry_Id,listagg(PDM.DIMENSION_MEMBER_NAME,';') within group (order by PDM.DIMENSION_MEMBER_NAME) Partner_Type,

'ONE_TIME_MIGRATION',sysdate,'ONE_TIME_MIGRATION',sysdate

from OALFSAAS_REPL.ZPM_PARTNER_PROFILES PP, OALFSAAS_REPL.HZ_PARTIES HZ,oalb2b.temp tp,oalb2b.temp2 tp2,

   OALFSAAS_REPL.HZ_CUST_ACCOUNTS HZC,OALFSAAS_REPL.ZPM_PARTNER_DIM_MEMBERS PDM,

   OALFSAAS_REPL.HZ_ADDTNL_PARTY_NAMES AP

where 1=1

and PP.COMPLIANCE_STATUS = 'ANC_ACTIVE'

and HZ.PARTY_NAME = tp.party_name

AND HZ.PARTY_ID = PP.PARTY_ID

AND HZ.PARTY_ID = HZC.PARTY_ID

AND HZ.STATUS = 'A'

AND HZC.STATUS = 'A'

AND PDM.OWNER_ID = HZ.PARTY_ID

AND AP.PARTY_ID = HZ.PARTY_ID

AND AP.STATUS_FLAG = 'A'

AND AP.PARTY_NAME_TYPE = 'TRANSLATED'

group by HZ.PARTY_NAME,AP.PARTY_NAME,HZ.PARTY_NAME,PP.COMPANY_NUMBER,HZ.PARTY_ID,HZ.PARTY_NUMBER,HZC.ACCOUNT_NUMBER,

    HZC.CUST_ACCOUNT_ID,HZ.STATUS,PP.STATUS,PP.COMPANY_NUMBER,PP.PARTNER_LEVEL,PP.EXTN_ATTRIBUTE_CHAR003,HZ.PARTY_NUMBER;


but when I run a select statement I am getting proper oupout.


thanks in advance.

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    but when I run a select statement I am getting proper oupout.

    Fetch all rows.

  • mathguy
    mathguy Member Posts: 10,164 Blue Diamond

    What User_[Whatever] likely means is that when you are "getting proper output" you may not, in fact, get proper output; you only think you do.

    Many client programs ask the database server to send them chunks of "N" rows of output (for example, 200 rows at a time) even while the query is still executing, so that they can begin to show output to the user as soon as possible. What you may be seeing is just the first "N" rows of output - and the "string concatenation too long" error does not occur in these first "N" rows, but later. If you click ENTER, or whatever other way your client program uses to request more output, enough times, you will get the same error from the SELECT statement.

    (I don't know if he is right or not - I am just explaining to you what he probably meant.)

    A couple of questions for you though:

    An aggregate query (with a GROUP BY clause) by definition cannot return duplicates, if all the columns in GROUP BY also appear in SELECT. In your case, they do. So why are you selecting DISTINCT?

    Also, naming the same column in GROUP BY more than once makes no sense. Why do you have so many different columns appear in GROUP BY more than once?

    And, specifically to your LISTAGG - why is a list of member names labeled as "partner type"? That makes no sense. Are you sure you are returning the correct values for that column in the first place? (Or is the word "type" used in some non-standard sense?)

  • EdStevens
    EdStevens Member Posts: 28,533 Gold Crown

    Just as an aside, your code is very difficult to read. First, you did not format it within your posting as 'code', second, it is just one hot mess of run-on, unformatted code. When you do that, it discourages people from trying to read it and understand it.

    If you will actually format your code in the first place, the preserve that formatting when you post (see https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text), it will be much more readable, and much more likely to attract a response. Here's your code, formatted: (took me longer to write this paragraph than it did to run your code through a formatter)

    INSERT INTO oalb2b.temp8 
                (partner_name, 
                 translated_name, 
                 company_name, 
                 company_id, 
                 tca_party_id, 
                 tca_party_number, 
                 tca_account_number, 
                 tca_account_id, 
                 orm_status, 
                 opn_status, 
                 opn_number, 
                 opn_membership_level, 
                 membership_type, 
                 account_number, 
                 partner_registry_id, 
                 partner_type, 
                 created_by, 
                 created_date, 
                 updated_by, 
                 updated_date) 
    SELECT DISTINCT HZ.party_name                                       Partner_Name 
                    , 
                    AP.party_name 
                    Translated_Name, 
                    HZ.party_name                                       Company_Name 
                    , 
                    PP.company_number 
                    Company_Id, 
                    HZ.party_id                                         TCA_Party_Id 
                    , 
                    HZ.party_number 
                    TCA_Party_Number, 
                    HZC.account_number 
                    TCA_Account_Number, 
                    HZC.cust_account_id 
                    TCA_Account_Id, 
                    HZ.status, 
                    PP.status                                           OPN_Status, 
                    PP.company_number                                   OPN_Number, 
                    PP.partner_level 
                    OPN_Membership_Level, 
                    PP.extn_attribute_char003 
                    Membership_Type, 
                    HZC.account_number 
                    TCA_Account_Number, 
                    HZ.party_number 
                    Partner_Registry_Id, 
                    Listagg(PDM.dimension_member_name, ';') 
                      within GROUP (ORDER BY pdm.dimension_member_name) Partner_Type 
                    , 
                    'ONE_TIME_MIGRATION', 
                    SYSDATE, 
                    'ONE_TIME_MIGRATION', 
                    SYSDATE 
    FROM   oalfsaas_repl.zpm_partner_profiles PP, 
           oalfsaas_repl.hz_parties HZ, 
           oalb2b.temp tp, 
           oalb2b.temp2 tp2, 
           oalfsaas_repl.hz_cust_accounts HZC, 
           oalfsaas_repl.zpm_partner_dim_members PDM, 
           oalfsaas_repl.hz_addtnl_party_names AP 
    WHERE  1 = 1 
           AND PP.compliance_status = 'ANC_ACTIVE' 
           AND HZ.party_name = tp.party_name 
           AND HZ.party_id = PP.party_id 
           AND HZ.party_id = HZC.party_id 
           AND HZ.status = 'A' 
           AND HZC.status = 'A' 
           AND PDM.owner_id = HZ.party_id 
           AND AP.party_id = HZ.party_id 
           AND AP.status_flag = 'A' 
           AND AP.party_name_type = 'TRANSLATED' 
    GROUP  BY HZ.party_name, 
              AP.party_name, 
              HZ.party_name, 
              PP.company_number, 
              HZ.party_id, 
              HZ.party_number, 
              HZC.account_number, 
              HZC.cust_account_id, 
              HZ.status, 
              PP.status, 
              PP.company_number, 
              PP.partner_level, 
              PP.extn_attribute_char003, 
              HZ.party_number; 
    


    Now, go and do likewise on your future posts.