I am debugging an application that is working correctly on many PCs but not on one particular one.
The part of the application that is having an issue is an automated restore using dpimp from another machines backup which used expdp.
I am seeing two issues, the most concerning is in the function where it drops objects and synonyms in preparation for the schema restore.
The function queries all synonyms for synonyms owned by a particular user then loops through the results dropping each synonym.
The function logs each sql statement that it is about to execute to a log.
Here is a snippet:
CURSOR SYNONYM_LIST (v_owner VARCHAR2) IS SELECT synonym_name FROM all_synonyms WHERE owner = upper('pos') AND table_name != 'SP_IMPORT_DATABASE' AND table_name != 'SP_WRITE_DBMS_OUTPUT_TO_FILE' AND table_owner = v_owner;
...then later on:
IF upper(argProd) = 'PRODUCTION' THEN BEGIN OPEN SYNONYM_LIST (argTargetOwner); LOOP FETCH SYNONYM_LIST INTO ls_synonym_name; EXIT WHEN SYNONYM_LIST%NOTFOUND; sqlstmt := 'DROP SYNONYM pos.'|| ls_synonym_name; dbms_output.put_line (sqlstmt); IF sqlStmt IS NOT NULL THEN EXECUTE IMMEDIATE sqlStmt; END IF; END LOOP; CLOSE SYNONYM_LIST; sp_write_dbms_output_to_file('SP_IMPORT_DATABASE'); EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1, 64); DBMS_OUTPUT.PUT_LINE (v_code || ' ' || v_errm); select user into cur_user from dual; DBMS_OUTPUT.PUT_LINE('Current user: ' || cur_user); sp_write_dbms_output_to_file('SP_IMPORT_DATABASE'); CLOSE SYNONYM_LIST; END; END IF;
Here is output of the script to the log:
2017-02-18 22:28:48,160 DBAUSER is starting SP_IMPORT_DATABASE. 2017-02-18 22:28:48,253 DROP SYNONYM pos.CAT_AWARD_ACCT_COUPON 2017-02-18 22:28:48,253 -1434 ORA-01434: private synonym to be dropped does not exist 2017-02-18 22:28:48,253 Current user: DBAUSER
The issue is this works on many other machines and this machine in particular is causing problems.
If I take the output SQL in the log, "drop synonym..." and run it in either sqlplus or sqldeveloper verbatim, as the same user (DBAUSER), I can drop the synonym no problem.
I should note, this function is called through a Java program on a JDBC connection.
I have also thought that maybe there was a case-sensitive issue, and tried quoting the "pos" as "POS" and "pos" as well as quoting the synonym name, but this results in no difference in the function. When I take that quoted sql and run in sqldeveloper, it works the same as sit always does in sql developer.
We also have a Windows WIM image that the PCs are built from. We decided to re-image this PC as we could not figure out the error. We re-imaged the PC to new hardware and the exact same issue came up. -To be noted is that there is a process after applying the WIM which changes the hostnames/data in tables/etc to correspond with the location this PC is at, so all PCs are not "identical" in the end, but should have identical setups.
Note these systems are using Oracle 12c 22.214.171.124 Enterprise.