0

I have this very strange observation on my Windows 10x64 machine:

Given the following script file (file name test.sql):

DEFINE SYS_NAME = &1
DEFINE SYS_PWD = &2
DEFINE ORACLE_HOME = &3

CONNECT &SYS_NAME/&SYS_PWD@//localhost:1521/xe AS SYSDBA

SET VERIFY OFF
SET SERVEROUTPUT ON
-- SET FEEDBACK OFF

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

---- enable DBMS_OUTPUT ---------
ALTER SESSION SET CONTAINER=CDB$ROOT;
@&ORACLE_HOME/rdbms/admin/dbmsotpt.sql

---- run SQL scripts ---------
BEGIN
  DBMS_OUTPUT.PUT_LINE('Database script initialization finished.');
  DBMS_OUTPUT.PUT_LINE('');
  END;
  /

DISCONNECT

@aaaaaaaaaaaa

EXIT

When I call this script against my local Oracle DB 18.4 XE Windows installation with appropriate arguments, like:

sqlplus -S /nolog '@C:\Temp\sql\test.sql' sys *** 'C:\app\Me\product\18.0.0\dbhomeXE'

Using SQL*Plus 19.11.0.0.0 I get this output (translated from German):

Session changed.

OS Message: No such file or directory

Using SQL*Plus 18.4.0.0.0 I get this output (again, translated from German):

Session changed.


Session changed.


Type created.


Package created.


Synonym created.


Synonym created.


User access granted.


User access granted.


Session changed.

Database script initialization finished.

PL/SQL Procedure successfully finished.

So, using SQL*Plus 19.11.0.0.0, neither the built-in SQL Script nor my script (@aaaaaaaaaaaa for demonstration purposes) is getting called.

Using SQL*Plus 18.4.0.0.0, the built-in SQL Script seems to be getting called, but my script (@aaaaaaaaaaaa for demonstration purposes) is not getting called.


Why isn't this working?

NB: The same script is working flawlessly when run in an Oracle 18c XE Linux Docker container.

5
  • Have you tried escaping backslashes in the script path? Cf. /rdbms/admin/dbmsotpt.sql and C:\app\Me\product\18.0.0\dbhomeXE.
    – mustaccio
    Jul 20 at 22:20
  • I tried everthing. Luckily, Windows 10 accepts both. I tried addressing an additional dummy script (SELECT 1 FROM Dual;) from anywhere on my hard drive using forward slashes and backslashes. While SQLPlus *18 declines any execution within $ORACLE_HOME (see above), they fixed that with SQLPlus *19. As you can see, I'm not using any path separator with @aaaaa in the example. It simply isn't run. You even don't get an error message (OS Message: No such file or directory). You may copy and try my script. Should run everywhere.
    – AxD
    Jul 21 at 9:00
  • Fun fact: Even the colorizer here at StackExchange is considering my script call a comment. It displayed in dark green above.
    – AxD
    Jul 21 at 9:09
  • On Windows, you can use a tool like Process Monitor (docs.microsoft.com/en-us/sysinternals/downloads/procmon) to confirm whether or not sqlplus is actually reading your @aaaaa script, even if it doesn't do anything with it.
    – pmdba
    Jul 21 at 10:45
  • I already did. Nope, aaaaaaa isn't found in the SysInternals ProcMon log. The call is just being skipped by SQL*Plus. I my original script I have half a dozen scripts being called at this place in the script. They are all not getting called. At least on Windows. On Linux, they are all getting called and executed.
    – AxD
    Jul 21 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.