Home » SQL & PL/SQL » SQL & PL/SQL » How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va (oracle 11g)
How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va [message #676459] Mon, 10 June 2019 05:29 Go to next message
Piranava
Messages: 1
Registered: June 2019
Junior Member
How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated value .Currenly we used varchar2 but it will fails when values more than 4000 are passed in.


The input is my order number where all order number are separated with (,)t .Order number is varchar2 here.

Apart from CLOB is there any other way.Please guide
Re: How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va [message #676460 is a reply to message #676459] Mon, 10 June 2019 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can pass as much as 32K with a VARCHAR2 datatype and much more with a CLOB.

[Updated on: Mon, 10 June 2019 06:12]

Report message to a moderator

Re: How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va [message #676461 is a reply to message #676460] Mon, 10 June 2019 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 13630
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it's failing at the 4000 limit then the problem is probably in the calling code.
4000 is the SQL limit for a varchar, the PL/SQL limit is 32767.
Re: How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va [message #676510 is a reply to message #676461] Thu, 13 June 2019 07:24 Go to previous message
Bill B
Messages: 1868
Registered: December 2004
Senior Member
in other words (see cookiemonster) under oracle 12 the maximum size of a varchar2 column in a table is 4000 bytes. The maximum size of a variable/parameters in a pl/sql block is 32767. In 12c and above the database can be configured to have a maximum table column size for varchar2 of 32767.

That being said, what is wrong with using CLOB. It's main purpose in life is to hold a string of characters that are bigger the 4000/32767

[Updated on: Thu, 13 June 2019 07:25]

Report message to a moderator

Previous Topic: How to build a query that gets its values from a file
Next Topic: MV Refresh On Commit
Goto Forum:
  


Current Time: Mon Jun 24 18:55:01 CDT 2019