11

Please advise. This SQLPlus call:

SQL > @dba_files_all

...is not working.

SP2-0310: unable to open file "dba_files_all.sql"

How can I resolve the error?

Tom Slabbaert
  • 21,288
  • 10
  • 30
  • 43
Jaad
  • 155
  • 1
  • 1
  • 7

8 Answers8

11

You need to provide the path of the file as string.

Put the path in double quotes and it will work.

For example:

@"C:\Users\Arpan Saini\Zions R2\Reports Statements and Notices\Patch\08312017_Patch_16.2.3.17\DB Scripts\snsp.sql";
alexherm
  • 1,362
  • 2
  • 18
  • 31
Arpan Saini
  • 4,623
  • 1
  • 42
  • 50
4

1.Use absolute path:

/u01/app/oaracle/test.sql

2.Check the path to see if script exists:

ls -l /u01/app/oaracle/test.sql
alexherm
  • 1,362
  • 2
  • 18
  • 31
tzJacky
  • 51
  • 3
4

I encountered this error when attempting to execute a file in the same folder as the calling function. In my example, this process:

  • Was executed in SQL Developer;
  • Has been a long-standing part of my system (moving a setup file with some settings and variable names through various folders; those folder names include the feature IDs and a short description);
  • Has worked fine in the past;
  • Did not require any pathing in my case because the files were in the same folder;
  • Failed on the most recent attempt with the error above (SP2-0310).

The issue in my situation was that the folder name in which it failed included a character (#) that was valid for a Windows file name, but confusing to SQL Developer.

Joe DeRose
  • 3,438
  • 3
  • 24
  • 34
  • Thanks. Similar problem here: SQL Developer does not seem to recognize non-ASCII characters in file names under Linux. If such a character is in a directory name it is not even displayed as a directory in the “open” file chooser. My workaround is to create symbolic links to the concerning files. – Renardo Nov 28 '18 at 15:14
2

Note that

SQL> @some_file.sql

means that sql app you are using will look for that using "absolute path" so if you want to use "relative path" use following format [add ?]

SQL> @?some_file.sql

else, use "full path" with first command.

KhogaEslam
  • 2,528
  • 1
  • 20
  • 21
0

All the answers so far imply that absolute paths are required. That aren't. Relative paths in sql is pretty universal in sql tools. Sometimes, you have to configure a lost default configuration such as in the case of SQLDeveloper as explained in this answer: https://stackoverflow.com/a/24003529/442968

Community
  • 1
  • 1
Rondo
  • 3,458
  • 28
  • 26
0

I just run into same error when I was trying to unlock oe schema. While reading the error, I realized that when I run the following line:

>SQL @?/demo/schema/order_entry/oe_main.sql

The error returned a completely different path

SP2-0310: unable to open file "C:/app/USER/product/18.0.0/dbhomeXE/demo/schema/order_entry/oe_main.sql"

Thus I copied my sql file to the path specified by the error and everything worked. I recommend that you do the same. Check the path in the error and adjust accordingly.

Herve H
  • 71
  • 4
0

Use absolute path or run sqlplus command from a shell/dos that points to the path of the script. Also, to use a masterscript, refer to subscripts with @@.

LovaBill
  • 5,107
  • 1
  • 24
  • 32
-1

verify that your file has an extension .sql not .sql.txt

Ayoub Anbara
  • 407
  • 5
  • 10