Pycharm show test result statistics (Passed/Failed)

An incredibly trivial issue that I’m posting here just as a reminder, as it cost me about an hour of frustration.

New to Pycharm and running tests I noticed that I didn’t appear to have the right-most panel (the Statistics Pane) as in all the examples I had seen:


Took me forever to finally find the option to enable statistics by clicking on the blue gear, mostly because I had no idea what the panel was even called until I reviewed the PyCharm docs.


ORA-00980: synonym translation is no longer valid

Recently ran into an issue where a query, which worked when run directly, failed with the above error when placed in a PL/SQL procedure.

The set up was basically:

Server A:


SCHEMA2: No objects, but select on SCHEMA1.TABLE1

Public synonym TABLE1 for SCHEMA1.TABLE1

Server B:

DBLINK1: Public DB Link to Server A using SCHEMA2 credentials


PROCEDURE1: contained a select against SYNONYM2

So when we would query SYNONYM2, it would resolve to TABLE1@DBLINK1, which in turn (using the remote public synonym TABLE1->SCHEMA1->TABLE1) would resolve to SCHEMA1.TABLE1@DBLINK1.

This worked fine in a query outside of PL/SQL, but once added to a procedure we would get the ORA-00980: synonym translation is no longer valid error.  After some digging found that this is a known issue – [ID 453754.1].  Basically this simply will not work, it sounds more or less like yet another limitation of PL/SQL – you cannot query a synonym which in turn queries another synonym across a DB link.  Instead, you must fully qualify the second synonym (so in our case, instead of creating the local synonym as SYNONYM2 for TABLE1@DBLINK1 we created SYNONYM2 for SCHEMA1.TABLE1@DBLINK1).

Naturally, although this issue has been around forever, Oracle claims it is ‘not a bug’.

The case of the mysterious

Just  quick note about a problem I ran into recently.  A previously healthy PHP application suddenly started failing with the following error:

However, nothing had changed on either the application or database side.  The DB was up and healthy, and the connection string from the php application appeared correct.  Checking tnsping on the client, I realized that for some reason the DB service name (ORCL.WORLD) was no longer resolving to the DBServer1 host, but instead was resolving to IP

While I was familiar with the conventional loopback address, 53.53 was not something I’d seen before.  A bit of investigation led to this:

But why did this cause my app to fail?  The culprit was my sqlnet.ora, which listed

What happened was previously, when Oracle attempted to resolve ORCL.WORLD it would first invoke EZConnect, receiving an NXDOMAIN (not a domain) as a response – because of this failure it would then utilize the tnsnames file and correctly resolve the address.  However, now when using EZConnect Oracle receives a response ( and, thinking that it has located the server, attempts to connect.

Swapping EZCONNECT and TNSNAMES (so that TNSNAMES is queried first) resolved the issue.

Executing Oracle functions and procedures in SQLPlus and PL/SQL

Just a quick TIL to reinforce my memory on this point.

I often come across code snippets online which take the form

Usually my first reaction to this is one of confusion – why is there a colon before test (is it a bind variable?) – why do I need to set test equal to a value if I don’t intend to use it?  Furthermore, if I attempt just

I get errors stating that the wrong number of parameters were used – which always strikes me as odd, given that it has the exact same parameters as the first example (which runs successfully).


Some notes:

  • In this case DBMS_SPM.LOAD_CURSOR_FROM_CACHE is a function (not a stored procedure) – for functions, you must either call the function like
    • select function(arguments) from dual
    • provide a variable to catch the returned value

in SQLPlus

using anonymous PL/SQL block

Note that stored procedures do not require a variable to catch a return.

EM 12c – find a user / schema in all databases

When deprovisioning users (for reasons of employment termination, consulting engagement completion, etc.) we must often lock or completely remove user accounts in potentially numerous databases.

If these databases are tracked within Enterprise Manager Cloud Control 12c, there is a fairly handy view that drastically improves this process, namely the SYSMAN.MGMT$DB_USERS view in your Grid Repository database



Oracle user privileges on directory

Keep on forgetting this fact, so I’m committing it to the internet’s memory (as mine is suspect).

To view user privileges on a directory, use the dba_tab_privs view.  As this is Oracle, of course it would be ridiculous to expect that the “tab” in the view stands for table…  One would almost think there would be a dba_obj_privs (as with dba_obj_audit_opts) but of course not.  Don’t let the column name ‘TABLE_NAME’ fool you either, TMPDIR below is actually just a directory.




Creating a DSN on Windows to Oracle 11

Fairly simple process, but wanted to note it for future.

First, downloaded the Oracle InstantClient (Basic) and InstantClient ODBC packages:


Then created a folder (C:\oracle_instantclient) and extracted the two downloaded zip files inside the folder.  Both will create and extract into a folder named instantclient_11_2.

While here, I also created a tnsnames folder for steps below.


Inside the instantclient_11_2 folder I ran odbc_install.exe – Oracle could do a better job of feedback, as all I saw was a flash of a command prompt, but whatever…

Then in Environment Variables I added C:\oracle_instantclient\instantclient_11_2 to the PATH variable, and created a TNS_ADMIN variable pointing at my tnsnames folder (C:\oracle_instantclient\tnsnames).

path_variable tnsnames_variable

I created a tnsnames.ora file inside of the tnsnames directory, and added a tns entry



I’m using the 32 bit client, so instead of using the default ODBC administrator, I launch the 32 bit version from C:\Windows\SysWOW64\odbcad32.exe.


I can now see and use the ODBC driver for Oracle:






Altering columns in SQL Server and Oracle

While a great deal of DDL is common across these two platforms, altering individual columns varies somewhat:

SQL Server





Setting the editor in SqlPlus

By default the “ed” command in SqlPlus invokes the corresponding default editor in Linux.

Exit with “q”

A better solution is to set the editor in SqlPlus – in this case I’m using “vi” as my editor of choice:

Once done, invoking “ed” places you into a vi edit – the saved file can then be run via the usual:

To persist this setting, it can be placed in glogin.sql or login.sql



« Older Entries