Thursday, January 18, 2007

SQL*Plus Tip: @ vs. @@

I built huge amounts of PL/SQL code with SQL*Plus, using the '@' command to import source files, without ever running across the '@@' command. But one day I found a limitation in '@', and in one fell swoop changed all my SQL and PL/SQL scripts to use '@@' and have never looked back.

You need '@@' if you ever want to compile source code by running SQL*Plus from a different directory. For example, I had source files that would call each other like so:


and it all worked fine, as long as I went into the directory where all the files were and ran SQL*Plus from there.

But to automate my build process, I started running various scripts from a different directory, such as:

sqlplus $credentials source/file1.sql

For file1.sql to be able to import file2.sql and so on, I needed to use '@@'. With '@@', all the import commands are processed relative to the directory where the original file sits, not the directory where you run SQL*Plus.


adderek said...

Great! I wasn't aware of that.

Paulo Travassos said...

You would think a logical extension of @@ would be to allow the use of relative pathing such as @@somedir/script.sql, but apparently this doesn't work. :(