Example: Reading a set of records from an Oracle stored procedure.


// Oracle stored procedure we wish to test
// for more information on this example, see the following Oracle documentation
#if 0
 Create or replace package ExampleInfo as

   Type ExampleRec is record

   (
   INT_VALUE     integer,

   STRING_VALUE   varchar2(50)

   );

   Type ExampleCursor is ref cursor return 

   ExampleRec;

   End ExampleInfo;

   

   Create or replace procedure ExampleInfoProc

   (LONG_CRITERIA IN integer, empcursor IN OUT 

   ExampleInfo.ExampleCursor)

   As

   Begin

   Open empcursor For

   select INT_VALUE, STRING_VALUE 

   from db_example

   where EXAMPLE_LONG = LONG_CRITERIA;

   End;
#endif

class ProcBCA {
public:
 void operator()(BoundIOs &cols, variant_row &row)
 {
  cols["INT_VALUE"] >> row._int();
  cols["STRING_VALUE"] >> row._string();
  
  cols.BindVariantRow(row);
 }
};

class ProcParams {
public:
	long long_criteria;
};

class ProcBPA {
public:
 void operator()(BoundIOs &cols, ProcParams &row)
 {
  cols[0] << row.long_criteria;
 }
};

// Read the contents of a table and print the resulting rows
// *** you must have Oracle ODBC driver version 8.1.5.3.0 for this to work ***
// The reason why is that the stored procedure shown above returns a "cursor"
// to pass back multiple rows which is only supported in Oracle ODBC driver
// version 8.1.5.3.0 or higher.  (Merant drivers for Oracle also support this syntax.)
void StoredProcReadData() {

        // Note: ExampleInfoProc takes an integer as an input parameter to determine
        // what records to return, and returns a cursor to a set of rows as an
        // output parameter. The output cursor is bound implicitly as a set of columns.
	DBView<variant_row, ProcParams> view("{call ExampleInfoProc(?)}", 
		ProcBCA(), "", ProcBPA());

	variant_row s(view.GetDataObj());

	// Print out the column names
	vector<string> colNames = s.GetNames();
	for (vector<string>::iterator name_it = colNames.begin(); name_it != colNames.end(); ++name_it)
		cout << (*name_it) << " ";
	cout << endl;

	// Print out all rows and columns from our query
	DBView<variant_row, ProcParams>::sql_iterator print_it = view.begin();
	print_it.Params().long_criteria = 22;

	for (; print_it != view.end(); ++print_it)
	{
		 variant_row r = *print_it;
		 for (size_t i = 0; i < r.size(); ++i)
		 	cout << r[i] << " ";
		 cout << endl;
	}
}