0

I want to execute a PL/SQL procedure which is inside a .sql file.

I'm calling my .sql file with SQL*Plus like this : 

@myfile.sql

And here is myfile.sql

declare 
    listTotal number := 0;
    nbrCommit number := 200;
begin
    select count(1) into listTotal from MKTMLF.TMP_ID_CLI_SUPPR;
    [... different updates here ....]
commit;
    if listTotal > nbrCommit THEN
        [... different updates here ....]
        commit;
    end if;
end;

When i paste that PL/SQL inside Oracle SQL developer it's OK, it tells me "Procedure PL/SQL finished" but when i do it with SQL*Plus it just show me a number

55

and nothing else! Actually i have found that these number is the number of lines inside my file.sql.

What is the problem? Why is it OK inside Oracle SQL developer and with SQL*Plus?

Siick
  • 491
  • 1
  • 7
  • 23
  • 2
    Type a forward slash '/' and your procedure will run. You have put your code in the buffer but you need to tell it to run what's in the buffer - hence the '/'. – BriteSponge Mar 29 '18 at 09:22
  • @BriteSponge Tahnk you so much!!! That "/" make it works! – Siick Mar 29 '18 at 09:33
  • 1
    Related: https://stackoverflow.com/q/1079949/330315 –  Mar 29 '18 at 11:01

1 Answers1

3

Please add a forward slash / at the end of file. That will ask the client to run the code.

The trailing / is something that sqlplus and sqlcl look for to know it's the end of the block and please run the above code block.

In SQLDEV, there is a smart parser that can determine the end of the block syntactically to know what to execute.

Kris Rice
  • 3,300
  • 15
  • 33
SimpleOne
  • 98
  • 7