1

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.' enter image description here

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' enter image description here

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.