CODEWITHSUNDEEP
oracle-databasesqlplus
Jeromy French
Jeromy French

Reputation: 12121

Getting error sp2-0310 when using "@@" (double "at" sign) command

I have a folder structure similar to

/Release_script/
  install.sql
/common_code/
  error_handling.sql
/Reporting/
  fancy_report1.sql
  fancy_report2.sql
  some_code_to_include.sql

The install.sql script includes the lines

@../Reporting/fancy_report1.sql
@../Reporting/fancy_report2.sql

fancy_report1.sql and fancy_report2.sql both start with the line 

@@../common_code/error_handling.sql

They also both include other /Reporting/ files, ie @@some_code_to_include.sql

As documented within "SQL*Plus User's Guide and Reference", the @@ (double "at" sign)

Runs a script. This command is almost identical to the @ ("at" sign) command. It is useful for running nested scripts because it has the additional functionality of looking for the specified script in the same path or url as the script from which it was called. Only the url form is supported in iSQL*Plus.

When I open sqlplus in /Release_script/ and attempt to run install.sql, I get the error

SP2-0310: unable to open file "../common_code/error_handling.sql"

If I change fancy_report1.sql's reference to @../common_code/error_handling.sql, then execute fancy_report1.sql directly from the /Reporting/ folder, it works.

What commands should I use to run commands from the starting point of the calling file (ie fancy_report1.sql) and not from the starting point of the working directory (ie, /Release_script/)? Why does the @@ command seem to behave exactly like the @ command?

For what it's worth:

sqlplus -S /nolog<<EOF
prompt &_SQLPLUS_RELEASE

Yields:

1102000100

Upvotes: 2

Views: 3619

Answers (2)

tale852150
tale852150

Reputation: 1628

From the SQL*Plus documentation:

    @@ (double "at" sign)

Runs a script. This command is similar to the @ ("at" sign) command.
It is useful for running nested scripts because it looks for the specified
script in the same path as the script from which it was called.

Hopefully, the second half of that final sentence may be the clue to your issue. If so, you may need to use absolute versus relative path names.

Unfortunately, I have been unable to recreate the issue on Red Hat Linux 6.7 or Windows 7. Here is my Windows session ( I just use select sysdate - 1 from dual; in the file error_handling.sql):

enter image description here

So my suggestion is to use absolute path names.

SQL*Plus version (on Windows 7):

enter image description here

Upvotes: 0

Patrick Marchand
Patrick Marchand

Reputation: 3445

I'm going to say it's your version of SQL*Plus that doesn't like relative paths using @@.

This link: http://www.orafaq.com/wiki/SQL*Plus_FAQ says "The @@ reference does not support relative directory references such as @@dir/file.sql or @@./file.sql." but it worked with my version.

I did the same setup as you described and ran the scripts using SQL*Plus: Release 12.1.0.2.0 and it ran fine. (The only difference is that I made the error_handling.sql script generate the text "COMMON_CODE\ERROR_HANDLING SCRIPT".)

I set my working directory to be Release_script and ran the install file:

C:\tmp\Release_script>sqlplus <un/pwd> @install.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 15:16:00 2016

MSG
---------------------------------
COMMON_CODE\ERROR_HANDLING SCRIPT


MSG
---------------------------------
COMMON_CODE\ERROR_HANDLING SCRIPT

Upvotes: 1

Related Questions