1

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!

2 Answers2

0

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;
0

Please follow these instructions for installing utPLSQL 3.1.10 on your system.

  1. After unzipping folder, go to folder called SOURCE and open create_utplsql_owner.sql in SQL Developer(You can simply drag that file to already opened SQL Developer Worksheet and user schema SYSDBA). You can press F5 and script will execute following code and ask you to insert name of new schema owner of utPLSQL, it is default to type ut3, second input is password (ut3) and third is for tablespace: default is user.
  2. Go to install.sql file, drag to SQL Developer and just press F5
  3. create_synonyms_and_grants_for_public.sql this script will allow you to share framework along to the other users, if you want to run utPLSQL on another user schema you can use create_user_grants.sql and create_user_synonyms (first parameter is owner UT3, second is user which can execute framework functionality like UT3 user).
  4. After installing framework, connect to UT3 schema and write this code to check if framework is successfully installed. -- Test exec ut.run(); if you see something like procedure is successfully completed, and you can not see real output, that problem is solved by setting set serveroutput on;

I hope that my explanation is helpful, please write feedback or ask if you have any misconceptions :)

LukaS
  • 26
  • 1