0

I am preparing SQL scripts that can deploy a set of code to a target environment with minimal fuss. I want the executing DBA to just sign on to the target database and execute "install_release.sql". That file "includes" (via the "@" command, which is supported by SQL Developer) other scripts:

PROMPT Compile package specs...
    PROMPT ...package A
    @../../../path/to/packageA/spec.sql

    PROMPT ...package B
    @../../../path/to/packageB/spec.sql

PROMPT Compile package bodies...
    PROMPT ...package A
    @../../../path/to/packageA/body.sql

    PROMPT ...package B
    @../../../path/to/packageB/body.sql

This part of the process seem to work great.

However, each of the package bodies tries to include common, repeated, SQL snippets (for example, standard EXCEPTION declarations) that are to be included in the package's definition. As an example, "exception_declarations.sql" contains code like this:

EMPTY_PARAMTER         EXCEPTION
INSUFFICIENT_ACCESS    EXCEPTION

Whereas "packageA/body.sql" contains code like this:

CREATE OR REPLACE PACKAGE BODY My_Schema.PackageA AS
    @../../../path/to/exception_declarations.sql

FUNCTION ETC(...etc...

When I attempt to execute "install_release.sql" as a script from SQL Developer, I can see from the resulting logs that the compiler follows the paths to compile the specs (no problem), and into the package bodies...but once inside the package bodies, it throws this error when attempting to execute @../../../path/to/exception_declarations.sql:

Encountered the symbol "@" when expecting on of the following: begin end function pragma procedure subtype type current cursor delete exists prior

Adding to the mystery, this exact same code compiles as expected (no error) when executed via SQL Plus (I'd just use SQL Plus, but I'm working remotely through a fairly locked-down virtual machine that wont let me install SQL Plus...but does let me "install" SQL Developer...it's complicated...I don't like talking about it because then the demons start to laugh at me).

What am I doing wrong here? Why will the compiler follow the pathing in set of scripts, but not from within the package body code? I assume it's because the @ command is embedded within the DDL...so...How can I get SQL Developer to compile code that includes code within the DDL?

Improve this question
2
  • PS--is this the best forum for this question? Or should it be moved to super user? Commented Jan 10, 2022 at 14:44
  • Neither SQL*Plus nor SQL Developer compile any code; SQL statements are sent to the database server for compilation. Clearly these two client tools handle the include directives differently, so I'm afraid you'll have to live with this. Commented Jan 10, 2022 at 15:33

1 Answer 1

Reset to default
1 
@  !=  #include  
@  ==  START

The START command and it's "@" equivalent will invoke a complete, external PL/SQL script.
"@" and START are not part of the Oracle PL/SQL syntax, so you can't use it inside other PL/SQL DDL constructs.

You should construct either a single script that contains everything or a top-level, "controller" script that includes each of the lower-level elements, in the right order.

@ empty_parameter_exception.sql
@ insufficient_access_exception.sql
@ package_a.sql
@ package_a.body.sql
@ package_b.sql
@ package_b.body.sql

Anyway, if your Exceptions, etc., are "standard", then should they not be in their own Package, rather than repeating their [textual] definitions in each, separately-compiled Package?

Improve this answer
1
  • Blast. What about @@...same concept (just different starting point of reference), right? And why does SQL Plus execute this code as expected, but SQL Developer doesn't. Fair point about encapsulating all the common exceptions into their own package, which all the other packages can reference. Commented Jan 10, 2022 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.