OraFAQ Forum: SQL & PL/SQL » Add sentence if result of string concatenation is too long

Home » SQL & PL/SQL » SQL & PL/SQL » Add sentence if result of string concatenation is too long (Toad for Oracle, v11.6.1.6, Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Add sentence if result of string concatenation is too long [message #617375]

Fri, 27 June 2014 17:40

a415
Messages: 1
Registered: June 2014

Junior Member

I am attempting to concatenate a VARCHAR2 (Contact Description) with a CLOB (Report). The result of this gives the error ORA-01489: result of string concatenation is too long. I used dbms_lob.substr to bring down the size of the clob, but I want to add the sentence "See platform for additional details" only when the string concatenation is too long, but not when it fits.

What's the best way to do this in the Description?

Here is my code:

Select    '"Activity_ID","' ||
          'Subject","' ||
          'Description"'
   from dual
union all
(select '"' || iden.identity_id || to_char(cont.contact_date,'YYYYMMDD') || to_char(cont.ref_number) || '","' ||  -- Activity ID
             null || '","' ||                                                                    -- Subject
             cont.contact_description || (case when cont.report is not null then 'REPORT: ' || dbms_lob.substr(cont.report,2500,1) else null end) ||'"'   --Description
 from contact cont,
        identity iden
  where cont.entity = iden.entity
    and iden.change_ind is null);

[Updated on: Fri, 27 June 2014 17:48]

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617377 is a reply to message #617375]

Fri, 27 June 2014 18:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

> but I want to add the sentence "See platform for additional details"
add sentence where exactly?

I doubt this can be done using only plain SQL.
I know it could be done using PL/SQL.

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

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617382 is a reply to message #617375]

Sat, 28 June 2014 00:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No problem for me (replacing repeated lines by ...):

SQL> create table t (v clob);

Table created.

SQL> insert into t values(lpad('A',4000,'A'));

1 row created.

SQL> update t set v=v||lpad('B',4000,'B');

1 row updated.

SQL> commit;

Commit complete.

SQL> select length(v) from t;
 LENGTH(V)
----------
      8000

1 row selected.

SQL> select 'See platform for additional details: '||v from t;
'SEEPLATFORMFORADDITIONALDETAILS:'||V
--------------------------------------------------------------------------------
See platform for additional details: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
...
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
...
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

1 row selected.

SQL> select v||' See platform for additional details' from t;
V||'SEEPLATFORMFORADDITIONALDETAILS'
--------------------------------------------------------------------------------
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
...
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
...
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
 See platform for additional details

1 row selected.

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617671 is a reply to message #617375]

Wed, 02 July 2014 11:17

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Perhaps like this?:

SELECT '"Activity_ID","' || 'Subject","' || 'Description"' FROM DUAL
UNION ALL
( SELECT    '"'
         || Iden.Identity_Id
         || TO_CHAR ( Cont.Contact_Date, 'YYYYMMDD' )
         || TO_CHAR ( Cont.Ref_Number )
         || '","'|| -- Activity ID
           NULL
         || '","'|| -- Subject
           Cont.Contact_Description
         || ( CASE
                 WHEN Cont.Report IS NOT NULL THEN
                        'REPORT: '
                     || CASE
                            WHEN DBMS_LOB.Getlength ( Cont.Report ) > 2500 THEN
                                   DBMS_LOB.SUBSTR ( Cont.Report, 2500, 1 )
                                || CHR ( 10 )
                                || 'See platform for additional details'
                            ELSE
                                Cont.Report
                        END
                 ELSE
                     NULL
             END )
         || '"' --Description
    FROM Contact Cont, Identity Iden
   WHERE Cont.Entity = Iden.Entity
     AND Iden.Change_Ind IS NULL );

[Updated on: Wed, 02 July 2014 11:28] by Moderator

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617673 is a reply to message #617671]

Wed, 02 July 2014 11:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why?

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617773 is a reply to message #617673]

Thu, 03 July 2014 08:48

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Michel Cadot wrote on Wed, 02 July 2014 12:29

Why?

Bacause:
Quote:

. . . but I want to add the sentence "See platform for additional details" only when the string concatenation is too long, but not when it fits.

[Updated on: Thu, 03 July 2014 10:02] by Moderator

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617774 is a reply to message #617773]

Thu, 03 July 2014 09:09

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

By the way you don't need DBMS_LOB for SUBSTR in 11g, normal substr would do the work.

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617781 is a reply to message #617773]

Thu, 03 July 2014 10:03

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why 2500 fits the condition "the string is too long"?
I showed that string is NEVER too long when appropriately used.

[Updated on: Thu, 03 July 2014 10:04]

Report message to a moderator

Re: Add sentence if result of string concatenation is too long [message #617789 is a reply to message #617781]

Thu, 03 July 2014 11:20

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

String is never too long, but OP for some reason (requirement?) uses the 2500 limit:
Quote:

... 'REPORT: ' || dbms_lob.substr(cont.report,2500,1)

Report message to a moderator

Previous Topic:	Killing job after sometime is not working.
Next Topic:	Another Hierarchical circular reference question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:57:34 CDT 2024