Default search path for SQL*Plus does not match Oracle documentation

[vectro]

According to Oracle documentation,

The SQL*Plus executable is usually installed in $ORACLE_HOME/bin

However, we just look in $ORACLE_HOME:

sqitch/lib/App/Sqitch/Engine/oracle.pm

Line 81 in afe87ce

file( ($ENV{ORACLE_HOME} || ()), 'sqlplus' )->stringify

I guess if this is changed then a change would also be required to docker-sqitch since that seems to work currently.

[theory]

Yeah if you just unzip the package there is no bin directory:

unzip instantclient-sqlplus-linux.x64-21.9.0.0.0dbru.zip 
Archive:  instantclient-sqlplus-linux.x64-21.9.0.0.0dbru.zip
  inflating: instantclient_21_9/glogin.sql  
  inflating: instantclient_21_9/libsqlplusic.so  
  inflating: instantclient_21_9/libsqlplus.so  
  inflating: instantclient_21_9/sqlplus  
  inflating: instantclient_21_9/SQLPLUS_LICENSE  
  inflating: instantclient_21_9/SQLPLUS_README  

[vectro]

I guess that Instant Client and ORACLE_HOME Client are different things, per https://www.oracle.com/database/technologies/faq-instant-client.html

Definitely not an expert here, but maybe it's possible that docker-sqitch should not be relying on ORACLE_HOME but rather just PATH?

[theory]

I would defer to people who use Oracle regularly, but TBH I've never seen a standard location for any of this stuff. Hence ORACLE_HOME. That FAQ is confusing af.

[theory]

Definitely not an expert here, but maybe it's possible that docker-sqitch should not be relying on ORACLE_HOME but rather just PATH?

Not sure how that'd change anything, unless someone is passing ORACLE_HOME to docker run, which seems silly.

[vectro]

Definitely not an expert here, but maybe it's possible that docker-sqitch should not be relying on ORACLE_HOME but rather just PATH?

Not sure how that'd change anything, unless someone is passing ORACLE_HOME to docker run, which seems silly.

Well, I guess that I meant is that if Sqitch were changed to look in the path recommended by Oracle for SQL*Plus ($ORACLE_HOME/bin), then the sqitch-oracle Docker image wouldn't be providing the right ORACLE_HOME.

[theory]

To change it, we'd have to:

• In the Docker image, move the files output from instantclient-sqlplus-linux.x64-$version.zip into a bin subdirectory of ORACLE_HOME, rather than just in ORACLE_HOME itself.
• Update paths to point to this location
• Consider modifying oracle.pm to also look in the bin subdirectory of ORACLE_HOME

After which it would all work pretty much as it did before. I think in the absence of someone complaining that it's wrong and input from an experienced Oracle person declaring what's the right way to do it and what's not, I'm inclined to leave it as-is. In 10+ years I don't recall it being an issue.

[vectro]

@theory Or just set PATH to point to the directory where the sqlplus binary is located, and don't depend on ORACLE_HOME to find sqlplus in the Docker image?

The issue that I'm seeing is that in a full Oracle install (i.e., not the Sqitch docker image / not the instant client), Sqitch is not finding SQL*Plus because the sqlplus binary is in $ORACLE_HOME/bin.

[theory]

I think it would make sense to change oracle.pm to default to sqlplus in the path and, if it doesn't exist, to look in ORACLE_HOME and ORACLE_HOME/bin for it.

[vectro]

Seems reasonable!

[theory]

Something like this:

if ($ENV{ORACLE_HOME}) {
    my $bin = file $ENV{ORACLE_HOME}, 'bin', 'sqlplus';
    return $bin if -x $bin;
    $bin = file $ENV{ORACLE_HOME}, 'sqlplus';
    return $bin if -x $bin;
}
return file 'sqlplus';