1

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 12.1.0.2 Enterprise.

Thanks

  • Which user are you using to make JDBC connection? – JSapkota Feb 20 '17 at 9:03
  • If you are pulling synonym name dynamically, why not owner too? In my case of export,import I prefer to drop the user to ensure the slate is clean, but ymmv. – Raj Feb 20 '17 at 13:48
  • @JSapkota "dbauser", same user I use in sql developer which works. Log output: 2017-02-18 22:28:48,253 Current user: DBAUSER – Shawn Feb 20 '17 at 20:30
  • @Raj not my script, it comes with an enterprise product. Need to use what provided, otherwise upgrade path may be broken and this system will be non-contiguous with rest of the systems the provided script works on. – Shawn Feb 20 '17 at 20:30
  • @Raj For the heck of it I did what you suggested to see the results, same results: Creates this statement: DROP SYNONYM POS.CAT_AWARD_ACCT_COUPON .... with these results...-1434 ORA-01434: private synonym to be dropped does not exist....script snippet: sqlstmt := 'DROP SYNONYM ' || ls_synonym_owner || '.'|| ls_synonym_name; – Shawn Feb 20 '17 at 21:03

Your Answer

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

Browse other questions tagged or ask your own question.