For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Specify the first part of the string literal with a TO_CLOB function, so the rest of the string is considered CLOB rather than VARCHAR2...
DECLARE
K CLOB;
BEGIN
DELETE FROM ABCD;
COMMIT;
FOR I IN 1..17
LOOP
K:= K||TO_CLOB('A')||' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th'||I;
END LOOP;
INSERT INTO ABCD VALUES(k);
dbms_output.put_line(length(k));
END;
You may also need to split up your string literal in to pieces and concatenate them with ||, as there can be limits on the length of literals.
Not enough:
SQL> DECLARE
2 K CLOB;
3 BEGIN
4 FOR I IN 1..15
5 LOOP
6 K:= K||TO_CLOB('A')||' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the re
sult set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values
from the result set into fields in th
7 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set
into fields in th
8 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
9 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
10 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
11 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
12 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
13 A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields
into fields in th'||I;
14 END LOOP;
15 dbms_output.put_line(length(k));
16 END;
17 /
33246
PL/SQL procedure successfully completed.
4 FOR I IN 1..16
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 6
6 K:= K||TO_CLOB('A')||TO_CLOB(' cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values fro
m the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows o
f values from the result set into fields in th
into fields in th'||I);
35463
SQL>
SY.
A CLOB is not a PL/SQL variable - the CLOB resides in the database as LOB segments, and the variable in PL/SQL is a pointer to this database LOB. The documentation uses the phrase LOB locator, for the PL/SQL variable, but it is just a friendly non-scary name for a pointer.
A LOB variable (locator or pointer) has a formal API - application programming interface - in the form of the DBMS_LOB package.
And it makes a LOT of sense to use this API directly in your PL/SQL to create and manipulate LOB locators in PL/SQL.
Explicit API calls (like DBMS_LOB.WriteAppend) to add a string to a CLOB, reads better, is understood better, and maintains better, than your attempt to add strings to a CLOB via concatenation. As this posting proves very clearly.
Do yourself a favour and use DBMS_LOB - and do your LOB processing explicitly, and properly.
For CLOBS use concatenation only when you are absolutely certain that it will not be longer than 32767 chars (as much as fits into a varchar2 variable in PL/SQL).
If there may be more than 32767 char, then use dbms_lob.write_append.
BEDE wrote:For CLOBS use concatenation only when you are absolutely certain that it will not be longer than 32767 chars (as much as fits into a varchar2 variable in PL/SQL). If there may be more than 32767 char, then use dbms_lob.write_append.
BEDE wrote:
That's not necessarily true.
Whilst Billy is correct that it's probably good practice to use DBMS_LOB package for dealing with CLOBs, it isn't necessary.
The || concatenation operator is perfectly capable of concatenating CLOB values larger than 32767 bytes (note, it's bytes, not characters), just as the other string functions such as LENGTH, SUBSTR, INSTR etc. can all operate on CLOBs as well as VARCHAR2, without having to use the DBMS_LOB package. It's been like that for a few versions of Oracle now (certainly since 10g if not before)
Why? Concatenations works perfectly with clobs, see https://docs.oracle.com/database/121/SQLRF/operators003.htm#SQLRF51158
Numbers can't be implicit converted to clob, so just add || to_char(I) instead of ||I at line 15
DECLARE K CLOB;BEGINDELETE FROM ABCD;COMMIT; FOR I IN 1..17 LOOPK:= K||'A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th'||to_char(I); END LOOP;INSERT INTO ABCD VALUES(k);dbms_output.put_line(length(k));COMMIT;END;/
K:= K||'A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th
A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th A cursor FOR loop implicitly declares its loop index as aasdfdafasdfasdfsadfhes rows of values from the result set into fields in th'||to_char(I);
/
This is interesting. You are correct, of course (I just tried that and if we wrap I within TO_CLOB it works - not sure why you would use TO_CHAR and not TO_CLOB directly, although perhaps it makes no difference).
If we were doing this in SQL instead, not in PL/SQL, this wouldn't cause a problem. For example:
declare k clob;begin for i in 1 .. 12000 loop select k || i -- concatenate in SQL into k from dual ; end loop; dbms_output.put_line(length(k));end;/PL/SQL procedure successfully completed.48894
declare k clob;begin for i in 1 .. 12000 loop select k || i -- concatenate in SQL into k from dual ; end loop;
dbms_output.put_line(length(k));end;/
48894
Of course, this is the WRONG way to solve the problem. The RIGHT way to solve it is to wrap numbers within TO_CHAR or TO_CLOB, both in PL/SQL and in SQL - and not depend on Oracle playing Big Brother in one case but not in the other. Implicit conversions are never good.
Other than that, K was already declared CLOB. As far as the other operands are VARCHAR2 or CHAR (or other CLOB), the result of concatenating to K will always be CLOB. The only reason we need to wrap I within TO_CLOB is that it is NOT of character data type, and PL/SQL will not convert it to CLOB implicitly.
More food for thought: als long as your input fits in a varchar2 (32767) it works, even is the result bigger.
declare x clob;begin x := rpad( to_clob( 'y' ), 32767 ); -- works x := x || 1; dbms_output.put_line( length( x ) );end;
declare x clob;begin x := rpad( to_clob( 'y' ), 32768 ); -- doesn't work x := x || 1; dbms_output.put_line( length( x ) );end;
mathguy wrote:Of course, this is the WRONG way to solve the problem. The RIGHT way to solve it is to wrap numbers within TO_CHAR or TO_CLOB, both in PL/SQL and in SQL - and not depend on Oracle playing Big Brother in one case but not in the other. Implicit conversions are never good.
mathguy wrote:
Well, I'd say it is reasonable to expect from Big Brother Oracle using logic. And I don't see much logic in CLOB concatenated with number resulting in VARCHAR2 especially when this manifests itself in PL/SQL only:
SQL> declare
2 v_clob clob;
3 begin
4 v_clob := lpad(to_clob('X'),32767,'X') || 1;
5 end;
6 /
4 v_clob := lpad(to_clob('X'),32768,'X') || 1;
declare
ORA-06512: at line 4
4 v_clob := lpad(to_clob('X'),32768,'X') || to_char(1);
SQL> select lpad(to_clob('X'),32767,'X') || 1
2 from dual
3 /
LPAD(TO_CLOB('X'),32767,'X')||1
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
SQL> select lpad(to_clob('X'),32768,'X') || 1
LPAD(TO_CLOB('X'),32768,'X')||1
I suppose at some level it is a matter of opinion (or preference) - I am very much in the camp that prefers strongly typed languages. "Don't concatenate a number to a string; if you need to, convert the number to string explicitly, otherwise we'll throw a data type mismatch error." Obviously that would answer the problem at hand.
On the other hand I understand the opposing argument - if we did that, instead of allowing people to concatenate numbers to strings, the cost of developing code would be greater; there would be a lot more to test and debug, and perhaps some of the less-skilled and lower-paid developers wouldn't be able to cope at all, creating problems for everyone. (Which is why I view it as a matter of "preference" rather than "this is right and that is wrong".)
But, yes, I agree: IF Oracle is going to be Big Brother, for the reasons explained (and many more, perhaps), then at least it should be consistent with itself. Why concatenate a number to a really long CLOB without a problem in SQL, concatenate a number to a VARCHAR2 without a problem in PL/SQL as long as the result fits in VARCHAR2, but then fuss over "explicit conversion" of number, only when it is concatenated to CLOB in PL/SQL? I am not defending Oracle's decision, I am just stating my opinion that we should always use explicit conversion (if we understand the concept, and if we don't mind taking the time to write the wrapper - spend more time here and less in fixing errors).
In this PL/SQL example the result doesn't fit in a varchar2, it just starts with the largest varchar2. And it works!.
x clob;
begin
x := rpad( to_clob( 'y' ), 32767 ); -- works
x := x || 1;
dbms_output.put_line( length( x ) );
end;
Doesn't look to logical to me indeed. Maybe Oracle tries to keep the operands for the concatenation operator as a varchar2 as long as that fits. And switch to a clob when needed, not for the result but for the operands And at that point the number can't be implicit converted to the "required" type.
Yes, it does. But why? In original case we have clob and number concatenation. If we check implicit conversion matrix number can't be converted to clob directly. So Oracle converts clob to varchar2, number to varchar2 and then does concatenation. As a result, when clob is converted to varchar2 we exceed varchar2 limit of 32767 and it errors out. In your example x || 1 x is converted to varchar2 and since length(x) = 32767 no exception is raised. You'll ask why concatenation result which has length of 32768 doesn't raise exception? Pure implementation issue:
2 x clob;
4 x := rpad('y',32767) || rpad('y',32767);
Most likely intermediary results aren't checked - could be PL/SQL optimization, although setting plsql_optimize_level=0 still behaves same way.
As soon as you:
4 x := rpad( to_clob( 'y' ), 32768 );
5 x := x || 1; -- doesn't work
6 dbms_output.put_line( length( x ) );
7 end;
8 /
ORA-06512: at line 5
It appears PL/SQL intermediary VARCHAR2 operations use 64K buffer:
4 x := rpad('y',32767) || rpad('y',32767) || 'y';
4 x := rpad('y',32767) || rpad('y',32767) || 'yy';
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
It certainly looks that Oracle had left some room for improvement
{off-topic}
Hi SY,
I opted out when this forum became "fancy with avatars and all" some 5-7 years ago.
So, please excuse my now noob question on trying to return: How exactly did you format your code in above answer?
This did not help me much when I tried the same: https://community.oracle.com/docs/DOC-889070#jive_content_id_How_do_I_format_code
BR
Peter
Peter,
The tidiest way to format code is to use the Advanced Editor (which isn't available via the inbox, only directly within the thread) and then highlight the code, choose the font "Courier New" from the list of fonts and, optionally (though I like to do this), go to the >> icon and choose "quote", which insets the code in a quote box.
{even more off-topic}
Ah, I (fondly) remember Usenet!
[Not that I was lurking in Oracle groups at that time ...]
vijzDec 31 2008 — edited Dec 31 2008