#1 26 Jun 2009 20:55

sundaram
Member
Registered: 26 Jun 2009
Posts: 1

Stored Procedure IN Parameter

Hi All,

Iam working in Java with Oracle project, iam new to Oracle StoredProcedure, my requirement is application will give the comma sepeated string to the Stored Procedure like (8979989,33433343,454e54454,etc....) as the IN parameter to Stored Procedure, I need to create the select query base on the input.. like

select * from grand_select where gzID in (8979989,33433343,454e54454,etc....),

My Problem is the IN Parameter for the StoredProcedure is declared as VarChar2, most of the time my the INparameter is exceding the VarChar2 size, can any one help me or give me some better soln.

CREATE OR REPLACE Procedure UpdateDBData
   ( gzID  IN varchar2 )
IS
    ---Validation
-----etc

Always my gZID is exceeding the VARCHAR2 SIZE

Offline

#2 26 Jun 2009 23:30

Simon Sheppard
Super Administrator
Registered: 27 Aug 2005
Posts: 874
Website

Re: Stored Procedure IN Parameter

If varchar2 is too small then you need to use a CLOB - see datatypes http://www.ss64.com/ora/syntax-datatypes.html

heres an example
http://www.oracle.com/technology/sample … bToSP.html

CREATE OR REPLACE PROCEDURE updateStory(id NUMBER, colValue  IN CLOB) IS
BEGIN
...etc

Offline

Board footer

Powered by FluxBB