Returning a value from a stored proc..

[RhythmAddict112]

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...

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;
/

thank you in advance!

 

[dsanchez2]

Change the Procedure to a Function. For example:

    FUNCTION NewBatch
    (    
        Dt            IN VARCHAR2,
        glbRegion     IN VARCHAR2,
        glbUser       IN VARCHAR2,
        cBillSys      IN VARCHAR2,
        strPromoCodes IN VARCHAR2,
        BatchID       IN VARCHAR2
    )
    RETURN tbl_OWR_Batch.id%type 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;

    RETURN nID;

    END;

 

[RhythmAddict112]

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.

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>

 

[SantaMufasa]

Addict,

Your procedure (NewBatch2.NewBatch2) specifies 7 arguments: 6 VARCHAR2s and 1 NUMBER. Your "exec NewBatch2.NewBatch2" has only 6 VARCHAR arguments. You must specify the seventh argument preferrably as a NUMBER field, that is visible (in this case) to your SQL*Plus session, but visible to ASP when calling NewBatch2.NewBatch2 from ASP. Here is an example of a way to define and specify the seventh argument from within SQL*Plus:

SQL> var x number
SQL> execute NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540',:x);

Let us know your results/findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:54 (17Sep04) UTC (aka "GMT" and "Zulu"), 09:54 (17Sep04) Mountain Time)

 

[RhythmAddict112]

Mufasa,
Thanks for your reply. Executing the SP as you did does in fact work fine in sql plus. However, running that does not work in ASP...returns:

Microsoft OLE DB Provider for Oracle (0x80040E14)
ORA-06550: line 1, column 71: PLS-00363: expression ':x' cannot be used as an assignment target ORA-06550: line 1, column 7: PL/SQL: Statement ignored


However, it does work if I do not return a value and use this syntax...

 

[SantaMufasa]

Addict,

My apologies for not being clear: SQL*Plus uses the "var x number" plus the ":x" reference. I am not ASP literate, so I cannot suggest the syntax you will use for ASP to define and use the counterpart variable to SQL*Plus's ":x" definition and use. Does ASP not allow the definition of local variables?

If ASP does not allow local-variable definition, then ASP should absolutely, positively allow reference to SQL functions (such as SYSDATE, SUBSTR(...), UPPER(...), et cetera). If my presumption is correct about ASP's ability to use and refer to SQL functions, then ASP should also allow references to user-defined functions. If that is also correct, then you may wish to use "DSanchez's" suggestion about changing your procedure to a function. Then, in ASP, you would say something like:

SELECT NewBatch2.NewBatch2('17-SEP-2004','NY','greenf','I',' ','8540')
from <whatever>;

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:22 (17Sep04) UTC (aka "GMT" and "Zulu"), 10:22 (17Sep04) Mountain Time)

 

[RhythmAddict112]

I have created the function successfully...However, I do not know how to call this function in sql plus ( i have never worked with functions before ) Mainly, How do I pass all the values to the function and get my ID number back? ( in sql plus ) ?

Thank you for your help

 

[RhythmAddict112]

I have tried this....

  1* select newb('17-SEP-2004','NY','greenf','I',' ','8540') from dual
SQL> /
select newb('17-SEP-2004','NY','greenf','I',' ','8540') from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "s.NEWB", line 14

 

[SantaMufasa]

Addict,

My bad...I forgot that a function cannot do DML. It appears that you will need to discover how ASP defines and handles local variables. Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:54 (17Sep04) UTC (aka "GMT" and "Zulu"), 11:54 (17Sep04) Mountain Time)

 

[gunjan14]

Hi,

Probably this will help

http://www.asp101.com/samples/viewasp.asp?file=storedprocs.asp

Regards,
Gunjan