Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Using IN clause in a stored procedure

    Hi all...I need to use the IN clause in my stored proc. This is getting run from an ASP page, and the values getting pased like this...

    8569,4583,2341, etc

    My SP works fine, unless I pass more than one value...I have read this however I am unsure how to apply it to my stored procedure. My stored proc is below...any help would be great

    Code:
    CREATE OR REPLACE PACKAGE NewBatch2
    AS
     
    	PROCEDURE NewBatch2
    	(
    		
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID				IN			VARCHAR2,
    		nID_OUT				OUT			NUMBER
    		
    	);
    END;
    / 
    CREATE OR REPLACE PACKAGE BODY NewBatch2
    AS
    	PROCEDURE NewBatch2
    	(	
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID				IN			VARCHAR2,
    		nID_OUT				OUT			NUMBER
    	)
    	IS
    		nID tbl_OWR_Batch.id%type;
    	 
    	BEGIN 
    
    Insert INTO tbl_OWR_Batch (dCreated, cRegion, cCreatorID, cBillSys, cComment) Values (Dt,glbRegion,glbUser,cBillSys, strPromoCodes) returning ID into nID;
    
    Update tbl_OpenWindowRequests SET iBatch = nID WHERE ID IN BatchID AND cBillSys = cBillSys;
    nID_out := nID;
    
     
    
    	END;	 
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See Ask Tom for the correct way to do that.

    An alternative "quick and dirty" approach is to change "WHERE ID IN BatchID" to "WHERE ID INSTR( ','||BatchID||',', ','||ID||',' ) > 0"

    That looks to see if the string ',8569,4583,2341,' contains the value ',9999,' (assuming ID=9999).

    Note that this "quick and dirty" solution prevents the use of an index on ID, so Tom Kyte's method (in the link above) may be preferable.

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Sorry, In my above post the word "this" was actually a link to this http://asktom.oracle.com/pls/ask/f?p...A:210612357425
    ask tom article, however I botched it up quite nicely

    What I meant however is that I do not know how to apply the method that he used to my scenario..

    Also, ID is an index so I cannot use INSTR
    Last edited by RhythmAddict; 09-27-04 at 14:19.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by andrewst
    See Ask Tom for the correct way to do that.

    An alternative "quick and dirty" approach is to change "WHERE ID IN BatchID" to "WHERE ID INSTR( ','||BatchID||',', ','||ID||',' ) > 0"

    That looks to see if the string ',8569,4583,2341,' contains the value ',9999,' (assuming ID=9999).

    Note that this "quick and dirty" solution prevents the use of an index on ID, so Tom Kyte's method (in the link above) may be preferable.
    You seem to have an extra ID in there. Wouldn't it be.

    WHERE INSTR( ','||BatchID||',', ','||ID||',' ) > 0
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •