Oracle Stored Procedure

Question

Hi,

I want to run the stored procedure script, but I am facing the problem that, when I run multiple stored procedures at one time, all procedures gets compiled but it does not create all those procedures separately, it compile it as a single procedure. Can anyone help me out to solve this prolem.

Answer 1

A script to create multiple procedures should look like this:

create or replace procedure proc1 (param1 varchar2)
is
begin
    null;
end;
/

create or replace procedure proc2 (param1 varchar2)
is
begin
    null;
end;
/

create or replace procedure proc3
is
begin
    null;
end;
/

What does yours look like?

Best practice is always to put related stored procedures in packages anyway:

create or replace package package1 is

    procedure proc1 (param1 varchar2)
    is
    begin
        null;
    end;

    procedure proc2 (param1 varchar2)
    is
    begin
        null;
    end;

    procedure proc3
    is
    begin
        null;
    end;

end package1;
/

Answer 2

I expect that you're missing out the / after each procedure.

Answer 3

I think David and Tony, both got the point.

First thing to check is that you have ended every procedure with the slash / so that oracle can recognize the end of each procedure.

Since stored procedures instructions/statements are separated by ; Oracle does not know when a statement finishes the procedure, and that's why you need to use another "terminator"

Second thing is, if you got several procedures that can be categorized, then you should create a package, remembering to create the package definition and the package body.

CREATE OR REPLACE PACKAGE PCKG1 AS
    PROCEDURE PROC1;
    PROCEDURE PROC2 (PARAM1 VARCHAR2);
END PCKG1;
/


CREATE OR REPLACE PACKAGE BODY PCKG1 AS

    PROCEDURE PROC1 IS
    BEGIN
        -- YOUR CODE HERE --
    END PROC1;

    PROCEDURE PROC2 (PARAM1 VARCHAR2) IS
    BEGIN
        -- YOUR CODE HERE --
    END PROC2;

END PCKG1;
/

That way you'll be able to find your procedures/functions easily when you have developed some dozens.

Good answers for everyone.