Oracle: deploy a new package body works but the package remains the same

[fluca1978]

I have to admit I'm not an expert in Oracle (I like PostgreSQL the much, but that's another story).
Anyway, I need to change a single procedure into a package, so what I did is:

• create a change with a deployment script that contains `create or replace package body foo; '. Note that the procedure signature did not change;
• at the end of the deployment script added alter package foo recompile body;

When I deployed the changes, sqitch gives me an okay status.
However, inspecting the body content from SQL Developer reveals the procedure body is still the old one.

Am I missing something here? What is the correct way to deploy such a change?

[fluca1978]

It seems that in order to allow sqlplus to execute more than one command in a single script, I need to separate every statement with a /, so this seems to work:

CREATE OR REPLACE PACKAGE BODY foo;
...
/ 

alter package foo compile body;
/ 

Since sqitch is passing thru sqlplus, I suspect the same has to be done in deployment script, right?
If so, please help me to understand why has sqitch reported an OK on deployment?

[theory]

I don't think that's a requirement for all Oracle stuff; the registry script doesn't need the slashes except after the CREATE TYPE statement, and based on this SO answer, I'm not sure it's needed there, either, though I suspect that d96b322 would not have worked without it. Given the discussion in that OS thread, I would guess that for some reason the semi-colons are not triggering immediate execution the way they should.

FWIW, the only options Sqitch passes to sqlplus are: -S -L /nolog here. On connecting, it sets

        'SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF VERIFY OFF',
        'WHENEVER OSERROR EXIT 9;',
        'WHENEVER SQLERROR EXIT 4;',

And then, for the registry variable:

    return $schema ? ("DEFINE registry=$schema") : (
        # Select the current schema into &registry.
        # https://www.orafaq.com/node/515
        'COLUMN sname for a30 new_value registry',
        q{SELECT SYS_CONTEXT('USERENV', 'SESSION_SCHEMA') AS sname FROM DUAL;},
    );

Maybe one of those changes the behavior or / somehow? I honestly have no idea.

[fluca1978]

@theory , thanks for the excellent documentation.

I would bet that the problem is about a begin...end block.

I tested this script:

% cat test.sql
select current_date from dual; 
/

create table foo( i int );

alter table foo add ( c varchar2( 20 ) );

drop table foo;


create  function f_foo()
return date
as 
begin
   select current_date
   from dual;
end;

that produces the following output thru sqlplus (Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.24.0.0.0):

>@test.sql

CURRENT_D
---------
23-JAN-25


CURRENT_D
---------
23-JAN-25


Table created.


Table altered.


Table dropped.

  8  

so all the statements are executed immediatly except the last one, that waits for instructions. If now I put a / on stdin the function is created:

 8  /

Warning: Function created with compilation errors.

(don't mind about compilation errors at the moment, I'm confusing PL/PgSQL with PLSQL right now...).

Let's change the script as follows:

% cat test.sql
select current_date from dual; 
/

create table foo( i int );

alter table foo add ( c varchar2( 20 ) );

drop table foo;


create  function f_foo()
return date
as 
begin
   select current_date
   from dual;
end
/

which results in:

>@test.sql

CURRENT_D
---------
23-JAN-25


CURRENT_D
---------
23-JAN-25


Table created.


Table altered.


Table dropped.


Warning: Function created with compilation errors.

So, according to the above, it seems to me that:

• if the statement has a ; and does not include a begin...end block, it is executed immediatly;
• a / executes what is in the buffer, like \g in psql, and if the previous statement has been already executed, it is re-executed;
• if a statement has a begin..end block, it is executed only via /, and in such case a ; does not trigger a double execution;
• some special commands need the / (see create type.

But again, I'm not an Oracle expert here, just writing down what I got in the hope it can become more clear.

I also confirm that https://github.com/sqitchers/sqitch/blob/develop/lib/App/Sqitch/Engine/oracle.sql#L104 here there is the need to execute the command with /:

>@test2.sql
  2  /

Type created.

where the script is:

% cat test2.sql
create type ltest AS varray(1024) OF VARCHAR2(512);

Last but not least, what is probably the root cause of my problems (i.e., why did sqitch reported an OK deployment while it was not), it seems to me that sqlplus is ignoring any error, and I would guess that the exit status is the way by which sqitch gets an idea of what happened:

>@test2.sql
  2  /

Type created.

>/
create type ltest AS varray(1024) OF VARCHAR2(512);
            *
ERROR at line 1:
ORA-00955: name is already used by an existing object

^D


% echo $?
0

EDIT: the above is not true. If I set a WHENEVER SQLERROR EXIT 4; in my login script as sqitch does, any SQL error causes sqlplus to abort with exit code 4 (as expected). I am now wondering if having a login.sql script on path (that is executed when sqitch connects, because I see the script outputting an informational message I put into) is preventing some sqitch required configuration.

[theory]

Very nice summary, thank you!

I also confirm that https://github.com/sqitchers/sqitch/blob/develop/lib/App/Sqitch/Engine/oracle.sql#L104 here there is the need to execute the command with /:

I knew there must've been a reason it was there!

EDIT: the above is not true. If I set a WHENEVER SQLERROR EXIT 4; in my login script as sqitch does, any SQL error causes sqlplus to abort with exit code 4 (as expected).

Phew!

I am now wondering if having a login.sql script on path (that is executed when sqitch connects, because I see the script outputting an informational message I put into) is preventing some sqitch required configuration.

Oh that's an interesting possibility. If there is some option to SQL*Plus to get it to ignore such files, similar to psql's --no-psqlrc, I'd gladly add it to the Engine config.