How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures.

Procedure:

create or replace procedure temp_proc is
begin
  DBMS_OUTPUT.PUT_LINE('Test');
end

it is created successfully. But when i execute:

execute temp_proc;

it shows ORA-00900: invalid SQL statement

So help needed here

Execute is sql*plus syntax .. try wrapping your call in begin .. end like this:

begin 
    temp_proc;
end;

(Although Jeffrey says this doesn't work in APEX .. but you're trying to get this to run in SQLDeveloper .. try the 'Run' menu there.)

Oracle 10g Express Edition ships with Oracle Application Express (Apex) built-in. You're running this in its SQL Commands window, which doesn't support SQL*Plus syntax.

That doesn't matter, because (as you have discovered) the BEGIN...END syntax does work in Apex.

Both 'is' and 'as' are valid syntax. Output is disabled by default. Try a procedure that also enables output...

create or replace procedure temp_proc is
begin
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('Test');
end;

...and call it in a PLSQL block...

begin
  temp_proc;
end;

...as SQL is non-procedural.

I use oracle 12 and it tell me that if you need to invoke the procedure then use call keyword. In your case it should be:

begin
  call temp_proc;
end;

Have you tried to correct the syntax like this?:

create or replace procedure temp_proc AS
begin
  DBMS_OUTPUT.PUT_LINE('Test');
end;

In Oracle SQL Developer (GUI), using 12c, also don't forget to enable the DMBS Output window (click View => Dbms Output, then click "+" sign, and select your connection), by default the window is not enabled.

The following syntax will then output to this window:

begin
  temp_proc
end;

You can do simply the following on the Command Window:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as XXX@YYY


SQL> call temp_proc();

Or:

SQL> execute temp_proc();