Attempting to install utPLSQL 3.1.10

Question

I have downloaded utPLSQL.zip from github, unzipped the archive, opened the user guide bundled with the zip, and have started following along with the manual installation process.

I'm using Oracle 18c XE, SQL Developer 20.2, and utPLSQL 3.1.10. I'm using sqlplus to execute scripts, logging in using the command: sqlplus '/ as sysdba'

The first three scripts run fine:

@create_utplsql_owner.sql <schema name> <password> users;
@install.sql <schema name>;
@install_ddl_trigger.sql <schema name>

The fourth script:

@create_synonyms_and_grants_for_public.sql <schema name>;

Fails with the exception: Creating synonyms for UTPLSQL objects in schema to user PUBLIC create public synonym ut for .ut * ERROR at line 1: ORA-00955: name is already used by an existing object

Has anyone run into this? I tried reverting to utPLSQL 3.1.9 but ran into the same issue...

Thanks!

Error is pretty clear. drop the synonym before running the script. — OldProgrammer, Sep 01 '20 at 17:58

score 0 · Answer 1 · answered Sep 01 '20 at 21:58

My first installation attempt of utPLSQL had failed. I deleted the schema which was intended to act as the test repository, and that is when this particular problem started.

Digging into utPLSQL installation scripts and some Oracle documentation I found that deleting a schema does not remove said schema's public synonyms from the table DBA_SYNONYMS.

Running the install script: install.sql creates several synonyms where is the owner.

Running the install script: create_synonyms_and_grants_for_public.sql creates a load of public synonyms.

Dropping the schema to reset and resinstall utPLSQL only deletes the synonyms where is the owner -- the public synonyms have to be manually deleted. This script does the job and allows a clean installation to take place:

declare
    sqq varchar2(250);
    v_code varchar2(250);
    v_errm varchar2(250);

    cursor del_syns is
        SELECT synonym_name
        FROM DBA_SYNONYMS
        WHERE table_owner = 'C##UNIT_TEST_REPOSITORY';

begin

  FOR syn_name IN del_syns
  LOOP
    sqq := 'drop public synonym ' || syn_name.synonym_name || ' force';
    dbms_output.put_line(sqq);
    execute immediate sqq;
  END LOOP;

EXCEPTION
    when others then
        v_code := SQLCODE;
        v_errm := SUBSTR(SQLERRM, 1, 64);
        DBMS_OUTPUT.PUT_LINE ('------- Error code ' || v_code || ': ' || v_errm);

end;

Attempting to install utPLSQL 3.1.10

1 Answers1