22

I am using Oracle 10g database and trying to run a procedure using SQL commands.

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

Then click on Run button. It shows: "procedure created".

When I try to execute it using:

execute exam;

then click on Run button, it shows:

ORA-00900: invalid SQL statement

Thanks for your help.

jclozano
  • 618
  • 6
  • 24
kumar
  • 317
  • 1
  • 6
  • 15

3 Answers3

33

Just noticed a detail in your question. You press run button. Thus, you must be using an IDE.

You cannot use execute in IDEs - it is an sql*plus command. It might work in Oracle SQL Developer though, but I wouldn't use it there anyway;

Try 

begin
  exam;
end;
Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49
1

See: Syntax error while trying to call an Oracle package using ODBC in C#

You have to put "{" and "}" before the command. Example:

    processListCmd.CommandType = CommandType.StoredProcedure;
    processListCmd.CommandText = "{ call rep_invnr_proclist }";
    processListCmd.ExecuteNonQuery();
Community
  • 1
  • 1
Michael Kremser
  • 185
  • 1
  • 9
  • the question is not related to C# – Line Jun 19 '20 at 07:46
  • @Line You are absolutely right! Probably I got confused with too many browser tabs opened and picked the wrong tab when answering. – Michael Kremser Jun 20 '20 at 14:35
  • 1
    Actually, this may be the correct answer. Since, as @kirill-leontev pointed out, @kumar seems not to be using SQL*Plus, using `{ call ... }` instead of `exec ...` should work better. – Magnus Reftel Apr 15 '21 at 14:50
0

Lose the double-quotes around the name. They're a bad practice in Oracle.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • when used: create or replace procedure exam is begin DBMS_OUTPUT.PUT_LINE('Test'); end; same error is coming. – kumar Dec 05 '12 at 12:04
  • Must be the way that you're executing it in whatever tool you're using then. Does it work in SQL*Plus? If not, you may be executing it as SQL instead of as a procedure call. – David Aldridge Dec 05 '12 at 12:08
  • that should be rather a comment, it does not provide the solution in my opinion – Line Jun 19 '20 at 07:47