20

I have a stored procedure in Oracle Database 10g where my input is a varchar2 but I'm having issues getting it to run when the input string is long (not sure of exact length maybe > 8000).

My thought is the 'intext varchar2' (as below) is by default is too small. In other cases where I need a longer string I might define a varchar2 as "intext2 VARCHAR2(32767);" I tried to define the size similarly in the code below but my syntax is incorrect.

create or replace PROCEDURE TESTPROC ( intext IN VARCHAR2

) AS ....

What is the (default) size of the intext varchar2?

Can that size be defined (increased)?

thanks

Gern Blanston
  • 42,482
  • 19
  • 50
  • 64

1 Answers1

41

You cannot specify a size for a VARCHAR2 parameter to a procedure.

The procedure should happily accept strings up to 32k in size (the maximum size of a VARCHAR2 in PL/SQL). If it were a function that was being called from SQL rather than PL/SQL, the limit would be 4k because the maximum size of a VARCHAR2 in SQL is only 4k.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Justin is this statement true for output parameter also? – D.J. May 20 '19 at 15:18
  • @D.J. - `out` parameters also do not specify a size, yes. – Justin Cave May 20 '19 at 15:28
  • But in my case its failing is there any settings that controls parameter size? – D.J. May 20 '19 at 15:33
  • 1
    @D.J. - Can you define "failing"? Since the caller needs to declare a variable to hold the output and that variable has to have a size, it is entirely possible that the procedure is trying to return a string that is larger than the variable the caller has declared. If so, the caller would need to declare a larger variable. You still wouldn't declare a length for the procedure's `out` parameter – Justin Cave May 20 '19 at 15:36
  • create or replace PROCEDURE USP_TEST(V_OUTPUT OUT VARCHAR2)IS VAR_PLSQL VARCHAR2(32000); TEMP NUMBER; BEGIN FOR REC IN 1..4005 LOOP BEGIN VAR_PLSQL := VAR_PLSQL||', '||REC; END; END LOOP; SELECT VSIZE(VAR_PLSQL) INTO TEMP FROM DUAL; DBMS_OUTPUT.PUT_LINE(TEMP); V_OUTPUT := VAR_PLSQL; END; – D.J. May 20 '19 at 15:49
  • this test procedure will fail at line 12 for me where i am assigning a pl/sql variable to parameter – D.J. May 20 '19 at 15:50
  • @D.J. - That procedure works fine if the caller declares a large enough variable to receive the output. The procedure will encounter an error if the caller declares a variable that is too small. The procedure is fine. The issue is in the caller. See for example https://livesql.oracle.com/apex/livesql/s/iey4rah32k89uasixt32i9al1 – Justin Cave May 20 '19 at 16:02
  • I am simply running this from sql developer – D.J. May 20 '19 at 16:27
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193652/discussion-between-justin-cave-and-d-j). – Justin Cave May 20 '19 at 16:29