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
– William Robertson, Commented Jul 22, 2020 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. — user330315
– user330315, Commented Jul 23, 2020 at 6: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
– William Robertson, Commented Jul 23, 2020 at 7: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. — user330315
– user330315, Commented Jul 23, 2020 at 7: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
– TenG, Commented Jul 23, 2020 at 11:12

Connor McDonald · Accepted Answer · 2020-07-23 07:56:11Z

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, 2020 at 7:56

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HasaniH Over a year ago

This is perfect. I started adding EXECUTE IMMEDIATE to all of my DDL scripts and didn't like the smell.

TenG · Accepted Answer · 2020-07-23 11:53:37Z

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 · Accepted Answer · 2020-07-23 10:45:39Z

0

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.

edited Jul 23, 2020 at 10:45

answered Jul 23, 2020 at 7:37

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

Collectives™ on Stack Overflow

PL/SQL DB Deployment Script

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related