How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

coracleoracle-11g-r2stored-procedures

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.

https://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:

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

Best Answer

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.

Related Solutions

Sql-server – SQL 2005- stored procedure performance troubleshooting

Things like select count(*) are just fine as long as the where clause is indexed correctly.

DTA may or may not be of any use.

The actual and estimated plan are what you need to look at. It'll tell you if there are any indexes which are missing. The indexes which are used will be in the execution plan.

Sql-server – How to output more than 4000 characters in sqlcmd

To be clear, the issue is really with the PRINT command and not the SQLCMD utility.

I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 characters, but not beyond 8000, run the following:

sqlcmd -Q "DECLARE @Yo VARCHAR(MAX) = REPLICATE(CONVERT(VARCHAR(MAX), '#'), 9000); PRINT @Yo;" -o out.txt

If you need more than 8000 characters printed, then you need to iterate over the variable in chunks of up to 8000 characters (the PRINT command will only display up to 8000 CHAR / VARCHAR characters at a time). But you don't want to simply chop it into even 8000 character chunks as that could be right in the middle of something that should not be split, like a word / object name / number, etc. Since the PRINT command will always include a newline character at the end, it is best to print up to the last newline character of each 8000 character chunk. The result is that the only lines to get split across multiple displayed lines are those that are over 8000 characters before the next newline character, and there is really nothing that can be done about that.

Try the following proc in place of your print(@sql) command (FYI: you don't need parenthesis around @sql for PRINT like you do for EXEC). Please note that finding the last newline character in a string requires creating a LastIndexOf function since one is not built into T-SQL. You can create a T-SQL UDF to do this. You can also use SQLCLR in which case you can code it yourself or simply get the Free version of the SQL# library (which I am the author of).

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;

IF (OBJECT_ID(N'dbo.Display') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.Display;
END;

GO
CREATE PROCEDURE dbo.Display
(
  @TextToDisplay VARCHAR(MAX)
)
AS
SET NOCOUNT ON;

  DECLARE @Length INT = LEN(@TextToDisplay),
          @Buffer VARCHAR(8000),
          @BufferLength INT,
          @StartIndex INT = 1,
          @LastNewlineIndex INT;

  SET @TextToDisplay = REPLACE(@TextToDisplay, CHAR(13), ''); -- normalize

  WHILE (1 = 1)
  BEGIN
    SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 8000);
    SET @BufferLength = DATALENGTH(@Buffer);

    IF (@BufferLength < 8000)
    BEGIN
      BREAK;
    END;

    SET @LastNewlineIndex =
                   SQL#.String_LastIndexOf(@Buffer, CHAR(10), @BufferLength, 1);

    IF (@LastNewlineIndex > 0)
    BEGIN
      PRINT SUBSTRING(@Buffer, 1, (@LastNewlineIndex - 1));
      SET @StartIndex += @LastNewlineIndex;
    END;
    ELSE
    BEGIN
      PRINT @Buffer;
      SET @StartIndex += @BufferLength;
    END;
  END; -- WHILE (1 = 1)

  -- Don't print empty line if final chunk was 8000 chars leaving final loop with 0
  IF (DATALENGTH(@Buffer) > 0)
  BEGIN
    PRINT @Buffer;
  END;
GO

Tests:

DECLARE @Test VARCHAR(MAX);
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7998) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1' + REPLICATE('a', 7998) + '1';
SET @Test = @Test + '2' + REPLICATE('b', 7998) + '2';
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';
----
SET @Test = '';
SET @Test = @Test + '1.1' + REPLICATE('a', 7000) + '1.1' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.2' + REPLICATE('a', 400) + '1.2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '1.3' + REPLICATE('a', 4000) + '1.3' + CHAR(13) + CHAR(10);
SET @Test = @Test + '2' + REPLICATE('b', 1798) + '2' + CHAR(13) + CHAR(10);
SET @Test = @Test + '3' + REPLICATE('c', 7000) + '3';

SELECT @Test;

PRINT '=============================';
EXEC dbo.Display @Test;
PRINT '=============================';

NOTES:

You could get away using the built-in CHARINDEX function to look for each next newline, and in this respect it would function almost like using a string splitting function. The difference between what is needed here and doing a simple split on newlines is that any element returned that is over 8000 characters will still need to be split into chunks of no more than 8000 characters.
In order to handle NVARCHAR(MAX) instead of VARCHAR(MAX), use the code that I posted to PasteBin: T-SQL Stored Proc to PRINT NVARCHAR(MAX) values
There is a stored procedure, Util_Print, in the SQL# library that handles NVARCHAR(MAX) and has some functionality beyond the T-SQL code shown above, but that is only available in the Full (i.e. paid for) version, not in the Free version. But the code shown here will handle most cases.

Best Answer

Related Solutions

Sql-server – SQL 2005- stored procedure performance troubleshooting

Sql-server – How to output more than 4000 characters in sqlcmd

Related Question