RqAttrValuesView_244, and ORA-00942: table of view does not exist, errors when working with user views in RequisitePro

Resolving The Problem

DISCLAIMER:

All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

To resolve this issue, delete and recreate all the Oracle views associated with the RequisitePro schema:

1. Have the database administrator grant the ReqPro database user the CREATE ANY VIEW privilege.
   The dba will need to execute the command:
   
GRANT CREATE ANY VIEW TO <username>;

   
   The <username> should be the name of the user that owns the RequisitePro schema. If the create_reqpro.sql script was not changed to use a different USER, the default user is reqpro.
   The command would then be:
   
GRANT CREATE ANY VIEW TO reqpro;

2. Once you have the permission to create any view, you will need to create the Oracle stored procedures necessary to delete and recreate the Oracle views for RequisitePro. The script 10370_viewproc.sql attached to this solution includes the commands that can be used to create these stored procedures.
   
   The text of the script is also included at the bottom of this solution between the delimiters REM - BEGIN FILE and REM - END FILE. If you experience problems downloading the 10370_viewprocs.sql attachment create the script manually by cutting a pasting the text at the bottom of this solution into a text editor such a notepad.
3. Download and place the 10370_viewprocs.sql file into a directory that is easy to remember and doesn't have spaces in the path, for example:
   
c:\temp\10370_viewprocs.sql.

4. Connect to the database with SQL*Plus as the RequisitePro database user and execute the following command:
   
@<path to file> \10370_viewprocs.sql

   
   For example:
   
@c:\temp\10370_viewprocs.sql

   
   You should see the message Procedure created. repeated 7 times.
5. While still connected in SQL*Plus as the RequisitePro database user delete the existing views by using the command:
   
exec rqdrop_all_views;

   
   The message PL/SQL procedure successfully completed. will be displayed when the stored procedure finishes.
6. Recreate the views dropped in step 4. by executing the following command:
   
exec rqrepair_views;

   
   The message PL/SQL procedure successfully completed. will be displayed when the stored procedure finishes.
   
   Valid views have now been created for RequisitePro.

Note: The above solution is also useful when importing RequisitePro data back into an Oracle instance. After importing data using the Oracle import utility RequisitePro's Oracle views may show as being invalid because Oracle restores the views before the schema integrity constraints are enabled. Deleting and recreating the views using the information in this solution will remedy this problem.



