Thanks @davidski for the link, it's definitely a great start. But unfortunately, it hasn't worked for me.
Setting aside my worry that I'm derailing my own thread into a Mac-specific workflow, from my extensive Googling, it seems that we also need to handle the @rpath issue, which is that the libsqora.dylib uses @rpath to locate dependent libraries, but R apparently doesn't follow these paths 
. This RStudio community post gives some hints to use install_name_tool to change the relative paths to specific locations.
I've followed both @davidski's recommendation and @seanw's answer in the linked post. I've also played with DYLD_LIBRARY_PATH and have made sure that /usr/local/lib is in that library path. Currently, trying to connect to the DB via odbc inside RStudio causes an immediate "R Session Aborted" fatal error that crashes RStudio (v. 1.2.1114, Build 1122 (2e0f7658)). Attempting to connect via a plain R console session gives me the following error that I suppose brings me back full circle to my original question.
Error: nanodbc/nanodbc.cpp:950: HY000: [Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified