Home » SQL & PL/SQL » SQL & PL/SQL » Issue with string passing (oracle 10g)
| Issue with string passing [message #473707] |
Tue, 31 August 2010 04:57  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi
DBMS_OUTPUT.put_line ( 'OUTPUT LINE('
|| j
|| ') manoj '
|| SUBSTR (empno_result 1, 199)
);
When I am using substr It is working fine ,But When I am removing substr it is giving me the error as ORA-06512 PL/SQL number or value error:character strings tooo small.I have implemented CLOB FOR EMPNO_RESULT VARIABLE.
I dont want to truncate my data.Can you please suggest me the alternative of passing the whole string for more than 4000 characters.
Appreciate your help on the above?
Thanks & Regards
Thakur Manoj R
|
|
|
|
|
|
| Re: Issue with string passing [message #473733 is a reply to message #473719] |
Tue, 31 August 2010 08:54  |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Oracle version!!! Most likely you are on 9i or older where DBMS_OUTPUT.PUT_LINE was limited to 255 bytes. In newer versions this restriction is lifted:
SQL> select * from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> set serveroutput on
SQL> declare
2 v_clob clob := lpad('X',6000,'X');
3 begin
4 dbms_output.put_line(v_clob);
5 end;
6 /
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
.
.
.
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
| Re: Issue with string passing [message #473940 is a reply to message #473733] |
Wed, 01 September 2010 10:43 |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi
Hi
Actually There is a column by the name empno_result which is there in the packages
Case 1:
a_sachtype (j).empno_result:= empno_result ;
Here a_output is an tabletype of objecttype
l_empno_list (i).empno_result: :=empno_result ;
l_empno_list is an table type of object type
Now When I am adding substr function
a_output(j).empno_result:=substr(empno_result,1,475);
l_empno_list (i).empno_result: :=substr(empno_result,1,475) ;
This is truncating my data to 475 characters.I dont want to truncate my data.
and trying to run the wrapper with
DBMS_OUTPUT.PUT_LINE('sachline(' || j || ') result ' ||substr( empno_result,4000,1));
DBMS_OUTPUT.PUT_LINE( SUBSTR(empno_result,226,500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,501,750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,751,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1001,1250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1251,1500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1501,1750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1751,2000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,2001,2250));
This wrapper is giving data upto 475 characters.But empno_result is having very big data more than 32767 characters.This wrapper is running successfully.
Case 2:
for e.g
If I increase the length of empno_result variable in the package It is giving me error when I am running the above wrapper
empno_result is having clob datatype.
a_sachtype(j).empno_result:= substr(empno_result ,1,500);
Here a_output is a tabletype
l_empno_list (i).empno_result:=substr(empno_result,1,500);
Here l_empno_list is a tabletype
When I am running the wrapper
and trying to run the wrapper with
DBMS_OUTPUT.PUT_LINE('sachline(' || j || ') result ' ||substr( empno_result,4000,1));
DBMS_OUTPUT.PUT_LINE( SUBSTR(empno_result,226,500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,501,750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,751,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1001,1250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1251,1500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1501,1750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1751,2000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,2001,2250));
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 81
Case 3:
When I am simply writting in the package
a_sachtype(j).empno_result:= empno_result;
Here a_sachtype ios is also a tabletype.
l_empno_list (i).empno_result:=empno_result;
Here l_empno_list is a tabletype
The package is compiling
But When I am running the wrapper
DBMS_OUTPUT.PUT_LINE('sachline(' || j || ') result ' ||substr( empno_result,4000,1));
DBMS_OUTPUT.PUT_LINE( SUBSTR(empno_result,226,500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,501,750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,751,1000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1001,1250));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1251,1500));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1501,1750));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,1751,2000));
DBMS_OUTPUT.PUT_LINE(SUBSTR(empno_result,2001,2250));
It is also giving me error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "manoj_pk line 461
ORA-06512: at line 51
Why I am getting error if I am increasing it to 500?.Even I am using for loop I am getting the same error since empno_result is having very big data having more than 4000 characters.I dont have access to dbms_lob access.
Can you suggest me how to print empno_result data which is causing error and impacting my big applications?
Appreciate your help on the above.
Thanks & Regards
Thakur Manoj R
|
|
|
|
|
|
|
|
| Re: Issue with string passing [message #473946 is a reply to message #473942] |
Wed, 01 September 2010 10:56 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) What version of oracle are you using?
2) why don't you have access to dbms_lob?
3) Why are you using dbms_output at all? Generally it's only used for debugging code.
4) what do you think substr's parameters are? The third one isn't what you think it is.
|
|
|
|
|
|
| Re: Issue with string passing [message #473997 is a reply to message #473995] |
Wed, 01 September 2010 22:12 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
manoj12 wrote on Wed, 01 September 2010 22:59My Oracle version is 9.2.0.8.
As I already mentioned, in older versions like 9.2.0.8 DBMS_OUTPUT.PUT_LINE does not accept clob and limits line size to 255 bytes per line. You will have to split output into 255 byte chunks and DBMS_OUTPUT.PUT_LINE one chunk at a time.
SY.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon May 04 17:13:37 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