REM - BEGIN FILE viewprocs.sql
CREATE OR REPLACE PROCEDURE RQCREATE_VIEW_EDIT
(id integer, dataType integer) is
cid     integer;
function_txt varchar2(30);
create_txt  varchar2(100);
select_txt  varchar2(100);
from_txt   varchar2(100);
where_txt  varchar2(100);
sql_txt   varchar2(400);
id_str    varchar2(10);
user_str   varchar2(50);
BEGIN
SELECT user INTO user_str FROM dual;
id_str := TO_CHAR(id);
IF dataType = 1 THEN
function_txt := user_str || '.RQ_STR_TO_STRING';
ELSE
function_txt := user_str || '.RQ_STR_TO_NUMBER';
END IF;
create_txt := 'CREATE VIEW RQATTRVALUESVIEW_' || id_str || ' AS ';
select_txt := 'SELECT RequirementID, ' || function_txt || '(FieldValue) AS FieldValue ';
from_txt := 'FROM ' || user_str || '.RQUSERDEFINEDFIELDVALUES ';
where_txt := 'WHERE FieldID = ' || id_str;
sql_txt := create_txt || select_txt || from_txt || where_txt;
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, sql_txt, dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
CREATE OR REPLACE PROCEDURE RQCREATE_VIEW_LIST
(id integer, dataType integer) is
cid     integer;
create_txt  varchar2(100);
select_txt  varchar2(200);
where_txt  varchar2(100);
sql_txt   varchar2(400);
id_str    varchar2(10);
user_str   varchar2(50);
BEGIN
SELECT user INTO user_str FROM dual;
id_str := TO_CHAR(id);
create_txt := 'CREATE VIEW RQATTRVALUESVIEW_' || id_str || ' AS ';
select_txt := 'SELECT RequirementID, ListItemID, ItemRank FROM ' || user_str || '.RqUserDefinedListValues LV, ' || user_str || '.RqUserDefinedListItems LI ';
where_txt := 'WHERE LV.FieldID = LI.FieldID AND ListItemID = ItemID AND LV.FieldID = ' || id_str;
sql_txt := create_txt || select_txt || where_txt;
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, sql_txt, dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
CREATE OR REPLACE PROCEDURE RQCREATE_VIEW_SORT
(id integer, dataType integer) is
cid     integer;
create_txt  varchar2(100);
select_txt  varchar2(200);
where_txt  varchar2(200);
sql_txt   varchar2(500);
id_str    varchar2(10);
user_str   varchar2(50);
BEGIN
SELECT user INTO user_str FROM dual;
id_str := TO_CHAR(id);
create_txt := 'CREATE VIEW RQATTRVALUESSORTVIEW_' || id_str || ' AS ';
select_txt := 'SELECT RequirementID, Min(ListItemID) AS ListItemID, Min(ItemRank) AS ItemRank FROM ' || user_str || '.RqUserDefinedListValues LV, ' || user_str || '.RqUserDefinedListItems LI ';
where_txt := 'WHERE LV.FieldID = LI.FieldID AND ListItemID = ItemID AND LV.FieldID = ' || id_str;
where_txt := where_txt || ' GROUP BY RequirementID';
sql_txt := create_txt || select_txt || where_txt;
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, sql_txt, dbms_sql.v7);
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
CREATE OR REPLACE PROCEDURE RQCHECK_VIEW
(id integer, dataType integer) is
cid   integer;
sql_txt varchar2(100);
v_count number;
ignore  integer;
id_str  varchar2(10);
view_name varchar2(50);
BEGIN
id_str := TO_CHAR(id);
view_name := 'RQATTRVALUESVIEW_' || id_str;
sql_txt := 'SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME = :vn';
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, sql_txt, dbms_sql.v7);
DBMS_SQL.BIND_VARIABLE(cid, ':vn', view_name);
DBMS_SQL.DEFINE_COLUMN(cid, 1, v_count);
ignore := DBMS_SQL.EXECUTE(cid);
LOOP
IF DBMS_SQL.FETCH_ROWS(cid)>0 THEN -- get column values of the row
DBMS_SQL.COLUMN_VALUE(cid, 1, v_count);
IF v_count = 0 THEN
IF dataType = 0 OR dataType = 10 THEN
RQCREATE_VIEW_LIST(id, dataType);
ELSE
RQCREATE_VIEW_EDIT(id, dataType);
END IF;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
IF dataType = 10 THEN
RQCHECK_VIEW_SORT(id, dataType);
END IF;
COMMIT;
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
CREATE OR REPLACE PROCEDURE RQCHECK_VIEW_SORT
(id integer, dataType integer) is
cid   integer;
sql_txt varchar2(100);
v_count number;
ignore  integer;
id_str  varchar2(10);
view_name varchar2(50);
BEGIN
id_str := TO_CHAR(id);
view_name := 'RQATTRVALUESSORTVIEW_' || id_str;
sql_txt := 'SELECT COUNT(*) FROM USER_VIEWS WHERE VIEW_NAME = :vn';
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, sql_txt, dbms_sql.v7);
DBMS_SQL.BIND_VARIABLE(cid, ':vn', view_name);
DBMS_SQL.DEFINE_COLUMN(cid, 1, v_count);
ignore := DBMS_SQL.EXECUTE(cid);
LOOP
IF DBMS_SQL.FETCH_ROWS(cid)>0 THEN -- get column values of the row
DBMS_SQL.COLUMN_VALUE(cid, 1, v_count);
IF v_count = 0 THEN
RQCREATE_VIEW_SORT(id, dataType);
END IF;
ELSE
EXIT;
END IF;
END LOOP;
COMMIT;
DBMS_SQL.CLOSE_CURSOR(cid);
END;
/
CREATE OR REPLACE PROCEDURE RQREPAIR_VIEWS
is
attrId integer;
dataType integer;
CURSOR c1 IS
SELECT Id, dataType
FROM RqUserDefinedFields;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO attrId, dataType;
EXIT WHEN c1%NOTFOUND;
RqCheck_View(attrId, dataType);
END LOOP;
CLOSE c1;
END;
/

CREATE OR REPLACE PROCEDURE RQDROP_ALL_VIEWS
is
attrId integer;
CURSOR c1 IS
SELECT Id FROM RqUserDefinedFields;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO attrId;
EXIT WHEN c1%NOTFOUND;
RqDrop_View(attrId);
END LOOP;
CLOSE c1;
END;
/
REM - END FILE viewprocs.sql