getting the following error when trying to install Data Miner repository
section 1 : error returned
section 2 : script been run
section 3: two rows are returned when I run the follow subset off the script. I think this is causing the issue
==================section 1
ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at line 4
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested
==================section 2
SQL> -- drops all public synonyms where table owner = ODMRSYS
SQL> set serveroutput on
SQL> set verify off
SQL>
SQL> EXECUTE dbms_output.put_line('Drop public synonyms created by ODMRSYS. ' || systimestamp);
Drop public synonyms created by ODMRSYS. 13-JUL-23 03.23.45.485694000 PM +00:00
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 w_synonym varchar2(30);
3 w_owner varchar2(30);
4 w_table_name varchar2(30);
5 countObjects integer := 0;
6 countObjectsDropped integer := 0;
7 countObjectsFailedToDrop integer := 0;
8 sql_text varchar2(256);
9 Dynamic_Cursor integer;
10 dummy integer;
11 v_err_msg VARCHAR2(4000);
12
13 --select owner, synonym_name, table_owner, table_name from DBA_SYNONYMS
14 --where table_owner = 'ODMRSYS'
15 cursor synonym_cursor is
16 select synonym_name, table_owner, table_name from DBA_SYNONYMS
17 where table_owner = 'ODMRSYS'
18 order by synonym_name;
19 BEGIN
20 DBMS_OUTPUT.ENABLE(NULL);
21
22 open synonym_cursor;
23 fetch synonym_cursor into w_synonym,w_owner,w_table_name;
24 while synonym_cursor%FOUND loop
25 BEGIN
26 sql_text := 'drop public synonym ' || w_synonym || ' FORCE' ;
27 DBMS_OUTPUT.PUT_LINE
28 (sql_text );
29 Dynamic_Cursor := dbms_sql.open_cursor;
30 dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
31 dummy := dbms_sql.execute(Dynamic_Cursor);
32 dbms_sql.close_cursor(Dynamic_Cursor);
33 countObjectsDropped := countObjectsDropped + 1;
34 EXCEPTION
35 WHEN OTHERS THEN
36 v_err_msg := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK(), 1, 4000);
37 DBMS_OUTPUT.PUT_LINE ('Drop failed: ' || 'Synonym: "' ||
38 w_synonym || '" Table Name: ' || w_table_name || ' Error: ' || v_err_msg );
39 countObjectsFailedToDrop := countObjectsFailedToDrop + 1;
40 dbms_sql.close_cursor(Dynamic_Cursor);
41 END;
42
43 countObjects := countObjects + 1;
44 fetch synonym_cursor into w_synonym,w_owner,w_table_name;
45 end loop;
46 close synonym_cursor;
47
48 DBMS_OUTPUT.PUT_LINE
49 ('Total Number of Objects: ' || countObjects );
50 DBMS_OUTPUT.PUT_LINE
51 ('Total Number of Objects Dropped: ' || countObjectsDropped );
52 DBMS_OUTPUT.PUT_LINE
53 ('Total Number of Objects Failed to Drop: ' || countObjectsFailedToDrop );
54 END;
55 /
Total Number of Objects: 0
Total Number of Objects Dropped: 0
Total Number of Objects Failed to Drop: 0
PL/SQL procedure successfully completed.
SQL>
SQL> EXECUTE dbms_output.put_line('Finished dropping public synonyms created by ODMRSYS. ' || systimestamp);
Finished dropping public synonyms created by ODMRSYS. 13-JUL-23
03.23.45.521128000 PM +00:00
PL/SQL procedure successfully completed.
SQL> -- dropRole.sql drops the ODMRUSER role
SQL> -- NOTE: ERRORS ARE OK FOR THE DROP ROLE, AS IT CONFIRMS THE ROLE DOES NOT EXIST
SQL> --Drop ODMRSUSER role
SQL> DECLARE
2 v_sql varchar2(100);
3 role_not_exist EXCEPTION;
4 PRAGMA EXCEPTION_INIT(role_not_exist, -1919);
5 BEGIN
6 v_sql := 'drop role ODMRUSER';
7 EXECUTE IMMEDIATE v_sql;
8 DBMS_OUTPUT.PUT_LINE ('drop role ODMRUSER: succeeded ');
9 EXCEPTION
10 WHEN role_not_exist THEN
11 DBMS_OUTPUT.PUT_LINE ('drop role ODMRUSER: failed as ODMRUSER role does not exist ');
12 END;
13 /
drop role ODMRUSER: failed as ODMRUSER role does not exist
PL/SQL procedure successfully completed.
SQL> -- Check DB Version
SQL> DECLARE
2 v_version VARCHAR(30);
3 BEGIN
4 SELECT DISTINCT DB.VERSION INTO v_version FROM PRODUCT_COMPONENT_VERSION DB WHERE DB.PRODUCT LIKE '%Oracle Database%';
5 DBMS_OUTPUT.PUT_LINE('Database version is: ' || v_version);
6 IF( v_version < '11.2.0.4') THEN
7 RAISE_APPLICATION_ERROR(-20000, 'Not Supported Database Version. 11.2.0.4 or above is required.');
8 END IF;
9 EXCEPTION WHEN NO_DATA_FOUND THEN
10 DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND when attempting to query PRODUCT_COMPONENT_VERSION DB.');
11 RAISE_APPLICATION_ERROR(-20000, 'Not Supported Database Version. 11.2.0.4 or above is required.');
12 END;
13 /
Error starting at line : 2 in command -
DECLARE
v_version VARCHAR(30);
BEGIN
SELECT DISTINCT DB.VERSION INTO v_version FROM PRODUCT_COMPONENT_VERSION DB WHERE DB.PRODUCT LIKE '%Oracle Database%';
DBMS_OUTPUT.PUT_LINE('Database version is: ' || v_version);
IF( v_version < '11.2.0.4') THEN
RAISE_APPLICATION_ERROR(-20000, 'Not Supported Database Version. 11.2.0.4 or above is required.');
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND when attempting to query PRODUCT_COMPONENT_VERSION DB.');
RAISE_APPLICATION_ERROR(-20000, 'Not Supported Database Version. 11.2.0.4 or above is required.');
END;
Error report -
ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at line 4
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested
==================section 3
SELECT DISTINCT DB.VERSION FROM PRODUCT_COMPONENT_VERSION DB WHERE DB.PRODUCT LIKE '%Oracle Database%';
23.0.0.0.0
23.0.0.0.0 - Developer-Release