How to set a specific ArcSDE version for tabular reporting?

Question

I have created a multiversioned view in Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production. I can access a specific version using a SQL script in SQL Developer. However, using other tools, I can only access DEFAULT.

The following script works fine:

call sde.version_util.set_current_version('MY_VERSION');
select * from mv_my_view;

Encapsulating this logic inside an Oracle View (something similar to Jay Cummins' answer to this question would be a great solution, but I cannot see a way to put a call inside a view).

In Crystal Reports, I can add SDE.VERSION_UTIL.SET_CURRENT_VERSION as a table, but cannot use it to set the version. If I specify a known good version, I get a Query Engine Error: 'Cannot obtain error message from server.'

In contrast, if I specify a non-existent version (with a typo, for example), I get an 'HY000:[Oracle][ODBC][ORA]ORA-20126: Version x not found... ORA-06512'

How can I set the current version to something other than DEFAULT for tabular reporting purposes - either in an Oracle View, or using Crystal Reports, or using some other tool?

Answer 1

The key is to build a function that returns a result set that can be queried as if it were a table. In the function, include a call to set_current_version (see line 6 below).

SQL> create type emp_obj is object (empno number, ename varchar2(10));
  2  /

Type created.

SQL> create type emp_tab is table of emp_obj;
  2  /

Type created.

SQL> create or replace function all_emps return emp_tab
  2  is
  3     l_emp_tab emp_tab := emp_tab();
  4     n integer := 0;
  5  begin
  6     sde.version_util.set_current_version('ALT_VERSION')
  7     for r in (select empno, ename from emp)
  8     loop
  9        l_emp_tab.extend;
 10        n := n + 1;
 11       l_emp_tab(n) := emp_obj(r.empno, r.ename);
 12     end loop;
 13     return l_emp_tab;
 14  end;
 15  /

Function created.

SQL> create or replace view ALT_VER_VIEW as select empno, ename from table (all_emps);

View ALT_VER_VIEW created.

SQL> select * from alt_ver_view;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7902 FORD
      7934 MILLER