PL/SQL DB Deployment Script

Question

I'm trying to write a deployment script to run with SQL*Plus in a CI/CD pipeline but I can't find my way around what seems to be a very basic issue.

Here's a shortened version of the script release.sql:

DECLARE
    vnum NUMBER;
BEGIN
    SELECT COUNT(tname) INTO vnum FROM tab WHERE tname = 'DA_VERSION';
    IF vnum = 0 THEN -- run create scripts
        @ddl/da_001.sql
        @ddl/da_002.sql
        @dml/version.sql -- set initial version   
    END IF;
END;

da_001.sql looks like this:

CREATE TABLE TABLE_NAME 
(
  COLUMN1 NUMBER NOT NULL 
, CONSTRAINT TABLE_NAME_PK PRIMARY KEY 
  (
    COLUMN1 
  )
  ENABLE 
);

When I run

sqlplus.exe connection_string @release.sql

I get

CREATE TABLE DA_PRODUCTS * ERROR at line 6: ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:...

So it doesn't like the CREATE statement at the beginning of da_001.sql but I don't know why. What am I missing here?

The PL/SQL language has no `CREATE` keyword. You'll need `execute immediate` or a different approach. — William Robertson, Jul 22 '20 at 21:04
`@` is a `sqlplus` command, not a SQL or even PL/SQL command. It can **only** be used on the `sqlplus` command line. PL/SQL code is run on the server without any notion about which client tool is used to call it or even access to the files on the client. — , Jul 23 '20 at 06:14
@a_horse_with_no_name if this is part of a SQL\*Plus script, though, SQL\*Plus will embed the referenced script in the PL/SQL block before executing it. The referenced scripts do have to be valid PL/SQL, though. — William Robertson, Jul 23 '20 at 07:28
@WilliamRobertson: are you sure? This would require sqlplus to parse the PL/SQL body and replace the reference to the file with the actual file content before it sends the PL/SQL block to the server. — , Jul 23 '20 at 07:31
In the first script, you are missing the '/' at the end. Without this, it assumes the `CREATE TABLE` is part of teh PLSQL block. — TenG, Jul 23 '20 at 11:12
Also, as other suggested, PLSQL does not understand SQLPLUS directives like `@` etc. — TenG, Jul 23 '20 at 11:13

score 3 · Accepted Answer · answered Jul 23 '20 at 07:56

3

Create an installation file for your scripts, ie

install.sql
===========
@ddl/da_001.sql
@ddl/da_002.sql
@dml/version.sql -- set initial version

and then selectively call it via a wrapper in SQL Plus

set feedback off
set pages 0
spool /tmp/runme.sql
select 
  case when COUNT(tname) = 0 then '@@install.sql' else 'pro Skipped install.sql' end
FROM tab WHERE tname = 'DA_VERSION';
spool off
@/tmp/runme.sql

answered Jul 23 '20 at 07:56

Connor McDonald

10,418
1
11
16

This is perfect. I started adding EXECUTE IMMEDIATE to all of my DDL scripts and didn't like the smell. – HasaniH Jul 23 '20 at 13:18

TenG · Answer 2 · 2020-07-23T11:53:37.593

As others have said there is a strict separation between what SQL*PLus understands/is capable of and what is within the SQL and PLSQL languages.

Script handling is one of these distinct areas, which means you cannot executing a SQL script within a PLSQL block.

Also, SQL*Plus does not have any understanding of PLSQL logic constructs.

However, looking at your requirement there might be a way. The answer from @"Connor McDonald" should work. Here is my attempt using a more PLSQL based approach.

This approach uses SQLPLus variables, which can be referenced and amended in both PLSQL and SQLPlus.

First you need a 'No Op' script since when using the SQL*PLus '@' you must specify a valid script name:

noop.sql:

PROMPT No Op

Now your controller script:

-- Declare your variables 

VAR script1 VARCHAR2(256)
VAR script2 VARCHAR2(256)
VAR script3 VARCHAR2(256)

DECLARE
    vnum NUMBER;
BEGIN
    :script1 := 'noop.sql';
    :script2 := 'noop.sql';
    :script3 := 'noop.sql';
    SELECT COUNT(tname) INTO vnum FROM tab WHERE tname = 'DA_VERSION';
    IF vnum = 0 THEN -- run create scripts
         -- Set variables
        :script1 := 'ddl/da_001.sql';
        :script2 := 'ddl/da_002.sql';
        :script3 := 'dml/version.sql'; -- set initial version   
    END IF;
END;
/

-- Make variables referencable as SQLPLus defines

COLUMN s1 NEW_VALUE s1
COLUMN s2 NEW_VALUE s2
COLUMN s3 NEW_VALUE s3

SELECT :script1 s1, :script2 s2, :script3 s3
FROM dual;

-- RUN !!

@ &&s1
@ &&s2
@ &&s3

The 3 scriptn variables can be used in PLSQL.

To use as SQL_PLus substition variable (&) we use the COL ... NEW_VALUE command to map a SELECT list column alias to a substituion variable. So we will effectively map scriptn to subs variabl;e sn.

After the PLSQL blocks completes the scriptn variables will have teh value 'noop.sql' or the name of teh script to run.

Then at the end, reference the subs variables in the '@' commands.

Anything with 'noop.sql' will execute a blank script.

William Robertson · Answer 3 · 2020-07-23T10:45:39.627

I haven't used this myself but you might try something like this (simplified demo):

declare
    vnum number := 0;
begin
    --select count(tname) into vnum from tab where tname = 'DA_VERSION';
    if vnum = 0 then -- include create script
        execute immediate q'[
        @callthis.sql
        ]';
     end if;
end;

To work within execute immediate, the called script must contain a single statement and no semicolon.

PL/SQL DB Deployment Script

3 Answers3