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

    Unanswered: Return value from a stored procedure

    Hi all...I have a stored procedure that does an insert, grabs the ID value and then updates another table with that ID. This works fine...My problem is I need to output the ID value as well and Im not sure of the syntax/way to go about this because frankly my pl/sql are lacking....I'd greatly appreciate if someone could point me ni the right direction of this...my stored procedure is below....basically, i want to output my nID variable...

    Code:
    CREATE OR REPLACE PACKAGE NewBatch
    AS
     
    	PROCEDURE NewBatch
    	(
    		
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID			IN			VARCHAR2
    		New
    		
    	);
    END;
    / 
    CREATE OR REPLACE PACKAGE BODY NewBatch
    AS
    	PROCEDURE NewBatch
    	(	
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID			IN			VARCHAR2
    	)
    	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;
    
    	END;	 
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Change it from a procedure to a FUNCTION and just RETURN the value.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by RhythmAddict
    Hi all...I have a stored procedure that does an insert, grabs the ID value and then updates another table with that ID. This works fine...My problem is I need to output the ID value as well and Im not sure of the syntax/way to go about this because frankly my pl/sql are lacking....I'd greatly appreciate if someone could point me ni the right direction of this...my stored procedure is below....basically, i want to output my nID variable...

    Code:
    CREATE OR REPLACE PACKAGE NewBatch
    AS
     
    	PROCEDURE NewBatch
    	(
    		
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID			IN			VARCHAR2
    		New
    		
    	);
    END;
    / 
    CREATE OR REPLACE PACKAGE BODY NewBatch
    AS
    	PROCEDURE NewBatch
    	(	
    		Dt					IN		VARCHAR2,
    		glbRegion				IN		VARCHAR2,
    		glbUser				IN			VARCHAR2,
    		cBillSys             		IN   			VARCHAR2,
    		strPromoCodes			IN			VARCHAR2,
    		BatchID			IN			VARCHAR2
    	)
    	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;
    
    	END;	 
    END;
    /

    Make another argument in procedure which is not IN but OUT e.g. nID_out, when you use

    nID_out := nID;

    value of nID will be able outside the procedure in nID_out variable.

  5. #5
    Join Date
    Dec 2003
    Posts
    148
    Hi everyone, thank you for the responses. I believe I am somewhat restricted because this is going to be called from an ASP page; and I cannot find any information on the web about calling an Oracle function from ASP, I can find info on how to call a stored procedure. So I have edited my stored procedure to include an Out variable...But I cannot get it to work. I'm not sure if its the way Im executing it (do I need to declare a variable first?) or the way I'm assigning the value that i sgetting returned within my stored procedure....My error message and screen output follows..thank you for yoru suggestions.


    Code:
    SQL> CREATE OR REPLACE PACKAGE NewBatch2
      2  AS
      3   
      4   PROCEDURE NewBatch2
      5   (
      6    
      7    Dt     IN  VARCHAR2,
      8    glbRegion    IN  VARCHAR2,
      9    glbUser    IN   VARCHAR2,
     10    cBillSys               IN      VARCHAR2,
     11    strPromoCodes   IN   VARCHAR2,
     12    BatchID    IN   VARCHAR2,
     13    nID_OUT    OUT   NUMBER
     14    
     15   );
     16  END;
     17  / 
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY NewBatch2
      2  AS
      3   PROCEDURE NewBatch2
      4   ( 
      5    Dt     IN  VARCHAR2,
      6    glbRegion    IN  VARCHAR2,
      7    glbUser    IN   VARCHAR2,
      8    cBillSys               IN      VARCHAR2,
      9    strPromoCodes   IN   VARCHAR2,
     10    BatchID    IN   VARCHAR2,
     11    nID_OUT    OUT   NUMBER
     12   )
     13   IS
     14    nID tbl_OWR_Batch.id%type;
     15    
     16   BEGIN 
     17  
     18  Insert INTO tbl_OWR_Batch (dCreated, cRegion, cCreatorID, cBillSys, cComment) Values (Dt,glbReg
    ion,glbUser,cBillSys, 
     19  strPromoCodes) returning ID into nID;
     20  
     21  Update tbl_OpenWindowRequests SET iBatch = nID WHERE ID IN BatchID AND cBillSys = cBillSys;
     22  nID_out := nID;
     23  
     24  
     25   END;  
     26  END;
     27  / 
    
    Package body created.
    
    SQL> execute NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540');
    BEGIN NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540'); END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'NEWBATCH2'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    
    SQL>

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Your procedure has 7 arguments:

    Dt IN VARCHAR2,
    glbRegion IN VARCHAR2,
    glbUser IN VARCHAR2,
    cBillSys IN VARCHAR2,
    strPromoCodes IN VARCHAR2,
    BatchID IN VARCHAR2,
    nID_OUT OUT NUMBER

    but you run it only with 6 :

    NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540');

    you have to declare variable (number type), and add it as 7th argument
    to your procedure. the output value vill be stored to this variable

    NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540', var_out);

Posting Permissions

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