Oracle 10g中未使用OR REPLACE关键字创建存储过程的疑问及报错解析

Hey there! Let's tackle your two questions clearly, based on Oracle 10g behavior:

1. Consequences of omitting the REPLACE keyword when creating a stored procedure #

The impact depends entirely on whether a procedure with the same name already exists in your schema:

• If no matching procedure exists: Omitting OR REPLACE works just fine—your procedure will be created normally (assuming no syntax errors, of course). It behaves exactly like using CREATE OR REPLACE here.
• If a matching procedure already exists: Without OR REPLACE, running CREATE PROCEDURE will throw an immediate error (ORA-00955: name is already used by an existing object). The original procedure remains untouched; no changes are made to it. You need OR REPLACE to overwrite the existing procedure with your new definition.

2. Your specific Oracle 10g scenario #

Let's break down what happened with your statement:

create procedure procname is begin --query end;

First, the "procedure created with error" message tells you that Oracle did attempt to create the procedure, but hit a syntax or compilation error (in your case, likely because --query is a comment and there's no valid executable code inside the procedure body).

Did the procedure get created? #

Yes—but it's in an INVALID state. You can verify this by querying the data dictionary:

SELECT object_name, status FROM user_objects WHERE object_name = 'PROCNAME';

If the query returns a row with status = 'INVALID', that confirms the procedure exists but can't be executed until you fix the compilation errors.

Did it replace an existing procedure with the same name? #

Absolutely not. Here's why:

• If a valid procedure named procname already existed, omitting OR REPLACE would have caused Oracle to throw an ORA-00955 error immediately—you wouldn't have gotten the "procedure created with error" message at all. The original procedure would stay intact.
• Since you received the "created with error" message, it means there was no existing procedure with that name before you ran this command. You've just created an invalid new procedure, not replaced anything.

内容的提问来源于stack exchange，提问作者sneha deolikar