Advertisement
Advertisement


How to execute an oracle stored procedure?


Question

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

2012/01/11
1
60
1/11/2012 12:12:46 PM

Accepted Answer

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.)

2009/12/06
84
12/6/2009 9:57:25 PM

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.

2009/12/06

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.

2013/03/13

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;
2020/03/13

Have you tried to correct the syntax like this?:

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

Source: https://stackoverflow.com/questions/1854427
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]