PLS-00215: String length constraints must be in range (1 .. 32767)

Question

I have a oracle package and there is a function in it which is having out parameters.

the package name lets say ppp and function name lets say fff. function is below and i am unable to execute the function with out parameters.

function-

FUNCTION  fff (P_FID NUMBER,DUCTBANKLABEL VARCHAR2, SERVICEDUCTVALID OUT NUMBER ) RETURN VARCHAR2
----End of R3 - Obs#195 (1 Oct 2018)
AS

CNT NUMBER;
INSTDATE VARCHAR2(100);
DUCTSIZE NUMBER;
.
.
.
.
many more.......;
BEGIN
.
.
.function does its thing and returns the value

END;

Now am trying to call this function with a pl/sql block like this-

set serveroutput on;
declare
aa NUMBER:=129685933;
bb VARCHAR2:='1705297E TO P5547635';
cc  NUMBER;
ANS VARCHAR2;
BEGIN
ANS:=ppp.fff(aa,bb,cc);
DBMS_OUTPUT.put_line(ANS);
END;

But getting the below error-

Error report:
ORA-06550: line 3, column 4:
PLS-00215: String length constraints must be in range (1 .. 32767)
ORA-06550: line 5, column 5:
PLS-00215: String length constraints must be in range (1 .. 32767)
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Please help on how can i execute this function

score 2 · Answer 1 · answered Apr 14 '21 at 13:13

2

You need to specify VARCHAR2 variable lengths in your anonymous PL/SQL block.

DECLARE
    aa    NUMBER := 129685933;
    bb    VARCHAR2 (30) := '1705297E TO P5547635';
    cc    NUMBER;
    ANS   VARCHAR2 (200);
BEGIN
    ANS := ppp.fff (aa, bb, cc);
    DBMS_OUTPUT.put_line (ANS);
END;

answered Apr 14 '21 at 13:13

EJ Egyed

5,791
1
8
23

am just a starter so missed it completely ..its fine now but when the run the block instead of printing the output it says "anonymous block completed"..any idea what can i do? – TheSacredKiller Apr 14 '21 at 13:33

score 0 · Answer 2 · answered Apr 14 '21 at 13:13

0

varchar2 variables need to have a length as the error indicates.

Line 3

bb VARCHAR2:='1705297E TO P5547635';

and line 5

ANS VARCHAR2;

are both missing lengths. Assuming you want both to allow, say, 100 characters

bb VARCHAR2(100):='1705297E TO P5547635';
ANS VARCHAR2(100);

answered Apr 14 '21 at 13:13

Justin Cave

227,342
24
367
384

am just a starter so missed it completely ..its fine now but when the run the block instead of printing the output it says "anonymous block completed"..any idea what can i do? – TheSacredKiller Apr 14 '21 at 13:33
@TheSacredKiller - You've tagged this for SQL Developer and PL/SQL Developer, two totally different GUI tools. Which one are you actually using to execute your code? If you are trying to see something written to `dbms_output`, you may need to enable that in your GUI. – Justin Cave Apr 14 '21 at 13:37
i am using sql developer...oracle 12 c – TheSacredKiller Apr 14 '21 at 13:44
PL/SQL is a server process that has no ability to write to the client's screen. As such, dbms_output does _not_ write to the screen but to a buffer that is returned to the client _upon completion of the procedure_. It is then up to the client program to process (or ignore) that buffer. As @JustinCave said, you my need to enable it in your GUI. Or, if you are using sqlplus, you issue the _sqlplus command_ 'set serverout on' before executing the procedure. – EdStevens Apr 14 '21 at 13:44
@TheSacredKiller - I removed the PL/SQL Developer tag on the question then. In SQL Developer, if you go to View | DBMS Output, that will open the DBMS Output window. You'd want to click on the green plus icon in that window to enable DBMS Output for the session. – Justin Cave Apr 14 '21 at 13:50
@TheSacredKiller - SQL Developer is about 98% compatible with SQL\*Plus. In particular, instead of navigating menus to enable screen output, you can also issue the SQL\*Plus command directly, in the editor window, before running the anonymous block. The command is `set serveroutput on` as shown already. – Apr 14 '21 at 13:53
i tried serveroutput on and also as Justin and Stevens told enabling DBMS_OUTPUT from view but it still says "anonymous block completed".. – TheSacredKiller Apr 14 '21 at 13:57
@TheSacredKiller - Are you sure that there is something to print out? Perhaps the function is returning a NULL value or a string of all spaces or something else. – Justin Cave Apr 14 '21 at 14:00
let me put a string and then the variable and see if its printing the string or not – TheSacredKiller Apr 14 '21 at 14:06
yeah i guess it doesn't qualify the conditions inside the function body...its a big 300 line function and need to check authenticity of data. Anyways, thank you everyone for your help. appreciate it. – TheSacredKiller Apr 14 '21 at 14:11
Sounds like it would be helpful to run it through the debugger so you can watch it step by step. – William Robertson Apr 14 '21 at 22:05

PLS-00215: String length constraints must be in range (1 .. 32767)

2 Answers2