Home » SQL & PL/SQL » SQL & PL/SQL » More than 4000 characters in PL/QL variable
More than 4000 characters in PL/QL variable [message #292099] Mon, 07 January 2008 16:50 Go to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
To the comment about whether VARCHAR2 works with > 4000 characters in PL/SQL. . .
Everything I saw said you can have > 4000 characters in a varchar2 in PL/SQL, BUT I spent quite bit of time trying to debug a Function that was returning more than 4,000 characters in a VARCHAR2(32767). And it would NOT work.

While I can have > 4,000 in the variable in PL/SQL the function failed EVERY TIME the data was more than 4000 long.
Re: problem insert clob with more than 4000 bytes [message #292101 is a reply to message #292099] Mon, 07 January 2008 18:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
cakask wrote on Mon, 07 January 2008 14:50

To the comment about whether VARCHAR2 works with > 4000 characters in PL/SQL. . .
Everything I saw said you can have > 4000 characters in a varchar2 in PL/SQL, BUT I spent quite bit of time trying to debug a Function that was returning more than 4,000 characters in a VARCHAR2(32767). And it would NOT work.

While I can have > 4,000 in the variable in PL/SQL the function failed EVERY TIME the data was more than 4000 long.




The issue is what you are returning it into. If you are returning it to another varchar2 in pl/sql, then you can have up to 32767. If you are returning it to sql, then varchar2 is limited to 4000, but you can use clob instead. Please see the demonstration below.

-- function:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION test_func
  2    RETURN VARCHAR2
  3  AS
  4    v_string VARCHAR2 (32767) := RPAD ('*', 32000, '*');
  5  BEGIN
  6    RETURN v_string;
  7  END test_func;
  8  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> 


-- returning value from function to another pl/sql varchar2 variable:
 
SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> DECLARE
  2    v_test VARCHAR2 (32767);
  3  BEGIN
  4    v_test := test_func;
  5    DBMS_OUTPUT.PUT_LINE (LENGTH (v_test));
  6  END;
  7  /
32000

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


-- returning value to a clob variable in SQL*Plus:
SCOTT@orcl_11g> VARIABLE g_clob CLOB
SCOTT@orcl_11g> EXEC :g_clob := test_func

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT LENGTH (:g_clob) FROM DUAL
  2  /

LENGTH(:G_CLOB)
---------------
          32000

SCOTT@orcl_11g> 



[Updated on: Mon, 07 January 2008 18:40]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292102 is a reply to message #292101] Mon, 07 January 2008 18:55 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
I tried testing the HELP for GET in TOAD in the SQL Editor, but also in SQL/PLUS and the codein both cases died on the "RETURN MSG" command (based on the line numbers within the Function (pointing to the RETURN) and in the executed PL/SQL code (pointing to the SELECT ESRTN.HELP)

declare
v_string1 varchar2(2000) := null;
v_string2 varchar2(2000) := null;
v_string3 varchar2(2000) := null;
v_string4 varchar2(2000) := null;
v_msg varchar2(32767);
begin
select ESRTN.help('GET') into v_msg
from dual;

v_string1 := substr(v_msg,1,2000);
v_string2 := substr(v_msg,2001,2000);
v_string3 := substr(v_msg,4001,2000);
dbms_output.put_line(v_string1);
dbms_output.put_line(v_string2);
dbms_output.put_line(v_string3);
end;

So unless I am just MORE confused - shouldn't I at least been able to have the RETURN MSG from within the Function - execute without ERROR?
Re: problem insert clob with more than 4000 bytes [message #292107 is a reply to message #292102] Mon, 07 January 2008 20:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
cakask wrote on Mon, 07 January 2008 16:55

I tried testing the HELP for GET in TOAD in the SQL Editor, but also in SQL/PLUS and the codein both cases died on the "RETURN MSG" command (based on the line numbers within the Function (pointing to the RETURN) and in the executed PL/SQL code (pointing to the SELECT ESRTN.HELP)

declare
v_string1 varchar2(2000) := null;
v_string2 varchar2(2000) := null;
v_string3 varchar2(2000) := null;
v_string4 varchar2(2000) := null;
v_msg varchar2(32767);
begin
select ESRTN.help('GET') into v_msg
from dual;

v_string1 := substr(v_msg,1,2000);
v_string2 := substr(v_msg,2001,2000);
v_string3 := substr(v_msg,4001,2000);
dbms_output.put_line(v_string1);
dbms_output.put_line(v_string2);
dbms_output.put_line(v_string3);
end;

So unless I am just MORE confused - shouldn't I at least been able to have the RETURN MSG from within the Function - execute without ERROR?


You are adding multiple layers of complexity when you add things like Toad functions and dbms_output. That makes it difficult to tell which thing is causing the problem. I don't have Toad, so I can only test in SQL*Plus and it would be better if you did the same, otherwise you can't tell if your problem is due to something with Toad. Dbms_output.put_line has a limitation of 255 characters per line, so your code is bound to raise an error. You need to assign the value, rather than selecting into it. In the example below, I have used a function without Toad, assigned the value to the variable, and displayed only 255 characters or less per line using dbms_output.put_line.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION test_func
  2    RETURN VARCHAR2
  3  AS
  4    v_string VARCHAR2 (32767) := RPAD ('*', 32000, '*');
  5  BEGIN
  6    RETURN v_string;
  7  END test_func;
  8  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> set serveroutput on
SCOTT@orcl_11g> declare
  2    v_string1 varchar2(2000) := null;
  3    v_string2 varchar2(2000) := null;
  4    v_string3 varchar2(2000) := null;
  5    v_string4 varchar2(2000) := null;
  6    v_msg varchar2(32767);
  7  begin
  8    --  select test_func into v_msg from dual;
  9    v_msg := test_func;
 10    v_string1 := substr(v_msg,1,255);
 11    v_string2 := substr(v_msg,256,255);
 12    v_string3 := substr(v_msg,31750); -- last 250
 13    dbms_output.put_line(v_string1);
 14    dbms_output.put_line(v_string2);
 15    dbms_output.put_line(v_string3);
 16  end;
 17  /
********************************************************************************
********************************************************************************
********************************************************************************
***************
********************************************************************************
********************************************************************************
********************************************************************************
***************
********************************************************************************
********************************************************************************
********************************************************************************
***********

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 



Re: problem insert clob with more than 4000 bytes [message #292114 is a reply to message #292107] Mon, 07 January 2008 21:50 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
Thanks.

But my function did not have DBMS_OUTPUT.PUT_LINE - that was in the calling code that was shown - I was trying to see what the the Function would return - and every time the Help text was less that 4001 characters. . .it worked - at 4001 it ERRORED.

I thought I mentioned that I executed the code in PL/SQL with the code I showed. In later executions I limited the PUT_LINE output to substrings of 255 - with the same results - ERROR if > 4000 and good if < or =.

I only mentioned TOAD because I ALSO tried to test there, thinking that if I had some kind of setting wrong in SQL PLUS (And it is easier for my to see the result when I executed the command
select ESRTN.help('GET') from dual

My code worked just fine by just change the function output from VARCHAR2 to CLOB - oooohh
I see you are on 11g - our environment is 9i (9.2) - could that explain the problem? Why your would work with VARCHAR2 and mine would only work with CLOB or if the length than 4001.

Oh - I was going to ask why you had the comment line:
1 declare
2 v_string1 varchar2(2000) := null;
3 v_string2 varchar2(2000) := null;
4 v_string3 varchar2(2000) := null;
5 v_string4 varchar2(2000) := null;
6 v_msg varchar2(32767);
7 begin
8 select testcak_func into v_msg from dual;
9 -- v_msg := testcak_func;
10 v_string1 := substr(v_msg,1,255);
11 v_string2 := substr(v_msg,256,255);
12 v_string3 := substr(v_msg,31750); -- last 250
13 dbms_output.put_line(v_string1);
14 dbms_output.put_line(v_string2);
15 dbms_output.put_line(v_string3);
16* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "NEMIS.TESTCAK_FUNC", line 6
ORA-06512: at line 8

but I when I commented out that line and used your other (execution) line I did get the displays of the *****.

Okay now I swear Oracle is TRYING to drive me crazy!
(I started in MF's over twenty years ago and would not see these kind of inconsistencies!)
Do you understand whet the difference is?
select testcak_func into v_msg from dual;
*** VERSUS ***
v_msg := testcak_func;
Since these are Functions
Re: problem insert clob with more than 4000 bytes [message #292116 is a reply to message #292099] Mon, 07 January 2008 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
cakask,
When are you going to read & follow the posting guidelines as stated in URL below?
http://www.orafaq.com/forum/t/88153/0/


Do NOT describe in words what you think you are doing.
Use CUT & PASTE to show us the whole & complete session;
exactly what you are entering & how Oracle responds AND use <code tags> as documented in URL above.
Re: problem insert clob with more than 4000 bytes [message #292121 is a reply to message #292116] Mon, 07 January 2008 22:29 Go to previous messageGo to next message
cakask
Messages: 11
Registered: January 2008
Junior Member
"cakask,
When are you going to read & follow the posting guidelines as stated in URL below?"

SQL> MEOW !
(Are personal insults part of the Forum? - "1. Be polite!" ) Confused

I did CUT and PASTE the code/results - but I did not CODE my question. (My cut and paste - doesn't look like Barbara's but she graciously wasn't mean to me and was addressing my question.)

1. I did NOT post the HELP TEXT because the 8,100 characters of Help text are proprietary and I didn't think the content of the 8100 characters would matter.
2. My "whole & complete session" is 122KB and most has NO bearing.
3. I have not seen an example of the URL tags you are referring to.

DO you have any answer to the question?

Do you understand whet the difference is?
select testcak_func into v_msg from dual;
*** VERSUS ***
v_msg := testcak_func;
Since these are Functions

------------------------------------
1 declare
2 v_string1 varchar2(2000) := null;
3 v_string2 varchar2(2000) := null;
4 v_string3 varchar2(2000) := null;
5 v_string4 varchar2(2000) := null;
6 v_msg varchar2(32767);
7 begin
8 select testcak_func into v_msg from dual;
9 -- v_msg := testcak_func;
10 v_string1 := substr(v_msg,1,255);
11 v_string2 := substr(v_msg,256,255);
12 v_string3 := substr(v_msg,31750); -- last 250
13 dbms_output.put_line(v_string1);
14 dbms_output.put_line(v_string2);
15 dbms_output.put_line(v_string3);
16* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "NEMIS.TESTCAK_FUNC", line 6
ORA-06512: at line 8


1 declare
2 v_string1 varchar2(2000) := null;
3 v_string2 varchar2(2000) := null;
4 v_string3 varchar2(2000) := null;
5 v_string4 varchar2(2000) := null;
6 v_msg varchar2(32767);
7 begin
8 -- select testcak_func into v_msg from dual;
9 v_msg := testcak_func;
10 v_string1 := substr(v_msg,1,255);
11 v_string2 := substr(v_msg,256,255);
12 v_string3 := substr(v_msg,31750); -- last 250
13 dbms_output.put_line(v_string1);
14 dbms_output.put_line(v_string2);
15 dbms_output.put_line(v_string3);
16* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on size 1000000
SQL> /
************************************************************************************************************************************* **************************************************************************************************************************
************************************************************************************************************************************* **************************************************************************************************************************
************************************************************************************************************************************* **********************************************************************************************************************

[Updated on: Mon, 07 January 2008 22:42]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292132 is a reply to message #292121] Mon, 07 January 2008 23:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
When you use a SQL select statement, like,

SELECT some_value FROM ...;

some_value is limited to data types and sizes that are available in SQL.

When you

BEGIN
SELECT some_value INTO some_variable FROM ...;
END;
/

some_value is still limited to data types and sizes that are available in SQL.

Only when you

BEGIN
:some_variable := some_value;
END;
/

can you use data types and sizes that are available in PL/SQL, but not SQL.

The limit for VARCHAR2 in SQL is 4000. So, when you use "select some_value into some_variable from ...;" if some_value is varchar2, it cannot exceed 4000. However, SQL has a CLOB datatype, so if some_variable is CLOB, there is no problem.

There may be some exceptions where Oracle is able to do an implicit conversion from one data type to another.

[Updated on: Mon, 07 January 2008 23:45]

Report message to a moderator

Re: problem insert clob with more than 4000 bytes [message #292142 is a reply to message #292121] Tue, 08 January 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@cakask,
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Splitting the topic.

Regards
Michel

[Updated on: Tue, 08 January 2008 00:45]

Report message to a moderator

Re: problem insert CLOB with more than 4000 bytes [message #298761 is a reply to message #292142] Thu, 07 February 2008 10:00 Go to previous messageGo to next message
ChiramMFM
Messages: 1
Registered: February 2008
Location: Spain
Junior Member
Hi All,

I have the same problem than cakask. I use a function to return a CLOB with a very long String. I tried to use CLOB like return data type, but it didn't works. Please can someone post some Code example returning a CLOB.

I invoke the function in Java. The 400 characters restriction is allowed?.

I'm sorry for my bad English

Thank You

Bruno
Re: problem insert CLOB with more than 4000 bytes [message #298766 is a reply to message #298761] Thu, 07 February 2008 11:02 Go to previous message
Michel Cadot
Messages: 64136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Large Objects

Regards
Michel
Previous Topic: Directory Objects
Next Topic: ORA-00900: invalid SQL statement
Goto Forum:
  


Current Time: Thu Dec 08 06:12:55 CST 2016

Total time taken to generate the page: 0.08498 seconds