Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is the syntax for a stored procedure that outputs a refcursor, a varchar2, and a number?

I would like to output all three in the result, for the purpose of displaying count, and a message which indicates a successful query in addition to the sys_refcursor output but I cannot seem to find a good example of it.

My current C# code looks like this:

using Oracle.DataAccess.Client; 
//...

OracleCommand myCmd = new OracleCommand();
myCmd.Connection = myConnection;
myCmd.CommandText = "mypackage.myprocedure"
myCmd.CommandType = CommandType.StoredProcedure;
OracleParameter param0 = myCmd.Parameters.Add("myinput", OracleDbType.Varchar2, myinput, ParameterDirection.Input);
OracleParameter param1 = myCmd.Parameters.Add("myretcursor", OracleDbType.RefCursor, ParameterDirection.Output);

myConnection.Open();
OracleDataReader resultReader = myCmd.ExecuteReader();
mydatatable.Load(resultReader, LoadOption.OverwriteChanges);

and the Oracle stored procedure looks like this:

    PROCEDURE myprocedure (myinput IN Varchar2, myretcursor OUT sys_refcursor)

    IS

    BEGIN
    OPEN myretcursor FOR
    SELECT * FROM MYTABLE

    END myprocedure

I guess I want to add param2, param3 to the the c# as additional output parameters, then also somehow also output them in the stored procedure? I was not sure how to do this and kept encountering errors. Ideally I would, for exmple, pass the select count (*) as output into the c# application, as well as a varchar2 string message like 'Query Successful'.

Thank you.

UPDATE: Vincent's solution works below... regarding the C# code behind, the answer to the link he provided recommends enclosing the reader = executereader in a USING( ) for loading the table, and closing the reader before loading the other variables.

http://stackoverflow.com/q/6882690/613799

Depending on the size of the string from oracle, the calling c# code may need to specify the size of the varchar2 in the output parameter definition for the string message. Finally the following link was helpful for type casting the count from OracleDecimal to C# int:

http://stackoverflow.com/q/3026036/613799

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

share|improve this answer
    
I see... is there a reason you did not indicate OUT for p_count Number, p_message varchar2? Also can I see how you would read these two variables in the c# I was thinking to use ExecuteReader() again but that might not make sense for this variable type... Thanks. –  fa1c0n3r Sep 25 '12 at 11:30
    
Oops, sorry I meant to declare them as OUT variables, corrected. –  Vincent Malgrat Sep 25 '12 at 11:31
1  
I can't test c# code right now, but maybe this example on SO could help you: From Stored Procedure, return OUT parameter & OUT cursor & parse result. –  Vincent Malgrat Sep 25 '12 at 11:35
    
thanks let me try this out. –  fa1c0n3r Sep 25 '12 at 11:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.