Execute PROCEDURE on Oracle PL/SQL with DECLARE variable

Question

I just currently learning about Oracle PL/SQL. I wanna create store procedure with variable and then call it with another script. Is it possible?

I tried use simple script without variable and it works:

CREATE OR REPLACE PROCEDURE testmyproc AS
BEGIN
INSERT INTO tes_table(dt)
VALUES (sysdate);
commit;
END testmyproc;

Then I call it with another script abc.sql

begin
  testmyproc;
end;

It works successfully.

But, unfortunately if I use DECLARE (variable) at my PROCEDURE, it show error when I execute (but it success in create procedure).

Here's my PROCEDURE (no error):

CREATE OR REPLACE PROCEDURE sp_testmyproc AS
DECLARE
   job_name varchar(100);
   status_key number;
   status_desc varchar(100);
   notes varchar(250);
BEGIN
    status_key := 1;
    status_desc := 'SUCCESS';
    notes := 'Process Completed';
    INSERT INTO automation_log(job_name, dt, status_key, status_desc, notes)
    VALUES (job_name, sysdate, status_key, status_desc, notes);
    commit;
END sp_testmyproc;

Here's my execure script abc.sql (show error when i execute it)

-without DECLARE

begin
  sp_testmyproc;
end;

-I tried to execute it with DECLARE

DECLARE
  job_name varchar(100);
  status_key number;
  status_desc varchar(100);
  notes varchar(250);
begin
  status_key := 1;
  status_desc := 'SUCCESS';
  notes := 'Process Completed';
  sp_testmyproc;
end;

It show error like this:

>     ORA-06550: line 8, column 11:
>     PLS-00905: SP_TESTMYPROC is invalid
>     ORA-06550: line 8, column 3:
>     PL/SQL: Statement ignored

Can I call Procedure for another script? Is It best practice? I just think PROCEDURE can be used for many cases (something like function in programming).

Thank you!

Popeye · Accepted Answer · 2020-06-04 06:38:12Z

3

You need to learn the syntax of the procedure.

In Procedure, You should not use the keyword DECLARE. Any variables you want to declare must be between AS and BEGIN in the procedure.

Your procedure should look like follows:

CREATE OR REPLACE PROCEDURE sp_testmyproc AS
--DECLARE
   job_name varchar(100);
   status_key number;
   status_desc varchar(100);
   notes varchar(250);
BEGIN
    status_key := 1;
    .....
    .....
    .....

Refer to this document for the syntax of the oracle procedure as it is very easy to follow.

answered Jun 4, 2020 at 6:38

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Willy Sandi Over a year ago

I have 2 PROCEDURE(s) to run like this, when I change my PROCEDURE without DECLARE, the one execute successfully and the other still got same error.. Do you have any idea about error or this limitation?

Popeye Over a year ago

I think it is altogether different issue. Mark it as accepted and ask othet question to avoid any mess in single question.

pifor · Accepted Answer · 2020-06-04 07:07:08Z

0

Please note the difference when creating a stored procedure in SQL*Plus:

SQL> create or replace procedure test_ok as
  2   v number;
  3  begin
  4   v:=0;
  5  end;
  6  /

Procedure created.

SQL> show errors
No errors.


SQL> create or replace procedure test_ko as
  2  declare
  3   v number;
  4  begin
  5   v:=0;
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_KO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1  PLS-00103: Encountered the symbol "DECLARE" when expecting one of
     the following:
     begin function pragma procedure subtype type <an identifier>
     <a double-quoted delimited-identifier> current cursor delete
     exists prior external language

SQL>

If you have compilation errors you get at least Warning: Procedure created with compilation errors. If you have compilation errors and use show errors you get all error messages.

answered Jun 4, 2020 at 7:07

pifor

7,9722 gold badges12 silver badges18 bronze badges

2 Comments

Willy Sandi Over a year ago

I run create PROCEDURE and execute it via PL/SQL Developer. Do I need use DECLARE or not to execute it?

pifor Over a year ago

You cannot use DECLARE in stored procedure: this does not depend on the client tool used. It depends only on PL/SQL that is running on the database server side.

Collectives™ on Stack Overflow

Execute PROCEDURE on Oracle PL/SQL with DECLARE variable

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related