Including a SQL file within a SQL query

Question

In Oracle SQL Developer, is there any way to include a SQL file within a SQL query? Like the command \include{myfile.tex} in LaTeX.

Example:

• file1.sql contains FROM my_table;
• file2.sql contains SELECT * \include{file1.sql}

Executing file2.sql from Oracle SQL Developer should be equivalent to executing SELECT * FROM my_table;.

Answer 1

There are to features of sqlplus (and maybe of SqlDeveloper) that you can use to mimic the behaviour you want.

This is the START command

and the variable substitution feature.

We want to execute

SQLPLUS> select *
  2  from dual;

which results in

D
-
X

We now implement the requested files in the following way

file1.sql:

@file2.sql
select *
&FROM_CLAUSE
/

file2.sql

define FROM_CLAUSE='from dual'

this gives the sqlplus output

SQLPLUS> @file1
old   2: &FROM_CLAUSE
new   2: from dual

D
-
X

sqlplus shows the variables and their values. You can suppress it by setting the property VERIFY to OFF

SQLPLUS> set VERIFY OFF
SQLPLUS> @file1.sql

D
-
X

You can redefine the FROM_CLAUSE variable and use it as shown in file3.sql

set verify off

@file2.sql
select *
&FROM_CLAUSE
/

@file4.sql
select *
&FROM_CLAUSE
/

file4.sql

define FROM_CLAUSE='from dual , -
dual b'

the define clause can be spread over more then one line by ending each line by a minus '-' character. But sqlplus will join it to one line. I now no way to assign a multiline block to a variable.

This results in

SQLPLUS> @file3

D
-
X


D D
- -
X X

Here is another example

file5.sql

@file6.sql
select &TABLE_ALIAS..dummy
&FROM_CLAUSE
&WHERE_CLAUSE
&GROUP_CLAUSE
&ORDER_CLAUSE
/

file6.sql

define TABLE_ALIAS=a
define FROM_CLAUSE='from dual a, -
dual b'
define WHERE_CLAUSE=' '
define GROUP_CLAUSE=' '
define ORDER_CLAUSE='order by 1'

this gives

SQLPLUS> @file5

D
-
X

Here you see that the suffix '.sql' can be omitted in sqlplus. If a variable that represents a line should contains no value the it should be at least a blank, an empty string will raise an error in the sql statement

SQLPLUS> define ORDER_BY=''
SQLPLUS> select  *
  2  from dual
  3  &ORDER_BY;
SP2-0341:
line overflow during variable substitution (>3000 characters at line 3)

A dot character '.' terminates a variable name, so if you want a dot after a variable name you must add an additional dot.

Answer 2

In SQL*Plus, execute external scripts using @filename (http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1210918).

This also works in SQL Developer, just be aware of the differences between relative & absolute directory.