Returning a recordset from Oracle to .Net using an Oracle function that takes arguments

Question

I currently have the following funciton in an oracle database that returns a concatenated string seperated by pipes. It's a legacy application that is being updated to use .net 3.5. The exiisting application concatenates the returned result set into a VARCHAR2 data type. What I want to do is return the entire result set back to my .net client. The MS SQL equivalent of what I'm trying to accomplish is a simple "SELECT * FROM TBL WHERE id = @id" I'm not use to some of the concepts Oracle uses. I't seems like e blend of OOP and SQL querying. I've read multiple examples on this but can't seem to find exactly what I'm looking for. Can you please help?

CREATE OR REPLACE FUNCTION DOCSADMIN.GET_DOCS (
    RECID IN NUMBER) -- RECORD ID
    RETURN VARCHAR2 -- CONCATENATED STRING WITH PIPES
IS
    RETVAL          VARCHAR2(5000) :='';
    DOCSTRING       VARCHAR2(5000) :='';    
    DOCNAME      VARCHAR2(5000) :='';
    DOCNUMBER    NUMBER;
    STATUS       VARCHAR2(5000) :='';
    DOCTYPE      VARCHAR2(5000) :='';
    EDITDATE     DATE :='';
/******************************************************************************
   NAME:       GET_DOCS
   PURPOSE:    Pulls associated docs from profile table
******************************************************************************/


   CURSOR GETDOCINFO IS SELECT DOCNUMBER, DOCNAME, CUSTOM_STATUS, DOCUMENTTYPES.DESCRIPTION, LAST_EDIT_TIME
        FROM PROFILE, DOCUMENTTYPES, FORMS WHERE NAD_APID = IN_APID AND PROFILE.FORM = FORMS.SYSTEM_ID AND 
        DOCUMENTTYPE = DOCUMENTTYPES.SYSTEM_ID AND FORM_NAME = 'DOCS_PROFILE' ORDER BY DOCNUMBER;

BEGIN

    OPEN GETDOCINFO;
        --GET THE FIRST RECORD
        FETCH GETDOCINFO INTO DOCNUMBER, DOCNAME, STATUS, DOCTYPE, EDITDATE;
        --LOOP THROUGH ALL ASSOCIATED DOCS AND GRAB INFO
        WHILE GETDOCINFO%FOUND LOOP

            BEGIN

                DOCSTRING := DOCNUMBER || '|~|' || DOCNAME || '|~|' || STATUS || '|~|' || DOCTYPE || '|~|' || WS_EDITDATE;

                RETVAL := RETVAL || DOCSTRING || '|^|';

                GOTO STARTOVER;

            END;

            <<STARTOVER>>

            FETCH GETDOCINFO INTO DOCNUMBER, DOCNAME, STATUS, DOCTYPE, EDITDATE;

        END LOOP;

   CLOSE GETDOCINFO;

   RETURN RETVAL;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging

 the error and then re-raise
       RAISE;
END GET_DOCS;
/

Joshua · Accepted Answer · 2011-11-14 15:48:34Z

4

Well, you could convert the function in to a procedure and have an OUT parameter of the SYS_REFCURSOR type. With Oracle and .Net you can pass back a cursor and iterate through that as a reader.

Sample Oracle procedure:

CREATE OR REPLACE PROCEDURE TEST_SCHEMA.TEST_PROCEDURE (
  out_DATA OUT SYS_REFCURSOR;
) AS
BEGIN
  OPEN out_DATA FOR
  SELECT col1,
         col2
    FROM TEST_SCHEMA.TEST_TABLE;
END test_procedure;

Sample .Net end:

using (OracleConnection connection = new OracleConnection("connstring"))
using (OracleCommand command = connection.CreateCommand()) {
  command.CommandType = CommandType.StoredProcedure;
  command.CommandText = "TEST_SCHEMA.TEST_PROCEDURE";
  command.Parameters.Add("out_DATA", OracleType.Cursor)
      .Direction = ParameterDirection.Output;

  connection.Open();
  command.ExecuteNonQuery();
  OracleDataReader reader = 
      command.Parameters["out_DATA"].Value as OracleDataReader;

  if (reader != null) {
    using (reader) {
      while(reader.Read()) {
        string col1 = reader["col1"] as string;
        string col2 = reader["col2"] as string;
      }
    }
  }
}

Be sure to close the cursor after you're done using it (accomplished above by the using (reader) statement).

So in your case, you could probably create a procedure that outputs the original cursor in your function, then just iterate over the cursor in .Net as listed above. Just a note, the column names from the Oracle side are important and will match what you're using in .Net.

edited Nov 14, 2011 at 15:48

answered Nov 14, 2011 at 15:41

Joshua

8,2123 gold badges37 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gsirianni Over a year ago

How would I call this using an openquery command from SQL Server

gsirianni · Accepted Answer · 2011-11-14 17:52:31Z

0

What I have so far compiles fine.

CREATE OR REPLACE PROCEDURE DOCSADMIN.GET_DOCS_SP  ( IN_APID IN NUMBER,   out_DATA OUT SYS_REFCURSOR ) 

AS 
BEGIN   

OPEN out_DATA FOR   

SELECT DOCNUMBER, DOCNAME, CUSTOM_STATUS, DOCUMENTTYPES.DESCRIPTION, LAST_EDIT_TIME
        FROM PROFILE, DOCUMENTTYPES, FORMS WHERE APID = IN_APID AND PROFILE.FORM = FORMS.SYSTEM_ID AND 
        DOCUMENTTYPE = DOCUMENTTYPES.SYSTEM_ID AND FORM_NAME = 'PROFILE' ORDER BY DOCNUMBER;

END GET_DOCS_SP;

/

However, I've run into another situation and would appreciate your input. If I wanted to call the following from a sql database using OPENQUERY how would I do so? The legacy version that was returning the concatenated string looked like the following.

SELECT * FROM OPENQUERY (TESTSERVER, 'SELECT DOCSADMIN.GET_DOCS_SP (26) AS DOCINFO FROM DUAL')

Do I just remove the as DOCINFO FROM DUAL clause?

Thanks

answered Nov 14, 2011 at 17:52

gsirianni

1,3642 gold badges18 silver badges35 bronze badges

2 Comments

Joshua Over a year ago

I've never tried using OPENQUERY before, you may be able to use a wrapper (this is off the hip):

CREATE PROCEDURE DOCSADMIN.GET_DOCS_SP_OPENQUERY (IN_APID IN NUMBER) RETURNS SYS_REFCURSOR AS proc_result SYS_REFCURSOR; BEGIN DOCSADMIN.GET_DOCS_SP(IN_APID, proc_result); RETURN proc_result; END GET_DOCS_SP_OPENQUERY;

then from SQL SELECT * FROM OPENQUERY(TESTSERVER, 'SELECT DOCSADMIN.GET_DOCS_SP_OPENQUERY(26) AS DOCINFO FROM DUAL') That was written on the fly and may not even work.

gsirianni Over a year ago

I appreciate your help very much. I think the return value will work much better than the out parameter. Seems similar to C# syntax.

Collectives™ on Stack Overflow

Returning a recordset from Oracle to .Net using an Oracle function that takes arguments

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related