what happend if we create procedure without replace keyword-CodePudding

I create 1 procedure in oracle 10g , syntax of my stored procedure is:

create procedure procname is begin --query end;

I did not use or replace keyword when I execute this procedure. It gave me this message: procedure created with error.

What it exactly means my procedure is created or not and if it created will it replace my previous procedure because I have same procedure name before executing current procedure.

CodePudding user response：

The error message: procedure created with error. Means that your procedure was created but you will not be able to run it as it has errors.

You can use:

SHOW ERRORS;

immediately after the CREATE PROCEDURE DDL statement to show those errors or you can query:

SELECT *
FROM   user_errors
WHERE  name = 'YOUR_PROCEDURE_NAME';

If you want to recreate the procedure then either use:

DROP PROCEDURE your_procedure_name;

and then use CREATE PROCEDURE ...

or use:

CREATE OR REPLACE PROCEDURE ...

CodePudding user response：

Procedure was created (as message says), but it has errors.

There was no other procedure (or object) with that name, because you'd get "ORA-00955: name is already used by an existing object" error message:

This is the first procedure, it was successfully created:

SQL> create procedure procname is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

Running another create procedure, using the same procedure name:

SQL> create procedure procname is
  2  begin
  3    null;
  4    --
  5    null;
  6  end;
  7  /
create procedure procname is
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

What if the "new" procedure had errors (missing INTO clause and where to put that value)? Nothing, the same message again:

SQL> create procedure procname is
  2  begin
  3    select * from dual;
  4  end;
  5  /
create procedure procname is
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

If you created a procedure that is invalid, then:

SQL> create procedure procname_2 is
  2  begin
  3    select * from dual;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PROCNAME_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PLS-00428: an INTO clause is expected in this SELECT statement
SQL>