Display a message on command output

Asked

Viewed 1,201 times

1

I am running this PL/SQL and in case the condition falls on else, how to display the message to the user?

declare
  cont integer;
  tabela varchar2(100) := 'PESSOA';
  coluna varchar2(100) := 'NOME';
begin
    SELECT COUNT(NOME) into cont FROM PESSOA;
   if cont = 0 then
       execute immediate 'ALTER TABLE '||tabela||' DROP COLUMN '||coluna||'';
       commit;
   else
       ('Coluna '||coluna||'não excluído porque contém Dados');
   end if;
end;
/
oracle pl-sql

  • But how will this be used? Is it a trial? Or do you just want to show on the console?

  • I’m running several blocks like this one (Fields Creation). In this one specifically I would like it to display on the console. That is, if you fall into Else, it displays the message on the console and continues to run the other blocks normally.

2 answers

2


To print on the console just use the DBMS_OUTPUT.put_line:

begin
    DBMS_OUTPUT.put_line('Minha mensagem');
end;

Your code would look like this:

declare
  cont integer;
  tabela varchar2(100) := 'PESSOA';
  coluna varchar2(100) := 'NOME';
begin
    SELECT COUNT(NOME) into cont FROM PESSOA;
   if cont = 0 then
       execute immediate 'ALTER TABLE '||tabela||' DROP COLUMN '||coluna||'';
       commit;
   else
       DBMS_OUTPUT.put_line('Coluna '||coluna||'não excluído porque contém Dados');
   end if;
end;

To see the output in SQL Developer go to: View -> DBMS Output

  • You need to put another Iberian in Else ?

  • @Douglas, no. Include with your code in the answer.

  • Gave this message that is already the default: [2018-12-13 10:28:54] completed in 33 ms. But did not display the custom.

  • @Douglas you have to check the output (Output) that IDE you use? SQL Developer?

  • I’m looking at the exit anyway, I’m using the Datagrip.

  • @Douglas, I don’t know Datagrip but if you’re looking at the same output and it doesn’t show up the message is why it didn’t fall into Else. Do a test by putting another message in if just to see if it appears.

  • Same thing, tested in SQL Developer and displays the default message: PL/SQL Procedure successfully completed.

  • @Douglas, see if this helps you: https://stackoverflow.com/questions/7887413/printing-the-value-of-a-variable-in-sql-developer

  • Let’s go continue this discussion in chat.

  • 1

    Thank you, it was tool setup, now it worked.

Show 5 more comments

1

As the message is related to an error, it is possible to use the method Raise_application_error, that will interrupt the execution of the PLSQL procedure and will raise an exception for the application that is connected to the database.

  • +1 as he did not specify in the question that the code should continue after the message, the answer is also valid. But it would be interesting an example with the code of his question.

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.