Can I return ref cursor from plsql function in oracle using SQL Toolkit 2.1.0?

[dandugi]

Is there a way to return a ref cursor from a stored function or procedure and iterate through it?  I have defined the simple package in Oracle below, and want to call the function or procedure and iterate through the rows returned.

I'm using LabWindows/CVI 8.5.0, with SQL Toolkit 2.1.0 on Windows XP, connecting to a 10.2.0.4.0 Oracle instance running on another server.

 

CREATE OR REPLACE PACKAGE emp_package IS  TYPE

type_ref_cursor IS REF CURSOR; 

  FUNCTION get_emp_func RETURN type_ref_cursor; 

  PROCEDURE get_emp_proc (empcursor OUT type_ref_cursor);

END;

/

CREATE OR REPLACE PACKAGE BODY emp_package IS

FUNCTION get_emp_func RETURN type_ref_cursor

  IS

    tmp_cur type_ref_cursor;

  BEGIN

    OPEN tmp_cur FOR SELECT * FROM emp;

    RETURN tmp_cur;

END;

PROCEDURE get_emp_proc (empcursor OUT type_ref_cursor)

  IS

    tmp_cur type_ref_cursor;

  BEGIN     OPEN

tmp_cur FOR SELECT * FROM emp;

    empcursor := tmp_cur;

  END;

END;

/

[Adri_K.]

Hi,

The LabWindows/CVI SQL Toolkit Help Lists SQL Statement Attributes that can be used. One that is mentioned is, ATTR_DB_STMT_CURSOR_TYPE which returns a cursor type. One cursor type that could be returned is DB_CURSOR_TYPE_DYNAMIC which will make additions, changes, and deletions by other users visible, and all types of movement through the recordset are allowed.

You can also use ATTR_DB_STMT_RECORDSET_OBJECT to obtain an ActiveX object handle of the recordset.

There is limited example code of doing this in LabWindows/CVI, but there are a few discussion forums that discuss this being done in LabVIEW. Please take a look at this forum as well as KnowledgeBase 4AJCSSLC: How Do I Set Input Parameters and Get Output Parameters from an SQL Stored P....

Regards,
Adri Kruger

[dandugi]

I've spent a few days testing different routes.  I finally figured out a way how to call the function and iterate through the returned cursor:

int test(const int hdbc)
{
 int resCode;
 int hstmt = 0;        
 int i=0;
 char col1[101]="",col2[101]="";
 int stat1=0,stat2=0;
 
 // Set the command type attribute to store procedure. 
 resCode = DBSetAttributeDefault (hdbc, ATTR_DB_COMMAND_TYPE,DB_COMMAND_STORED_PROC);

 // Prepare a statement that calls the stored procedure.
 hstmt = DBPrepareSQL (hdbc, "emp_package.get_emp_func");

 // Set command type attribute back to default.
 resCode = DBSetAttributeDefault (hdbc, ATTR_DB_COMMAND_TYPE,DB_COMMAND_UNKNOWN);

 // Bind columns

 resCode = DBBindColChar (hstmt, 1, 100, col1, &stat1, ""); 

 resCode = DBBindColChar (hstmt, 2, 100, col2, &stat2, ""); 
 

 // Execute the statement.       
 resCode = DBExecutePreparedSQL (hstmt);
 

 // Loop thru columns in cursor
 while ((resCode = DBFetchNext (hstmt)) == DB_SUCCESS) {
  printf("col1=%s col2=%s",col1,col2);
 }

 // Close the statement.
 resCode = DBClosePreparedSQL (hstmt);

 hstmt = DBDeactivateSQL (hstmt);
 return 0;
}