What actually PROMPT<?> syntax do in oracle procedure?

November 21, 2022

So, in this code what exactly PROMPT syntax do?

PROMPT create or replace procedure abc (p_name, p_id)
AUTHID CURRENT_USER
as
begin
 dbms_output.put_line('hi');
end;

>Solution :

PROMPT has a meaning if you run some code in SQL*Plus (not many people do that nowadays). It displays text what follows that keyword.

SQL> help prompt

 PROMPT
 ------

 Sends the specified message or a blank line to the user's screen.

 PRO[MPT] [text]


SQL> prompt hello there!
hello there!
SQL>

In your case, it produces unwanted result as it displays the create procedure (instead of creating it):

SQL> PROMPT create or replace procedure abc (p_name, p_id)
create or replace procedure abc (p_name, p_id)
SQL> AUTHID CURRENT_USER
SP2-0734: unknown command beginning "AUTHID CUR..." - rest of line ignored.
SQL> as
SP2-0042: unknown command "as" - rest of line ignored.
SQL> begin
  2   dbms_output.put_line('hi');
  3  end;
  4  /
hi

PL/SQL procedure successfully completed.

SQL>

You got the result, but just as pure accident as

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

was a valid PL/SQL block.

Code you posted (without prompt) is invalid:

SQL> create or replace procedure abc (p_name, p_id)
  2  AUTHID CURRENT_USER
  3  as
  4  begin
  5   dbms_output.put_line('hi');
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE ABC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/22     PLS-00103: Encountered the symbol "," when expecting one of the
         following:
         in out <an identifier> <a double-quoted delimited-identifier>
         ... long double ref char time timestamp interval date binary
         national character nchar

3/1      PLS-00103: Encountered the symbol "AS" when expecting one of the
         following:
         with authid cluster order deterministic parallel_enable
         pipelined result_cache

6/4      PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         end not pragma final instantiable order overriding static
         member constructor map

SQL>

What does it mean? Procedure’s parameters have to have datatype:

SQL> create or replace procedure abc (p_name in varchar2, p_id in number)
  2  AUTHID CURRENT_USER
  3  as
  4  begin
  5   dbms_output.put_line('hi');
  6  end;
  7  /

Procedure created.

SQL> exec abc(null, null);
hi

PL/SQL procedure successfully completed.

SQL>