2

I am looking for a way to avoid a recurrent error of mine, working with SQL scripts that sometimes contain PL/SQL blocks. As you may know, in sqlplus, if you do not add a slash / after a PL/SQL block (Begin ... End;), the block is not executed.

But from my development tools, the lack of slash is not detected until my stuff gets deployed on the testing environment, and it adds confusion and stress to the process.

I had this issue in many places I've worked at, and with many tools. So I wonder if it is possible to configure Oracle sqlplus either to

  • execute the content of the "buffer" before commits, even if no / is there
  • raise an error message if "buffer" is not empty when exiting

Another solution would be to change the behaviour of my development tools, I know, but am also looking forward to extend my Oracle knowledge with your permission.

To fix the idea, let's say I deliver script a.sql, that is encapsulated later on into the company's deployment script dpl.sql

dpl.sql simplified (note the importance of the . that kind of hides the issue, but that I cannot change, and is THE thing to work around)

spool  dpl.log
@a
.
spool off

a.sql where I forgot the / after my pl/Sql block before of personal inconsistency by design (beauty of Nature)

create table a(n number);

begin
  dbms_output.put_line('coucou');
end;

insert into a select 1 from dual ;

commit ;

Result where you see no commit, and no error...

SQL> @dpl
Table dropped.
Table created.

SQL> select count(1) from a ;
  COUNT(1)
----------
         0

I went through these, to no avail:

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • You need to keep in mind just what component you're talking to: the PL/SQL or SQL engine or to SQL*Plus or another development tool. Basically you use a semicolon to indicate the end of every PL/SQL or SQL statement; use the / to indicate "end of buffer" for SQL*Plus. I never use the "/" in development tools other than SQL*Plus (i.e. TOAD, Sql Developer, Dbeaver, ...) as I just highlight what I want run. – Belayer Aug 07 '19 at 14:25
  • I’m not clear what *commits* have to do with missing `/` characters terminating PL/SQL blocks. If you commit inside the PL/SQL block then it will only commit if the block is executed. If that is not the issue then please edit your question to include an example of what you mean. – William Robertson Aug 07 '19 at 22:11
  • Sorry @WilliamRobertson. I found a simple example where it doesn't work on my side. Question edited. – J. Chomel Aug 08 '19 at 07:13
  • Committing is not the problem. The whole block from `begin` to `commit;` is invalid: the `.` skips it, but attempting to execute it would give syntax errors. Perhaps you should disable `;` as terminator for non-PL/SQL statements, then you would be forced to use `/` everywhere. But as you say, that won't work in other tools such as PL/SQL Developer. – William Robertson Aug 14 '19 at 07:30

0 Answers0