What is the default size of a varchar2 input to Oracle stored procedure, and can it be changed?

oracle stored-procedures varchar2
Gern Blanston picture Gern Blanston · Jan 13, 2011 · Viewed 49.7k times · Source

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

Answer

Justin Cave picture Justin Cave · Jan 13, 2011

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.

Related questions

Run Stored Procedure in SQL Developer?

I am trying to run a stored procedure that has multiple in and out paramaters. The procedure can only be viewed in my Connections panel by navigating Other Users | | Packages | | If I right click , the menu items are "Order Members …

Read More
Oracle Error ORA-06512

Just can't figure out why it gives me ORA-06512 Error PROCEDURE PX(pNum INT,pIdM INT,pCv VARCHAR2,pSup FLOAT) AS vSOME_EX EXCEPTION; BEGIN IF ((pNum < 12) OR (pNum > 14)) THEN RAISE vSOME_EX; ELSE EXECUTE IMMEDIATE 'INSERT INTO …

Read More
How to test an Oracle Stored Procedure with RefCursor return type?

I'm looking for a good explanation on how to test an Oracle stored procedure in SQL Developer or Embarcardero Rapid XE2. Thank you.

Read More